PostgreSQL supports a few methods of user authentication. Here's a look into various methods that are supported.    Now PostgreSQL is set up, we need to set up some alternative authentication methods. This will make things easier later on when we have to create databases and possibly other users.

PostgreSQL users are different to system users and need to be set up differently.

The authentication file is located in the data directory for postgresql - if you followed the source installation, it'll be in the /usr/local/pgsql/data directory.

Look for a file called 'pg_hba.conf' (this stands for 'Host Based Authentication').

Before starting, back up the original just in case we make a mistake :)

At the bottom of the file, it has the following:

# 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


This all looks rather confusing but it will become clear as we go :)

We'll concentrate on one section only (local), the same principle applies to the other sections if you need them.


# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all trust

'local' means you are on the same computer (server). You are either SSH'ed in, or you are running PostgreSQL server with a PHP (or perl, python, ruby etc) script that connects to it. Your script doesn't have to connect to another server on the network.

The 'CIDR-ADDRESS' column is only used for network addresses, either internally or externally. They can be specific IP's (eg 10.0.0.1/32) or ranges (eg 10.0.0.0/24). Because a socket interface doesn't have an IP address, this column is left empty.

The 'methods' of authentication is probably the next part to look at.

The valid methods are:
trust, reject, md5, crypt, password, krb4, krb5, ident, pam

'Trust' means accept all connections from this host. This is the least secure, because as long as you have a valid postgres user, you can connect to the database.

For example, as the user 'tomcat' I can do:

$ /usr/local/pgsql/bin/psql -U postgres -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(2 rows)


'Reject' rejects all connections from that host for that specific user.

The next 3 - md5, crypt and password - are all different password types. MD5 and crypt are obvious, password on the other hand is a little different. It sends the password in clear text and isn't very secure (this is mainly provided for backward-compatibility).

krb4 and krb5 are for kerberos authentication and are out of the realm of this tutorial.

ident checks whether you are the same user as the database user and either allows or denies access that way. You can provide an ident map from username to database user, but that's getting quite complicated and out of the realm here.

PAM uses the system pam module (called 'postgresql' by default), so see system do(edited)entation on setting that up.

There is a lot more documentation on the postgresql website about the different authentication types (http://www.postgresql.org/docs/8.0/static/client-authentication.html).

I'm going to change the authentication method to 'md5' and set up another 'database superuser' so we don't have to keep changing different system users.

The database column has some different options. It can be 'all', a specific database name (or specific names separated by a comma), or 'sameuser' or 'samegroup'. Sameuser means the database name is the same as the system user name. Same group means they are in the group with the same database name. For example, the database is called 'admins'. Anybody in the system group 'admins' can get access to the specified database.

You can build up quite a secure server using any or all of these options.

I've set mine up so that the 'postgres' user authenticates if you're logged in as that user, otherwise you require a password:

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

The same logic applies to the other options (host for IPv4 and IPv6).

Once you've set everything up how you like it, you'll need to restart your database server:

$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/logfile restart

Now that that's all set up, we can create other users.