PostgreSQL - making connections #1

Following installation, access to our PostgreSQL database server is highly restricted owing to its default configuration — this was first explained in the local connections as postgres article.

The present article and the next two will explain how to enable postgres for less restricted local and remote connections, a necessity if we're going to use it as the datastore for our web applications, or connect to it remotely with a database admin tool.


Going forward, I'll assume you have two databases and two normal database roles: 'demodb1' owned by role 'demorole1', and 'demodb2' owned by role 'demorole2'.

I'll also assume you have a superuser role named "mysuperuser".

See the creating and dropping roles and creating and dropping databases articles if you need help with role and database creation.

pg_hba.conf - Client authentication

The postgres configuration file 'pg_hba.conf' specifies how client connections are authenticated. We took a brief look at this file in the local connections article. We'll take a closer look now and make some changes.

For a full treatment of PostgreSQL client authentication, refer to Chapter 21 in the official documentation.

Local connections

Let's open this file and take a look:

sudo nano /etc/postgresql/8.3/main/pg_hba.conf

Find this part:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser

So when referring to postgres and "local connections", this means Unix domain socket connections.

Many Linux software packages know how to communicate with other local processes using domain sockets, including the popular web frameworks PHP and Ruby on Rails. Also, psql defaults to using domain socket connections.

The current setting in 'pg_hba.conf' allows domain socket connections to "all" databases, from "all" users (roles with the LOGIN attribute), using the "ident sameuser" authentication method.

Essentially, "ident sameuser" requires the role (user) specified for a connection to match the name of the Linux user invoking that connection. Many web developers will be unfamiliar with coordinating such a credentials scheme for their database, app and slice, so we'll change the third line to read:

local   all         all                               md5

The "md5" authentication method requires the client to supply an MD5-encrypted password matching the value stored in the specified role's PASSWORD attribute.

After saving our changes, we need to restart postgres to apply them:

sudo /etc/init.d/postgresql-8.3 restart

Let's see if it worked.

Logged into my demo slice as a Linux user named "mike" I'll attempt a local connection with psql. The '-U' flag specifies the role 'demorole1':

mike@demo:~$ psql -U demorole1
...
Password for user demorole1:
...
psql: FATAL:  database "demorole1" does not exist

What happened?

This is an important point: any time we connect to postgres, we have to specify a database in addition to a role. If we don't specify a database, postgres assumes we mean to connect to one having the same name as the specified role. But we haven't created a database named "demorole1", hence the error. This requirement applies even to superuser roles.

Let's try again, using the '-d' flag to specify a database:

mike@demo:~$ psql -U demorole1 -d demodb1
...
Password for user demorole1:
...
Welcome to psql 8.3.6, 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

demodb1=>

Great, we're in! You can exit any time with CTRL-D or the '\q' command.

Overlapping entries

What if I try to connect as Linux user 'mike' specifying the 'postgres' role?

mike@demo:~$ psql -U postgres -d postgres
...
psql: FATAL:  Ident authentication failed for user "postgres"

Recall this section in 'pg_hba.conf', which we looked at in the previous local connections article:

# Database administrative login by UNIX sockets
local   all         postgres                          ident sameuser

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

For local (domain socket) connections, the "ident sameuser" authentication method is specified for the 'postgres' role, overriding our option for "all" roles to authenticate with MD5-encrypted passwords. For our default superuser role this is a very reasonable setting which we'll leave unmodified.

An important point to remember: postgres will always respect the most restrictive setting in 'pg_hba.conf' when entries overlap for a particular role and connection type.

Summary

We've enabled password authenticated local (domain socket) connections for our postgres server, which is less restrictive than the default configuration.

Many popular software packages can use domain sockets, but we'll also want to configure our postgres server for local and remote TCP connections. The next article explains those steps.

Mike

Article Comments:

Chris commented Tue Nov 10 18:20:33 UTC 2009:

Your fix for the ident error doesn't work with 8.4.

"authentication option not in name=value format: sameuser"

Jay commented Sun Nov 15 03:12:56 UTC 2009:

Chris,

On 8.4.1, I've had success with just "ident".

estani commented Thu Apr 15 08:24:24 UTC 2010:

The syntax of pg_hba.conf got changed from 8.4 on. http://www.postgresql.org/docs/8.4/static/release-8-4.html#RELEASE-8-4-PG-HBA-CONF

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)