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".
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.
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
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.
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.
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.