Postgresql 8.3 on Debian Lenny

As I write this post, Debian Lenny is still Testing. I installed Lenny in VMware from a testing installer, and changed the sources.list file replacing lenny with testing. I will be installing Postgresql 8.3 by name, instead of the package Postgresql. As I learn more things, I will either create a new post or add to this one.

Install Postgresql

sudo aptitude install postgresql-8.3

This will install postgresql-client and OpenSSL (with some of its other packages). Location of Postgresql’s configuration files is /etc/postgresql/8.3/main/ and data is stored in /var/lib/postgres/8.3/main/.

Log in to Postgresql

It is installed under the user postgres and to use Postgresql client, you may need to be logged in with that user name. Two ways to do this are:

su -
su postgres

And the second way, if the user you are logged in as has sudo capabilities:

sudo su postgres

As you can see, there is no automatic way of using client unless you login as user postgres. But this can be changed. We can allow all users of the system to connect. By default, you should find a line local all all ident sameuser in the pg_hba.conf file, which means all users are allowed to access the database using Unix sockets (local users, not remote users). However, you are still not able to use the database as your own user. The reason is you need to add that user in the database as well.

As I see it, this is a good way of restricting access. Of course, you can still specify in pg_hba.conf file which users exactly are allowed and then also add only them to the database.

To add the user to the database, do the following:

su -
su postgres
createuser -P
[you will be asked to enter the password for the user, and also if this user should be a superuser]
exit

Now you can simply login using your own username. For example, if your user name is codeghar, then once you login to Debian with codeghar, you do this:

psql -W template1

You will be prompted for a password (because of the W) and then you will be using the client in the template1 database, which I think is provided by Postgresql by default.

Hat Tips

These sites helped a lot in not only getting me started, but also in writing this post: Installing Postgresql on Debian; PostgreSQL Database Server Configuration;