PostgreSQL

From HerzbubeWiki

Contents 1 Debian packages 2 References 3 Glossary 4 Starting/stopping the server 5 Managing database clusters 5.1 Create a new cluster 5.2 Delete a cluster 5.3 Upgrade a cluster 6 Database super user 6.1 Database role 6.2 System user 6.3 Access control 7 Roles 7.1 Users vs. roles 7.2 Managing roles 7.3 ~/.pgpass 8 Storage on disk 9 Databases 10 psql 11 Upgrading

Debian packages

postgresql
postgresql-client

References

External:

• PostgreSQL docs: http://www.postgresql.org/docs/
• PostgreSQL wiki: http://wiki.postgresql.org/wiki/Main_Page

On this wiki:

• TODO

On a Debian system:

• TODO

Glossary

Postgres / PostgreSQL 

The original project name was Postgres, indicating that this was the successor of the Ingres database system. At some point the project was renamed to PostgreSQL to indicate its use of SQL. There has been some discussion of renaming the project back to Postgres because of various problems with the new name (pronounciation among them), but so far it has not happened. I am not sure whether this is still an open issue.

Database cluster 

Where all databases of a PostgreSQL instance are stored

Starting/stopping the server

Debian allows to have more than one version of the PostgreSQL database server installed on the system at the same time. If you want to start/stop a specific version, do it like this:

/etc/init.d/postgresql start|stop 8.4

Managing database clusters

Create a new cluster

The following utility creates a new PostgreSQL database cluster for version 8.4, named "foo":

pg_createcluster 8.4 foo

The database, configuration and log files are stored in

/var/lib/postgresql/8.4/foo
/etc/postgresql/8.4/foo
/var/log/postgresql/postgresql-8.4-foo.log

Delete a cluster

The following utility completely destroys an PostgreSQL database cluster:

pg_dropcluster 8.4 foo

Upgrade a cluster

The following command upgrades the version 8.4 cluster named "foo" to the latest version of PostgreSQL available on the system:

pg_upgradecluster 8.4 foo

Notes (from the man page):

• The old cluster is given a new port so that the new cluster can keep the port
• The old cluster is set to manual startup mode, i.e. it is no longer automatically started during system boot
• After verifying that the new cluster works as expected, the old one should be destroyed with pg_dropcluster

Database super user

Database role

Installing the PostgreSQL packages creates a database cluster whose database super user by default is

postgres

This is a role defined in the administration database "postgres", in the table "pg_roles", whose "rolsuper" attribute is set to true. Although I haven't been able to confirm this (yet), I strongly suspect that the role by default is not secured with a password. The section "access control" further down explains why this is not a problem.

System user

Installing the PostgreSQL packages creates the system user

postgres

The user does not have a password set in /etc/shadow, which makes it impossible to login using a regular login mechanism (PAM). However, it is possible to use the su command to assume the user's identity.

Access control

The default configuration of PostgreSQL on Debian ensures that only the system user "postgres" is allowed to connect as the database super user "postgres". This is achieved by an entry in

/etc/postgresql/8.4/main/pg_hba.conf

The entry is this:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         postgres                          ident

Since nobody can login as the system user "postgres", and only the system super user "root" is allowed to switch user (su command line utility), access as the database super user is effectively restricted to the system super user.

Roles

Users vs. roles

In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities. In newer versions of PostgreSQL there are only roles. The SQL commands "CREATE USER", "ALTER USER", "DROP USER", etc., still exist but are aliases to "CREATE ROLE", "ALTER ROLE", "DROP ROLE" etc.

While the concept of "users" is implemented by using roles, the two are not entirely synonymous. Specifically, a role that represents a user usually has the privilege to log in, while a non-user role does not.

Consequently, the SQL command "CREATE USER" creates a role with the login privilege, while "CREATE ROLE" creates a role without login privilege by default.

Managing roles

The following SQL commands are used to manage roles:

• CREATE ROLE
• ALTER ROLE
• DROP ROLE

For managing user roles, there are two convenience command line tools that are wrappers around their corresponding SQL commands. The two utilities have man pages that can be consulted for details:

createuser   # wraps "CREATE ROLE"
dropuser     # wraps "DROP ROLE"

To list all roles that currently exist:

su postgres -c "psql -c 'SELECT rolname FROM pg_roles;'"

Changing a role's password:

alter role foo with password 'secret';
alter role foo with password null;

Because the SQL command includes single quotes, I was unable to form a single command line that uses both su and psql to change the password with SQL commands. The only alternative I have found so far is to change the password interactively in a psql session.

~/.pgpass

Passwords for certain users/roles can be stored in a password file which by default is located in the user's home directory and is named

~/.pgpass

The libpq library (which is also used by the psql utility) reads this file and uses passwords it finds there to perform automatical authentication. The format of the file content looks like this:

hostname:port:database:username:password 

For instance:

*:*:davical:davical_dba:secret

The password file must be protected like this:

root@pelargir:~# l /var/lib/postgresql/.pgpass 
-rw------- 1 postgres postgres 51 Jun 13 23:03 /var/lib/postgresql/.pgpass

In the example above, the file is located in the home directory /var/lib/postgresql of the PostgreSQL super user postgres.

Storage on disk

Depends on the version of PostgreSQL being used, and the name of the database cluster. At the time of writing, I had PostgreSQL 8.4 installed, so the directory that stores the data for the "main" database cluster is this:

/var/lib/postgresql/8.4/main

Databases

Create a new database

createdb dbname
createdb -O rolename dbname   # specify the role that is the owner of the database
psql -c 'CREATE DATABASE dbname OWNER rolename;'

Database backup:

sudo -u postgres pg_dumpall >/var/backups/postgresql/all-databases.sql
sudo -u postgres pg_dump --clean davical >/tmp/davical.sql  # davical = database name

Restore of a database:

psql -d davical </tmp/davical.sql

Connect to a database "davical":

sudo -u postgres psql davical

List all databases, including their owners

sudo -u postgres psql -c '\l'

psql

The command line utility psql is a terminal-based frontend to PostgreSQL. It can be used both interactively and in "automated mode". Read the tool's man page for details.

Some hints:

• The command "\d" lists all tables (and other objects) of the current database, including the owner

Upgrading

• Installing a new Debian package will create a new cluster for the new version
• It may be possible to upgrade the old cluster, but I have never tried this

pg_upgradecluster 9.0 main

• Instead I usually copy the access rules from the old to the new cluster. The file that needs updating is

/etc/postgresql/9.1/main/pg_hba.conf

• Probably it will also be necessary to upgrade the new cluster's port in

/etc/postgresql/9.1/main/postgresql.conf

• In addition, create database backups from the old cluster and restore the backups to the new cluster. Example for the davical database:

# old cluster
sudo -u postgres pg_dump --clean davical >/tmp/davical.sql
# recreate roles on new cluster
createuser --no-superuser --no-createdb --no-createrole --pwprompt --encrypted davical_app
createuser  --no-superuser --no-createdb --no-createrole --pwprompt --encrypted davical_dba
# recreate database
createdb -O davical_dba davical
# restore database content from backup
psql -d davical </tmp/davical.sql