PostgreSQL Connectivity and Security Configuration Primer

One of the most frequent topics I see related to PostgreSQL in forums and on support lines is being able to establish simple remote connections to the database server.  While all the elements are well documented, I haven’t always seen good explainers related to how these elements related to one another nor systematic walkthroughs related to troubleshooting problems with PostgreSQL connectivity.

This is a shame because it truly is the world’s most advanced open source database and really the only one that is well suited for enterprise business class applications.  This article will make a concerted effort to be a primer that describes the different elements of PostgreSQL networking and security configuration and how they relate to one another.  What this article will not do is make you a security expert generally, or with PostgreSQL, nor will it give you a one size fits all formula that can be applied to any situation.  If you are unfamiliar with information technology security practices or how the information presented here may apply to your specific situation, I would strongly recommend that you engage a professional to address your needs.

This article will focus on PostgreSQL versions in the 8.X series and is written specifically with versions 8.3 and 8.4 in mind (please be sure to check the xTuple Supported PostgreSQL Versions Page for more information about which version is right for you). Other versions may work, but there may be some minor differences as well.  This article will also assume that you have basic computer administration skills, enough so that looking at a configuration file or two isn’t a daunting thought.  PostgreSQL is a simple to manage database compared others such as Oracle, but it is an enterprise class database with some complexity.

The 40,000 Foot, High Level Overview

When a client application makes an attempt to connect to a PostgreSQL instance there is a checklist of sorts which must be satisfied before an application can make use of a database in the following order (conceptually rather than technically):

Each one of the points above is handled by some aspect of PostgreSQL configuration with the exception of point number 2 which is a reference to an external firewall or similar application/device.   To answer these questions PostgreSQL will look at its currently running configuration and make a determination on whether or not you’ve satisfied each point along the way.  To overlay the configuration points with the checklist above I might re-write it thus:

There absolutely can be more to it than this, but these are the basics and satisfying these conditions will generally get you up and running with a networked PostgreSQL instance.  So from here forward we’ll dive into some of the specifics of this configuration.

PostgreSQL Configuration Files

There are two files that specifically deal with the ability to connect to a PostgreSQL instance over the network.  Assuming PostgreSQL has been compiled normally, they are:

The default location of these files can vary depending on how your PostgreSQL instance was compiled or packaged and which platform you’re running.  There are also ways to change the postgresql.conf location at server runtime.  Because of the wide variety of locations I can’t give you a location where you’ll find these files.  For that you’ll have to rely on the documentation for your specific instance or simply search for them.  (Yes, even the names of these files can be changed, but that’s pretty rare in my experience!)

postgresql.conf

The postgresql.conf file controls many settings related to the operation of the server.  The formal documentation for this file can be found at: http://www.postgresql.org/docs/current/static/runtime-config.html. For this discussion, we’ll look at a section labeled ‘CONNECTIONS AND AUTHENTICATION’.  Below is an example of this section from an 8.4.1 instance:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)

port = 5432                             # (change requires restart)

max_connections = 100                   # (change requires restart)
                                        # Note:  Increasing max_connections costs ~400 bytes of shared memory per
                                        # connection slot, plus lock space (see max_locks_per_transaction).

#superuser_reserved_connections = 3     # (change requires restart)

#unix_socket_directory = ''             # (change requires restart)

#unix_socket_group = ''                 # (change requires restart)

#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)

#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)


# - Security and Authentication -

#authentication_timeout = 1min          # 1s-600s

ssl = on                                # (change requires restart)

ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'       # allowed SSL ciphers
                                        # (change requires restart)

#password_encryption = on

#db_user_namespace = off


# Kerberos and GSSAPI

#krb_server_keyfile = ''

#krb_srvname = 'postgres'               # (Kerberos only)

#krb_caseins_users = off


# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0                # TCP_KEEPIDLE, in seconds;
                                        # 0 selects the system default

#tcp_keepalives_interval = 0            # TCP_KEEPINTVL, in seconds;
                                        # 0 selects the system default

#tcp_keepalives_count = 0               # TCP_KEEPCNT;
                                        # 0 selects the system default

