1: Introduction

This article describes the basic procedures involved in administering a new PostgreSQL RDBMS installation, including creating users, databases and backups. It is assumed that PostgreSQL is already installed, initialised and running on a RedHat-derived server (RHEL, CentOS, Fedora etc).

2: The postgres superuser

PostgreSQL is managed by an internal superuser known as postgres. The postgres user can create other users with a range of admin privileges, but initially all management must be done via this user. In order to create new databases and database users, it is necessary to switch user (su) to user postgres. However, in order to do this, it is necessary to first switch user to the root user. In other words, by default it is only possible to su to postgres if you are already root. Note the hyphen (-) after su. It is important to include this so that the postgres user switches into the correct shell:

$ su
# su - postgres

By default, the postgres user account does not have a password. While not necessary, a normal Unix user account password can be added to the postgres accountat this point using the standard passwd utility, thereby making it possible to switch user (su) to postgres from any user account:

# passwd postgres
Changing password for user postgres.
New UNIX password: 
Retype new UNIX password: 
passwd: all authentication tokens updated successfully.

3: The psql console

Once logged in as the postgres user, the PostgreSQL management console can be started by running the psql command with no arguments. This produces the following opening message:

$ psql

Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

The psql console allows full control of the PostgreSQL RDBMS, using both PostgreSQL-specific commands and SQL statements. For a full list of psql commands, type \?. SQL commands can be typed directly on the command line, but must end with a semi-colon. To exit psql and return to the postgres user's shell prompt type \q and press Enter.

The first task we'll complete in the psql console is to provide the postgres superuser with a password. Note that this is a PostgreSQL-internal user password, not a system user password as discussed above. This is necessary because we are going to configure md5 authentication in section 4 below, which requires the postgres user to also use a password.

postgres=# alter role postgres with password 'secret';

4: PostgreSQL configuration files

PostgreSQL stores its configuration files in /var/lib/pgsql (on RedHat and CentOS at least). Doing an ls while logged in to the postgres user's shell displays several directories and files. The data directory contains the PostgreSQL configuration files, which can only be edited by the postgres user.

On development systems, the only configuration that is typically required involves setting the authentication method in /var/lib/pgsql/data/pg_hba.conf. The default method (ident sameuser) fails when the Unix user performing database operations is different from the PostgreSQL user account invoked to perform those operations:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          ident sameuser
# IPv6 local connections:
host    all         all         ::1/128               ident sameuser

The default options can be changed to trust to allow anyone who can connect to the server to access a database with whatever database user name they specify (database and user columns allowing). Trust authentication is appropriate and convenient for local connections on a single-user workstation, but should be avoided on multi-user systems.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

A safer option of course is to require a password. The md5 method requires a PostgreSQL password:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

5: Creating an admin user

At this point, as user postgres, it is possible to create new users who can own and manage databases. However, best practice suggests that an intermediate level user, with the necessary privileges to create other users and databases, be created first. This is avoids using the postgres superuser to accomplish routine administration tasks, in a similar fashion to avoiding unneccesary use the root user account for general Linux administration - it is insurance against inadvertent errors made while logged in as the superuser.

There are two ways to create PostgreSQL users of any kind, either by using the command-line utility createuser or by using SQL statements in the psql console. The following examples illustrate how each method can be used to create an intermediate level user (dbadmin):

5.1: Creating an admin user with the createuser utility

Invoked by the postgres user (in the postgres user shell), an intermediate user can be created with the createuser utility, which will prompt for the new user's password and further information about the user's privileges:

$ createuser -P dbadmin

Enter password for new role:  <secret>
Enter it again:  <secret>
Shall the new role be a superuser? (y/n)
Shall the new role be allowed to create databases? (y/n)
Shall the new role be allowed to create more new roles? (y/n)
CREATE ROLE

After providing the password, answer NO to the first question and YES to the following two questions. This creates a user (dbadmin) with intermediate privileges who can create users and databases but is not a full superuser.

This user could also be created using the following single command, which answers the previous interactive questions with command-line switches:

$ createuser -S -d -r dbadmin

Enter password for new role: <secret>
Enter it again: <secret>
CREATE ROLE dbadmin PASSWORD 'md5b5f5ba1a423792b526f799ae4eb3d59e' CREATEDB CREATEROLE INHERIT LOGIN;

The createuser man page has a good summary of the options available for this command.

5.2: Creating an admin user using an SQL statement

Users can also be created using SQL statements in the psql console. The CREATE ROLE command can be used with various options to create an admin user with password and appropriate privileges as follows. Note that the password is automatically encrypted using the md5 algorithm:

CREATE ROLE dbadmin PASSWORD 'secret' CREATEDB CREATEROLE INHERIT LOGIN;

6: Creating a regular database user

We can now use our newly-created dbadmin user for administrative tasks such as creating normal database user accounts as follows:

$ createuser -U dbadmin -P dbuser

Enter password for new role:  <secret>
Enter it again:  <secret>
Shall the new role be a superuser? (y/n)
Shall the new role be allowed to create databases? (y/n)
Shall the new role be allowed to create more new roles? (y/n)
Password:

Normal users don't require the ability to create databases or new roles, after providing the new user's password the answer to the next three questions is no. The final password prompt is for the dbadmin user's password.

New users can also be created in the psql console with one of the following two SQL commands:

CREATE ROLE dbadmin LOGIN PASSWORD 'secret';

or

CREATE USER dbadmin PASSWORD 'secret';

These commands produce equivalent results, which are to create a user with a password and login permissions. CREATE USER is equivalent to CREATE ROLE except that LOGIN is assumed by default, whereas it must be explicitly declared with a CREATE ROLE statement. Only roles that have the LOGIN attribute can be used as the initial role name for a database connection. A role with the LOGIN attribute can therefore be considered synonymous with a "database user".

7: Creating a database

Now we can create a database and assign ownership of it to a database user, who will be able to login to and manage the database using the password assigned via one of the methods outlined above.

From psql's SQL environment the command is:

CREATE DATABASE db_name OWNER dbuser;

Or from the shell the same result can be achieved with the createdb command:

$ createdb -O dbuser db_name

Note that you must be a superuser to be allowed to create a database for someone else (that is, for a role you are not a member of).

# createdb -U dbadmin -O dbuser db_name
Password: 
CREATE DATABASE

8: SELinux

By default RHEL, CentOS and Fedora servers enable SELinux in enforcing mode. While SELinux can easily be disabled, it is far better from a security point of view to leave it enabled and learn how to configure it appropriately. By default, enforcing mode blocks network access by the web server to a database server, which can be a source of immense frustration to the unwary and often results in SELinux being summarily disabled once it is unmasked as the culprit. The required access can easily be enabled while retaining the protection SELinux provides by setting the httpd_can_network_connect_db boolean to on in the following manner:

# setsebool -P httpd_can_network_connect_db on

The -P flag used here makes this change permanent. Omitting this flag would temporarily allow database connections from the web server, but the changes would be lost on a subsequent reboot.

9: Backup and restore

The pg_dump utility 'dumps' databases and database tables into a text file, enabling their backup and restoration.

In the following example, user dbuser backs up the table tbl_users in the database db_website to users.sql, a plain text file containing everything required to rebuild the table and populate it with data. Note that the command in this and following examples will typically produce a password prompt:

$ pg_dump -U dbuser -O -D -t tbl_users db_website > users.sql

To dump the entire database, simply run:

$ pg_dump -U dbuser -O -D db_website > website.sql