User Tools

Site Tools


database:pgsql:postgresql

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 USER

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

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
local   all         postgres                          ident sameuser
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          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.

Example:

listen_addresses = 'localhost, 192.168.63.222'

Make sure you restart postgres after you edited these configurations

service postgresql restart

or

killall -HUP postmaster

Test your database access

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

psql -U <username> <databasename>
Password: 
...

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

Logging

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

# Postgres
local0.*                -/var/log/postgres/postgres.log
/srv/wiki.niwos.com/data/pages/database/pgsql/postgresql.txt · Last modified: 2010/01/27 20:33 (external edit)