The most important settings for basic TCP/IP connectivity are the listen_address and port settings.  By default, the listen_address setting is configured to ‘localhost’, usually meaning that PostgreSQL is only listening on the loopback address (127.0.0.1) which effectively means that you can only connect to the PostgreSQL instance from the same computer that the instance is installed on.  If you want remote computers to be able to connect to the instance, you must change this setting.  In the most basic circumstances this would simply be the IP address of the computer that the PostgreSQL instance is installed on or ‘*’, which means listen on all IP addresses that are configured on the computer where PostgreSQL is installed.  If the server has more than one IP address or more than one NIC (and therefore more than one IP address), this setting gives you to flexibility to pick and choose.  The next setting is the port setting.  The portsetting determines which TCP port the PostgreSQL instance will listen for remote connections on.  The default value of 5432 is usually sufficient and is the recognized port for PostgreSQL instances to be listening on.  The times when it would make sense to change the port number are primarily when you have more than one PostgreSQL instance on the same server.

Some may advocate that you should change the default port number setting on internet accessible servers in order ‘hide’ the instance from those looking to exploit the server.  I personally don’t find this a compelling argument because firstly security through obscurity, in this case making the listening port obscure, is not security and second there are any number of tools that can scan a computer on the internet for listening ports and determine what program is listening on those open ports.  The fact is that if you must have your PostgreSQL instance available to arbitrary computers on the open internet, you must use other techniques to securely offer the service other than simple port renumbering; to be fair, port renumbering won’t hurt, just keep your expectations  about the technique in check.

This should be sufficient to get the PostgreSQL instance on the network and listening for connections.  One topic we haven’t covered and is outside the scope of this article to cover in depth is that you should also be sure that if you have a firewall, be it a software firewall on the computer that will run PostgreSQL or an external firewall device, that will also govern connectivity to the PostgreSQL server, this must be configured to allow TCP/IP connections to both the listen_address and the port settings that you identified in the postgresql.conf file.  Some Linux distributions can be pretty tight about this especially if you don’t use their pre-built packages, so be aware that connectivity can be blocked by external factors outside of PostgreSQL configuration.

pg_hba.conf

The pg_hba.conf file controls configuration related to authentication, the process of making sure you are who you say you are.  PostgreSQL is somewhat unique amongst RDBMSs in that others will not have this intermediate level for defining connectivity permissions, relying either on networking elements (like firewalls, VLANS, etc) and simply on the internal database user constructs which is why many are thrown off by the pg_hba.conf file.  The formal documentation for this file can be found at: http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html.  The way the pg_hba.conf file enforces security is that it sets a number of hurdles that must be overcome, similar to firewall rules, based on what you are trying to do and from where you are trying to do it.  The hurdles that each pg_hba.conf entry must define can be summarized as follows:

