PostgreSQL Server Security Primer

PostgreSQL or simply referred to as Postgres has had a very colorful history.  It began in 1986 as a POSTGRES project led by Professor Michael Stonebraker, which was sponsored by Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc.   Postgres95 was released in 1994 for general use on the web which included support for SQL language interpretation.  In 1996, Postgres95 was officially renamed to PostgreSQL and PostgreSQL 6.0 was released and the rest of it is history.  Today, PostgreSQL is one of the most popular open-source object-relational databases in use. DB-Engines ranks PostgreSQL as the fourth most popular relational database management system (RDBMS) as of September 2020.  In my opinion, PostgreSQL is overtaking MySQL in popularity after Oracle acquired Sun Microsystems in 2010.  At the same time, it should be noted that Widenius forked the open-source MySQL project to create MariaDB which is gaining popularity in recent years.

When it comes to security, all the RDBMS engines follow a similar pattern with subtle differences.  In this blog, I will walk you through the security implementation in PostgreSQL.  For the purposes of this blog, I am using open-source PostgreSQL 12.0 distribution running on CentOS 8 server. Before jumping into PostgreSQL security concepts, I need to point out that data rows in tables are referred to as tuples in PostgreSQL. If you are coming from other popular RDBMS platforms, such as Oracle or SQL Server just be aware that the word tuple is simply a formal way of referring to a row of data in a table.

At the heart of PostgreSQL security is the pg_hba.conf, which controls database access is via host-based access rules.  It restricts user access based on OS user, database user, database, connection type (local or remote).  PostgreSQL also supports multiple authentication mechanisms.  Starting with PostgreSQL 10, support for Salted Challenge Response Authentication Mechanism (SCRAM) was added and is based on RFC7767.  Support for SCRAM-SHA-256-PLUS was added in PostgreSQL 11.  PostgreSQL supports both SSL and TLS end-to-end encryption of all traffic between the client and server.

In addition to a very robust database server access, PostgreSQL supports role-based access to data and also provides row-level security (RLS), which was introduced in PostgreSQL 9.5.

With a fresh install of PostgreSQL engine, it only supports local connections using sockets on both Unix/Linux and Windows operating systems.  External clients will not be able to access the database via TCP/IP. The default configuration is ideal if an application ships with PostgreSQL as a back-end database and is installed as the same server as the application.  In situations where external clients need to connect to PostgreSQL engine via the network, the PostgreSQL engine needs to be configured with external network-based access.  This is accomplished by altering the configuration settings in postgresql.conf file located in the $PGDATA or PostgreSQL data directory. Besides security, another reason to establish a local socket-based connection is performance.  PostgreSQL client, such as psql will establish a Unix socket connection to the database.  However, if needed a network-based connection can be forced by specifying the hostname as part of the connection string provided that the postgresql.conf is set correctly.  In the screenshot below, I connected to the PostgreSQL engine using a Unix socket and is verified using the \conninfo command. Unix socket-based connection does not require a network for the client connection, however, requires a directory.  The directory must be unique for each instance of PostgreSQL, if you have more than one instance running on the same host.  In my case, PostgreSQL is using the default directory /var/run/postgresql.  This can be changed by modifying the following parameter in the postgresql.conf file to the directory of your choice, it is commented out by default.  PostgreSQL allows for the listing of more than one directory separated by a comma.

unix_socket_directories = ‘var/run/postgresql, /tmp’

In order to allow clients to connect to PostgreSQL across the network, we need to modify the settings in the postgresql.conf file and either restart the PostgreSQL engine or force a reload of the configuration file. The two parameters that control network client access are listen_addresses and port in the postgresql.conf file. Be default the listen_addresses is set to ‘*’, meaning PostgreSQL will listen to all the IP addresses assigned to the host server including the loopback address (127.0.0.1). The port is set to a default value of 5432, which is a well-known port assigned to PostgreSQL by Internet Assigned Numbers Authority (IANA).  However, you are free to choose any port of your liking.  Both listen_adresses and port are commented out in the postgresql.conf file.  In the screenshot below, I set the listen_addresses to the hostname sblinpsql12 and left the port set to 5432.  After saving the changes, I forced a reload of the configuration as shown in the screenshot below.

The screenshot below shows the two settings that I changed in the postgresql.conf file located in the $PGDATA directory.

After making the parameter changes and loading the new configuration changes, if you review the connection info, it clearly shows that it is using the network to establish the connection.

Now that the necessary changes have been made to the postgresql.conf to allow external connections, the next step is to configure the security settings to allow the client to connect to the PostgreSQL engine.  The PostgreSQL controls local and network authentication based on the settings in pg_hba.conf file, which is also located in the $PGDATA directory and is unique to each instance of PostgreSQL.  In PosgreSQL 12, there are six different connection rules that can set as shown in the screenshot below.  Depending on the version of PostgreSQL, you might find less than six rules in the pg_hba.conf file.

Let us examine all the six different connection rules that is available in PostgreSQL 12.

