PostgreSQL on Mandrake 9

OK, so you've installedMandrake 9 and PostgreSQL and now you want to use PostgreSQL. However, finding documentation specific to PostgreSQL on Mandrake 9 - well, you just can't find it.

First, be aware that a user postgres has been created which I suppose we can say is the database administrator. postgres is a system user without any password.

The following worked for me:
As root, go to /etc/rc.d/init.d/postgresql and go to line 150 which looks like this:

su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -p 

/usr/bin/postmaster start  > /dev/null 2>&1" < /dev/null

Now add -o "-i" to make it look like this:

su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -o "-i" -p 

/usr/bin/postmaster start  > /dev/null 2>&1" < /dev/null

When postmaster starts again when you start your computer up you will be able to connect to PostgreSQL using TCP/IP. This is essential for JDBC and also for PostgreSQL Access which comes with Mandrake 9. You will find PostgreSQL Access in Kicker --> Applications --> Databases
Setting just the tcpip_socket entry to true in /var/lib/data/postgresql.conf didn't work for me.

Next you want to add some users to PostgreSQL. However, before you can do this you must be able to access the database without being asked for a password. Otherwise, before you can create a user you may be asked for a password, or be told that for some or other reason you cannot create this user, even if you are logged into the system as root or postgres. Remember, postgres has no password at this stage

Go to /var/lib/pgsql/data and open pg_hba.conf in a text editor. The last line looks like this:

 local      all            ident    sameuser

Change ident and sameuser to trust. The line should look like this:

local      all                     trust

Save your changes and close pg_hba.conf. You will now be able to log locally into the database without being asked for a password. This is just for now. We are going to change that a bit later.

You will now have to restart the database server. Do a su and feed in your root password. When you are logged in as su root do a su postgres. You will be logged in as postgres without a password prompt. Root cannot start or restart the database server, but postgres can.
Open a term window and type in

pg_ctl restart -D /var/lib/pgsql/data -o "-i"

and press enter. You will be informed that the database server is shutting down, has shut down, is restarting and has successfully restarted. The -o "-i" option is to restart PostgreSQL with the "-i" option enabled. That will ensure you can connect to the database using TCP/IP. Note that /etc/rc.d/init.d/postgresql is not involved when we start PostgreSQL this (pg_ctl) way, hence the changes that we made there will not be taken into account untill PostgreSQL is started at system startup. Of course, /etc/rc.d/init.d/postgresql is executable by root, so nothing is stoppping you from executing it as root right at this moment. We have not yet set up the pg_hba.conf file for connections using TCP/IP, so all this is academical at the moment. But 10 minutes from now...

To see that the "-i" option is enabled do

ps ax | grep post

You should see:

			   1698 ?        S      0:00 /usr/lib/postfix/master
			   6686 pts/3    S      0:00 /usr/bin/postmaster -i
			   6687 pts/3    S      0:00 postgres: stats buffer process   
			   6689 pts/3    S      0:00 postgres: stats collector process   
			   6870 pts/3    S      0:00 grep post

or something similar. What is important is the

 /usr/bin/postmaster -i

line.

Now is as good a time as any to add the environment variable PGDATA to either your ~/.bash_profile or /etc/profile. If only you are going to use the database, ~/.bash_profile is fine, but if different users are going to use the database you must add PGDATA to /etc/profile. You must be root to change /etc/profile.
Add

PGDATA="/var/lib/pgsql/data"

to your /etc/profile or ~/.bash_profile file. Also add

PGDATA

to the list of environment variables to be exported. Now we can add users.

Only postgres can add users at this stage. Now type in

createuser userName -P

"createuser" is a shell script in /usr/bin. You do not have to be connected to PostgreSQL to use it, but at this stage you must be postgres. You will be prompted for your user's password. Type it in. You will be prompted again. Type it in again. If you don't want to be bothered with passwords don't use the -P option. You will then also have to use "trust" or "ident sameuser" in pg_hba.conf instead of the "password" or "md5" as explained below. Now you will be asked if your user is allowed to create tables and next if your user is allowed to create new users. Type in what you want here.

If you don't create a user able to create new users you will have to log in as postgres every time you want to create a new user.

A user able to create new users is a super user, just like postgres. I would recommend you make yourself a super user.
Create as many users as you want.

If you now migrate to /var/lib/pgsql/data/global you will find a file named pg_pwd there with your new users and their passwords. This file belongs to postgres and postgres alone, and of course root, has read and write permission to it.

Now do a cd ../
You will now be in /var/lib/pgsql/data
Open up pg_hba.conf in a text editor.
You changed the last line to look like this:

 local      all            trust

Change trust to password. The line should look like this:

local      all                     password

Append the following line below the one above:

host	all	127.0.0.1	255.255.255.255    password.

Of course, this sets you up for connections from localhost. If you want to be able to connect from your local network, enter your network address and your netmask. If you want to enable users to connect from anywhere enter 0.0.0.0 and 0.0.0.0 as the netmask.

You now have two uncommented lines at the end of your pg_hba.conf file.

You do NOT have to specify where your pg_pwd file is under the AUTH_ARGUMENT heading as indicated by the documentation inside the file. In fact, if you do things don't work.

You can now log in using TCP/IP from the localhost and directly from the local machine from a terminal window. PGAccess uses TCP/IP to log in.

If you want to you can do a "pg_passwd /var/lib/pgsql/data/global/pg_shadow" and add the same users you created and their passwords. pg_shadow will be created in /var/lib/pgsql/data/global/. The passwords will be encrypted, unlike the plain text passwords in pg_pwd. You can then change the "password" in the last two lines of pg_hba.conf to "md5". Things will work as before. DO NOT delete pg_pwd. Also, do not even delete the plain text passwords in pg_pwd. They are still needed. Deleting them prevented me from connecting. As I understand it, doing this ensures that the passwords are sent over the network in an encrypted form. The user names seem to still come from pg_pwd. I am not sure if this really works as it should.

Now, still as postgres, enter the following on the command line:

 pg_ctl restart -D /var/lib/pgsql/data -o "-i"

and postmaster will be restarted and all the changes you've made will be loaded. If you ever want to do a restart after you have restarted your machine (or logged out if you set PGDATA in ~/.bash_profile) the environment variable PGDATA will be loaded and you will not need to type in -D /var/lib/pgsql/data anymore.

You can now do a "createdb databaseName" as yourself, i.e. not as root or postgres. Hopefully you've added yourself as one of the PostgreSQL users. Once that is done you can log into PostgreSQL by doing a:

psql databaseName

at the command line. You will be prompted for your password and allowed in. At this stage I recommend you give postgres a password. Do a:

alter user postgres password 'theNewPassword';

Now create tables, add contents to the tables and have fun.