It is worth nothing that between PostgreSQL versions 8.3 and 8.4 that there were some pretty significant changes (http://www.postgresql.org/docs/8.4/static/release-8-4.html#RELEASE-8-4-PG-HBA-CONF) for more advanced configurations, but for a basic configuration using MD5 method connections, these changes won’t be very relevant in that it won’t change what we do here for pre-8.4 versions or the 8.4 version.

The order of the rules in pg_hba.conf are important.  The first rule that matches any given connection attempt wins.  So when you’re troubleshooting connectivity problems, you must not only look to see that you have an appropriate pg_hba.conf entry for your specific situation, but that you also don’t have a conflicting earlier entry in the file that would deny access.

Below are some example pg_hba.conf entries:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               md5

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
hostssl xtuple_data fred        192.168.0.0/24        md5
hostssl all         all         10.0.0.0/8            md5
host    all         all         0.0.0.0/0             reject

# IPv6 local connections:
host    all         all         ::1/128               md5

You can see the layout of the different options that were discussed earlier in the example; TYPE, DATABASE, USER, CIDR-ADDRESS, and METHOD all on one line separated by whitespace.  While this is not a comprehensive list of scenarios that can crop up in pg_hba.conf (see the formal docs for that), it should be enough to cover many, if not most, circumstances.  Let’s take a look at this line by line.

The first entry: local all all <blank> md5.  This ‘local’ value for TYPE means connections made locally using UNIX sockets. DATABASE is set to ‘all’ meaning this rule matches all databases in the PostgreSQL instance.  USER is also set to ‘all’ meaning that this rule applies to all database users attempting to connect.  There is no CIDR-ADDRESS value since UNIX sockets don’t use IP networking.  Finally METHOD is ‘md5’ which means that users will authenticate with passwords stored in the database as part of the database user definition, rather than, say, some external method like LDAP.  So if a user connects to the instance where this pg_hba.conf file is running, and they are using a UNIX sockets based connection from the same server where the PostgreSQL instance is running, this rule will apply to them and allow them to connect so long as they supply a valid password.  Even if there was another rule for ‘local’ later in the file, it would never apply since this rule applies to all database and all users using the ‘local’ method (first matching rule wins).

The second entry: host all all 127.0.0.1/32 md5.  This entry applies to all databases and all users just like the first line, but here the connection TYPE is ‘host’, which means that this is expecting a connection over the network.  Even so, this will only allow connections to the same computer that PostgreSQL is installed on because the CIDR_ADDRESS parameter is 127.0.0.1/32 which is the loopback address in the networking stack (for more about CIDR network addresses check this link out); remote connections cannot be made to 127.0.0.1 so it must be local.  And, again just like the first example, the user must supply a password which will be checked against the database user set up in the PostgreSQL instance due to the METHOD of ‘md5’.  So the only difference is that the first example uses UNIX sockets to connect to the instance and this line allows the local computer to connect to the PostgreSQL instance using TCP/IP.

The third entry: hostssl xtuple_data fred 192.168.0.0/24 md5.  This time the TYPE is ‘hostssl’ which means that connections matching this rule must be encrypted SSL connections through the network.  Unlike our previous two examples, we identify a specific DATABASE (‘xtuple_data’) and a specific database USER (‘fred’) which to grant access.  Fred will still need to supply the password stored with his account in the database (METHOD = ‘md5’).  Finally, it’s not just enough that Fred uses SSL and only tries to connect to the ‘xtuple_data’ database, he may only do so from computers connected to the 192.168.0.0/24 network as defined by the CIDR-ADDRESS.

The fourth entry: hostssl all all 10.0.0.0/8 md5.  By now you should be catching on.  This entry lets any database user connect to any database in the PostgreSQL instance using an SSL connection across the network, so long as they are connecting from the 10.0.0.0/8 network and have the right password for the user they are using.

The fifth entry: host all all 0.0.0.0/0 reject.  This entry has a different purpose than the others, rather than giving permission to connect to the databases in the PostgreSQL instance, this line identifies who to keep out.  By using METHOD ‘reject’ we’re saying that anyone (USER = ‘all’) matching the criteria of a non-SSL connection (TYPE = ‘host’), to any database (DATABSE = ‘all’), using any computer or network (CIDR-ADDRESS = ‘0.0.0.0/0’) will not be allowed to connect.  Remember, though, that the order of the rules are important; if this fifth entry were before the second entry in the file, then users connecting from 127.0.0.1/32 would not be able to connect, but that entry is first so people logging in from the loopback address will be able to connect.  Another small point is that 0.0.0.0/0 is the catch-all CIDR-ADDRESS for all networks and computers, so all TCP/IP connections will match this CIDR-ADDRESS.  Finally, I must admit that I don’t see METHOD ‘reject’ used that often.  The reason is that PostgreSQL will default to reject any connection that isn’t matched by one the entries that allow access, so the circumstances that require METHOD ‘reject’ aren't common.

The sixth entry: host all all ::1/128 md5.  Lastly all this line does is show that if your system is setup to support IPv6, that pg_hba.conf can manage those connections as well by using standard IPv6 addresses in the CIDR-ADDRESS position.  All other options retain their same meaning.

Remember that this is a pretty limited view of an otherwise very rich set of pg_hba.conf options.  If you want to stretch out a bit, I strongly recommend taking a look at the formal documentation (http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html).  It also deserves repeating that just because the pg_hba.conf file permits a user to connect to the PostgreSQL instance, that the authorization for that user account inside of the database may still deny the user’s connection attempt: it takes both pg_hba.conf and the user account login privileges to successfully connect to a given PostgreSQL instance.

Finally, when you do change the pg_hba.conf you must reload the database instance before the new configuration will take effect.  This can be done with the postgresql program pg_ctl:

pg_ctl reload

from the command line, though you may have to specify the path to PostgreSQL’s bin directory if you don’t already have it in your PATH environment variable.

PostgreSQL Database Authorization and Database Roles

Database permissions and user management cover a broad range of topics and a small book could be written covering the options in depth.  These permissions run the gamut from allowing a user to connect to the instance, to defining which tables and views the user can access, to can they insert, delete, update or select them and even defines these permissions down to the individual table column level as of PostgreSQL 8.4.  Naturally, for this article we’ll stick to those permissions needed to establish a simple connection to the instance and the database.  Links to the formal documentation will be listed in context below.

Database Roles

PostgreSQL ‘roles’ are the heart of PostgreSQL’s user management capabilities.  In PostgreSQL a database role can be either a specific user or user group; a database role can be a ‘member’ of another role.  From there, authorization for the different activities in the database are granted to the roles.  Roles that represent individual users can inherit the permissions assigned to the roles for which that the user role is a member.  After installing PostgreSQL, are two roles that are (typically) automatically created: ‘PUBLIC’ and ‘postgres’.  The PUBLIC role is a default role of which every other role becomes a member automatically.  The ‘postgres’ user (and it might be named differently) is the built-in superuser account; other roles may be granted superuser privileges, but ‘postgres’ is created at installation and is the automatic owner of some of the key databases that are created at install.  The formal documentation for roles can be found at: http://www.postgresql.org/docs/8.4/static/user-manag.html. 

To create a role that can login, assuming you are logged in as the pre-existing ‘postgres’ user, at an SQL prompt you would type:

CREATE ROLE fred WITH LOGIN INHERIT ENCRYPTED PASSWORD ‘lemmein’;

or optionally from a shell command line you could use the special ‘createuser’ script found in the ‘bin’ directory of the of the PostgreSQL instance installation. Then the example would look like:

createuser --username postgres --login --encrypted --pwprompt --no-createrole --no-createdb --no-superuser --inherit fred

when you hit enter, the createuser script would prompt you for the new user’s password and then again to confirm it.  The createuser script is just a wrapper for the SQL commands in the CREATE ROLE example, except you must be more specific or the script will ask you what the correct setting will be.  More on the createuser script can be found at this link: http://www.postgresql.org/docs/8.4/static/app-createuser.html

What we said with both of these commands is create a new role with the name ‘fred’, and give that role the right to login to the instance (LOGIN).  We want this new role to automatically inherit the authorizations of the other roles of which it might be member (INHERIT), and we want to give it an md5 hashed password of ‘lemmein’ (ENCRYPTED PASSWORD).  Remember that the LOGIN right at this stage is not enough to let the user connect to the PostgreSQL instance by itself, the user still must meet the criteria defined in the pg_hba.conf in order to connect regardless of the LOGIN permission here.

To prevent ‘fred’ from logging into the PostgreSQL instance (without deleting his role), we would issue the follow SQL command at the SQL prompt:

ALTER ROLE fred WITH NOLOGIN;

Database Connect Authorization

Now we’re ready to grant permissions to connect to a specific database in the PostgreSQL instance.  In this case we’ll presume you want user ‘fred’ to be able to connect to a database called ‘xtuple_data’.  If ‘fred’ was assigned as the owner of the database when it was created, he would already have permission to connect to it; but we’ll assume that this is not the case for the following example.  To allow ‘fred’ to connect to database ‘xtuple_data’ you would enter the following SQL commands at the SQL prompt as superuser:

GRANT CONNECT ON DATABASE xtuple_data TO fred;

This is pretty self descriptive in that it does precisely what it sounds like it does: it grants ‘fred’ the right to connect to the database ‘xtuple_data’.  And, just as with the LOGIN permission, this grant only matters if first a pg_hba.conf entry is satisfied to allow such a connection.  If we wanted to make it so ‘fred’ could no longer connect to this database we would issue the following SQL command at the SQL prompt:

REVOKE CONNECT ON DATABASE xtuple_data FROM fred;

By this point you should have a PostgreSQL instance that you can connect to via the network, the ability to get logged in and the permission to connect to a database.  As you can see this is all pretty simple stuff, it just requires an understanding of the flow described in the High Level Overview. 

Steven C. Buttgereit is Manager of Consulting & Development Services for Muse Systems, a San Francisco, CA based business systems consultancy and xTuple Solution Provider Partner.