• local: This setting can be used to allow Unix socket-based connections only, which is the only means to connect to the PostgreSQL engine by default.
• host: This setting is used to configure either plain or SSL-encrypted TCP/IP socket connections.
• hostssl: This setting is used to only allow SSL-encrypted TCP/IP socket connections and requires the ssl parameter in postgresql.conf to be set to on.
• hostnossl: This setting unlike the host parameter can be used to only allow non-SSL TCP/IP socket connections and will explicitly reject any SSL-encrypted connection requests.
• hostgssenc: This setting uses GSSAPI (General Security Service Application Program Interface) encryption for authentication and is based on RFC 2743.  When this setting is set, only connections that are encrypted with GSSAPI are allowed.
• hostnogssenc:  This setting is the exact opposite of hostgssenc, and will only allow TCP/IP socket-based network connections that do not use GSSAPI encryption. 
• DATABASE: The default parameter is all, which means, all authenticated clients can access any database within the instance.   However, clients can be restricted to one or more databases by explicitly specifying the database name(s).  If multiple databases are being listed, they need to be separated by a comma.  A separate file containing the list of database names can also be specified by designating the filename preceded with the @ symbol.
• user: The default value is all, meaning that all users with valid authentication credentials can connect.  A list of users or roles can also be specified separated by a comma.  A separate file containing the list of users or roles can also be specified by designating the filename preceded with the @ symbol.  Please note that if the role is not specified by preceding the role name with the + sign will restrict the login to the role only.  By specifying the + sign, all the users that are part of that role will be allowed to log in.  The reason being, in PostgreSQL there is no real distinction between users and roles.
• address:  The client IP address(es) or hostname(s) that are allowed to connect to the PostgreSQL engine.  The IP address can be specified with the CIDR mask preceded by a slash (/).  For example, 10.10.10.88/32 will restrict connection to a single IP address 10.10.10.88 because the CIDR mask is 32 bytes, meaning all 32 bytes will be matched.  If 10.10.10.0/24 is specified any IP address starting with 10.10.10.xxx will be allowed to connect.  If 10.10.0.0/16 is specified, the first 16 bytes will be matched and any host starting with an IP address 10.10.xx.xx will be allowed to connect.  If you prefer to allow connections from any IP address, then setting the value of 0.0.0.0/0 will match any IP address in your network. 

The final option is the METHOD, which is the client authentication method used for validating the connection.  The following the list of possible authentication mechanisms.

• peer:  This setting will restrict the logins to operating system defined users only, provided it matches the database username.  In addition, users are not required to provide a password.  A perfect example is the postgres user.  During the PostgreSQL installation process, an OS user called postgres is created and a corresponding user is created in the database as well, including a database with the same name. 
• trust: This setting will essentially allow users to connect without a password if the user is defined in the database.  I strongly recommend against using this setting for regular users.  A good use of this setting is for a user used for replication.
• reject:  This setting will reject all connections for a specified list of user(s), role(s), IP address(es) or IP segments.  The rules can be listed one after another, however, the order in which they are specified makes a difference.  For example, let us say that you want all the users starting with an IP address of 10.10.10.xx can connect but you would like to reject any connection attempts from a specific IP address, say 10.10.10.101.  The setting should like shown below. 

host		all		all		10.10.10.101/32 reject
 host		all		all		10.10.10.0/24   md5

However, if you accidentally reverse the order, the first rule takes effect and a client with IP address 10.10.10.101 can connect.

• md5: This setting will send the password across the network in an encrypted format. It is derived by combining the username and password and computing the md5 hash value. Please note that md5 encryption is not secure and I recommend that you use scram-sha-256 instead.
• password: This setting will send the password in plain-text format across the network and is not a preferred setting.  I cannot think of a single use-case that would require this setting.
• scram-sha-256: This setting will send the password that is encrypted using scram-sha-256 and is more secure than md5.  PostgreSQL uses 4096 iterations when computing the hash value, the number of iterations can be customized. It appends a 16-byte salt that is generated using a Cryptographically Secure Pseudorandom Number Generator (CPRNG) to the password hash.  This option is available starting with PostgreSQL 10 and is similar to mechanisms used in other popular RDBMS platforms.
• ident: This setting will obtain the operating system username of the client by contacting the ident server on the client and check it against the requested database user name.  This setting will work for TCP/IP network socket-based connections. 
• ldap:  This setting will users to authenticate using a Lightweight Directory Access Protocol (LDAP). It also requires changes to the pg_hba.conf file with the details about the LDAP server and requires the users to be defined in the PostgreSQL database as well. 
• gss: This setting will use GSSAPI to authenticate users and will only work for TCP/IP network socket-based connections.
• sspi: Security Support Provider Interface (SSPI) unlike GSSAPI is an API developed by Microsoft and is used in Windows systems to provide single sign-on.  This authentication mechanism is supported only when both the PostgreSQL server and client are running on Windows operating systems. 
• radius: This is setting is similar to password with the exception that the Remote Authentication Dial-in User Service (RADIUS) server is used for password verification. 
• cert: This setting uses SSL client certificates to authenticate connections and does not require a password.  The Canonical Name (CN) of the client certificate is matched against the database username and if they match, the client is allowed to connect. 
• pam: This setting uses Pluggable Authentication Modules (PAM) to authenticate connections.  The default PAM service name is postgresql and is used to verify the username and password combination and still requires the user to be present in the database.
• bsd:  This setting uses BSD Authentication mechanism to validate the username and password combination and also requires the user to be present in the database.

The screenshot below shows the pg_hba.conf file on my server.  I have configured all local connections to use md5, thus forcing local users to enter a password.  In addition, I have changed the ADDRESS setting to allow connections from any IP address using md5 password encryption for METHOD.  For IPv6 connections I have set scram-sha-256 password encryption.  For replication users, I have left the default setting in place.  I will be changing the USER settings to actual usernames as the default setting allows all users with the replication role.

Finally, please make sure that you carefully review the security settings in both postgresql.conf and pg_hba.conf and secure your PostgreSQL implementation from hackers and unauthorized users.  Given the complex nature of database security, consider using StealthAUDIT for Databases, a purpose-built database security tool to audit your database security. To learn more about how StealthAUDIT for Databases can help in auditing database security and regulatory compliance please visit our website at https://www.stealthbits.com/database-security-solution