Secure connections to PostgreSQL

The PostgreSQL database server is arguably one of the best SQL servers available, but it's not as easy for beginners to get a handle on it as with other SQL databases, such as MySQL.

Where MySQL stores all of its credential information within the SQL database itself, PostgreSQL takes a two-pronged approach. Table access and password information are defined within PostgreSQL itself, but the actual connection information is stored in an external file, typically /var/lib/pgsql/data/pg_hba.conf. This configuration file determines who is able to connect to the database, and how.

PostgreSQL supports two types of connections: local and remote (via TCP/IP). As a result, the configuration file may look a little different depending on which connection type you are defining. For instance:

local     all           postgres                            trust
host      all           postgres      127.0.0.1/32            trust

The above determines the connection to the database from the postgres user. The first, identified as local, indicates a UNIX domain socket, or local, connection. The second, host, identifies a TCP/IP connection. You can tune this further by requiring TCP/IP connections over SSL by using hostssl; host implies any connection, encrypted or not.

The syntax of the file is as follows:

* local [database] [user] [method] [option]

* host [database] [user] [cidr-address] [method] [option]

As you can see, the primary difference between the two connection types is that with the host (or hostssl or even hostnossl) type, you must specify the CIDR address, which is made up of an IP address and a CIDR netmask; in the above example, 127.0.0.1/32 was used. You may choose to use either "192.168.0.0/24" or "192.168.0.0 255.255.255.0" -- either format will work.

The database field determines which database connection is being defined; the "all" keyword means all databases. The user field determines the user able to connect to the database over that connection. Finally, the method is the authentication method to use.

You can use either trust (no authentication), reject (reject the connection), password (plain-text password), crypt or md5 (encrypted passwords using either crypt or md5), ident (uses the ident protocol), krb5 (kerberos authentication), pam (uses PAM), or ldap (obtains credentials from an LDAP database). As you can see, the choice of authentication method is quite varied. Typical use would be to use the md5 method as it sends encrypted passwords.

To use encrypted passwords, you must alter a user's record within PostgreSQL to provide the encrypted password to match. For example, to add a new user with access to his own database, you could use the following commands:

# su - postgres
$ createuser joe
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE
$ createdb joe
CREATE DATABASE

$ psql
postgres=# ALTER USER joe WITH ENCRYPTED PASSWORD 'foobar';
ALTER ROLE
postgres=$ q
$ exit
# cd /var/lib/pgsql/data
# echo "local joe joe md5" >>pg_hba.conf
# service postgresql restart

At this point, user joe can attempt to connect to the database:

$ psql
Password:
joe=#

By the prompt, we can see that joe connected to the same database as his username. You can also see that a password was required. If, however, the attempt was made via the localhost:

re$ psql -h localhost psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "joe", database "joe", SSL off

This, of course, assumes that pg_hba.conf looks as follows:

local     all           postgres                            trust
host      all             postgres      127.0.0.1/32            trust
local     joe             joe       md5

Of course, this is just the tip of the iceberg, and it should be obvious to see what kind of fancy authentication schemes can be put into play, particularly if you look at the various supported authentication methods.