TutoLivre

The administration of PostgreSQL [1] requires some care with security. One of them is to allow only certain users to connect and from certain hosts on the network.

Well today I will explain what I do to create a new super user in PostgreSQL when I do a new installation on Debian systems. This procedure is usually extremely important to successfully connect remotely with the bank as security measures by some managers of PostgreSQL, as PHPPgAdmin [2], do not allow the connection to the super default user (postgres).

Must first install the package of PostgreSQL:

root @ srv: ~ # aptitude install postgresql-8.1

Then take the super user's default PostgreSQL, to create a new super user:

[root @ srv: ~] # su - postgres
[postgres @ srv: ~] $ createuser-P
Enter the name of the role to be added: pgmaster
Enter the password for the new role:
Enter it again:
The new role may create a super-user? (y / n) s
CREATE ROLE

We can try to create the user as follows:

[postgres @ srv: ~] $ psql
postgres = # \ du
List of roles
Name of role | Super-user | Create role | Creates BD | Connections | Member of
-----+-----+----+---+----+----
pgmaster | yes | yes | yes | unlimited |
postgres | yes | yes | yes | unlimited |
(2 records)

postgres = # \ q

To free access to the new user simply add the file / etc/postgresql/8.1/main/pg_hba.conf the following line:
"All local pgmaster md5", that already guarantees local access to other types of access you can edit the file in the desired way.

To recharge a final test of the PostgreSQL server is connected with the new user:

[root @ srv / etc / init.d] # ./postgresql-8.1 force-reload

[root @ srv / etc / init.d] # psql-U pgmaster template1

Pgmaster password for user:

Now a small tip for anyone who needs to use much in the PostgreSQL bash, is the prompt or some shell-script and not want to be passing parameters entering a username and password every time. In this case is only set these values in the environment variables for the special PostgreSQL, PGUSER and PGPASSWORD, use this:

export PGPASSWORD = password
export PGUSER = password

That is what I hope this helps someone!

[1] - http://www.postgresql.org/
[2] - http://phppgadmin.sourceforge.net/