User Tools

Site Tools


PostgreSQL Administration

Create new users and databases

Make sure postgres is running

service postgresql start

If you start postgresql for the first time on Red Hat all system databases and users will be created.

You have to log in with the user postgres

su - postgres

Create a database user

createuser -P <username>
Enter password for new role: ****
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n

Create a database for the user

Option O stands for Owner.

createdb -O <username> <databasename>

Access control

The initial settings of postgresql are very strict. In order to let user access databases, you have to edit the pg_hba.conf file.

Open the file for editing:

vi /var/lib/pgsql/data/pg_hba.conf

On eul0000598 the path is:

vi /postgres/data/pg_hba.conf

Example config

# "local" is for Unix domain socket connections only
local   all         postgres                          ident sameuser
local   all         all                               md5
# IPv4 local connections:
host    all         all          md5

If you want to enable remote access on the database server you have to change the “listen_addresses” directive in postgresql.conf as well.


listen_addresses = 'localhost,'

Make sure you restart postgres after you edited these configurations

service postgresql restart


killall -HUP postmaster

Test your database access

For users other then postgres you should be asked for the password (md5).

psql -U <username> <databasename>

For user postgres you should be able to login without password (sameuser).

List existing databases

psql -U postgres
> \l+
                        List of databases
      Name       |     Owner     | Encoding |        Description
 db_app1 | swatchinstant | UTF8     |
 db_app2 | theclub       | UTF8     |
 postgres | postgres      | UTF8     |
 template0 | postgres      | UTF8     |
 template1 | postgres      | UTF8     | Default template database


Configure /etc/syslog.conf to log all postgres messages to one file:

# Postgres
local0.*                -/var/log/postgres/postgres.log
/srv/ · Last modified: 2010/01/27 20:33 (external edit)