1. Introduction

PostgreSQL is packed with several security features for a database administrator to utilize according to his or her organizational security needs. The word Security is a very broad concept and could refer to completely different procedures and methodology to achieve in different PostgreSQL components. This blog is divided into part 1, 2 and 3 and I will explain the word Security with regards to PostgreSQL version 12.1 and how it is practiced in different areas within the system.

In Part 1 of the blog, I will be discussing the basic security features that exist in PostgreSQL with emphasis on Host-based authentication methods as well as user-based access control with the concept of roles. If done right, we could have a much more robust database server and potentially reduce the attack surface on the server, protecting it from attacks like SQL injections. I will also briefly discuss a few of the advanced authentication methods such as LDAP and PAM authentication. There are many more advanced authentication methods supported and we will be producing more articles in the near future to cover more of these methods.

In Part 2 of the blog, I will be discussing TLS in greater detail, which I believe is crucial for a database administrator to understand first before enabling TLS in the PostgreSQL server. TLS is a fairly large and one of the least understood protocol today, which contains a lot of security components and methodology related to cryptography that could be quite confusing.

In Part 3 of the blog, I will be discussing how to apply TLS configurations to both PostgreSQL server and client following the TLS principles that have been discussed in Part 2. I will also briefly discuss Transparent Data Encryption (TDE) that the PG community is currently working on that introduces another layer of secured database environment.

Below is the overview of the security topics that will be covered in all parts of the blog:

Part 1:

• PostgreSQL Server Listen Address
• Host-Based Authentication
• Authentication with LDAP Server
• Authentication with PAM
• Role-Based Access Control
• Assign Table and Column Level Privileges to Users
• Assign User Level Privileges as Roles
• Assign and Column Level Privileges via Roles
• Role Inheritance

Part 2:

• Security Concepts around TLS
• Symmetrical Encryption
• Asymmetrical Encryption (a.k.a Public Key Cryptography)
• Block Cipher Mode of Operation (a.k.a Stream Cipher)
• Key Exchange Algorithm
• TLS Certificate and Chain of Trust
• Data Integrity Check / Data Authentication
• TLS Cipher Suite and TLS handshake
• TLS versions

Part 3:

• Preparing TLS Certificates
• Enabling Transport Layer Security (TLS) to PostgreSQL Server
• Enabling Transport Layer Security (TLS) to PostgreSQL Client
• TLS Connect Examples
• Transparent Data Encryption (TDE)
• Security Vulnerability

2. PostgreSQL Server Listen Address

PostgreSQL server is a TCP server that by default listens on localhost at port 5432. The server listen address may seem very trivial at first in terms of security but it is actually very important because understanding how the PostgreSQL is serving the incoming connections is fundamental to building a more secured network environment.

Connection settings are located in postgresql.conf

The listen_addresses parameter tells PostgreSQL which addresses to listen on. This value should match the IP address of the network interface cards in the host machine. ifconfig on Unix-based systems (or ipconfig for Windows) is a handy command that lists all the network interfaces and their IP addresses. listen_address supports the less secured * configuration, which will listen to all the network interfaces available

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

Another important connection configuration is the maximum connections allowed. By default PostgreSQL allows 100 simultaneous connections to be active at a time with 3 connections reserved for super user. That is 97 connections for regular database users. These numbers should be configured accordingly depending on the usage case of the database server and we definitely don’t want too many unintentional connections to access the database

max_connections = 100                # (change requires restart)
superuser_reserved_connections = 3   # (change requires restart)

3. Host-Based Authentication

Host-based authentication refers to the process of verifying the identity of a user connection based on the IP addresses of the connecting host. PostgreSQL supports host-based authentication by adding and removing desired entries in the pg_hba.conf file. This file works in a similar way as defining firewall rules. The official documentation on pg_hba.conf can be found here: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

A simple example below defines the following rules:

• Allows connections from subnet 192.168.3.0/24 to access the database named “software_team”
• Allows connections from subnet 192.168.4.0/24 to access the database named “marketing_team”
• Allows connections from subnet 192.168.5.0/24 to access the database named “sales_team”
• Allows connections from subnet 192.168.6.0/24 to access the database named “management”
• The admin user has permission to access all the database given that the admin is connecting from localhost (both IPv4 and IPv6) or from a UNIX domain socket.
• Allows all user connections coming from subnet 192.168.7.0/24 to access the database named “production_team” and is able to do replication connection. Please note that the word “replication” is a special term reserved to allow replication connections rather than database name.
• Allows user from a unsecured network “172.16.30.0/24” to access the “sales_team” database only if the connection uses SSL (a.k.a TLS)
• Rejects all connections from 172.16.50.5

# TYPE  DATABASE         USER            ADDRESS                 METHOD
local   all              admin                                   trust
local   all              admin           127.0.0.1/32            trust
local   all              admin           ::1/128                 trust

host    software_team    all             192.168.3.0/24          trust
host    marketing_team   all             192.168.4.0/24          trust
host    sales_team       all             192.168.5.0/24          trust
host    management       all             192.168.6.0/24          trust
host    production_team  all             192.168.7.0/24          trust
host    replication      all             192.168.7.0/24          trust

host    all              all             172.16.50.5/32          reject
hostssl sales_team       all             172.16.30.0/24          trust

The simple example above uses 2 basic methods to control the access, trust and reject. This will suffice in a small database server environment. However, depending on the infrastructure, the application’s nature and data security, stronger authentication methods are encouraged, such as LDAP, GSSPI with Kerberos, SSPI, RADIUS SCRAM-SHA-256…etc.

Generally speaking, most of these stronger authentication methods require PostgreSQL to communicate with foreign authentication servers to complete the authentication process in a more secured way and provide automatic “single-sign-on” authentications through means of shared secrets, token exchange, or user name mappings. I will briefly introduces LDAP and PAM authentication in this blog.

4. Authentication with LDAP Server

LDAP stands for Light-weight Directory Access Protocol, which is commonly deployed as centralized authentication system for medium to large organizations. This authentication server provides user credential authentication and stores related user details like distinguished name, domain names and business units..etc. Every entry in an LDAP directory server has a distinguished name (DN). It is the name that uniquely identifies an entry in the directory and made up of attribute=value pairs. As a LDAP client on the PostgreSQL side, attribute=value pairs are required to be supplied in pg_hba.conf file separated by commas. For example:

# TYPE  DATABASE         USER                 ADDRESS             METHOD
host production_team    production_user       0.0.0.0/0           ldap dapserver=192.168.7.100 ldapport=389 ldapprefix="cn=" ldapsuffix=", dc=organization, dc=com"

Please note that LDAP by default is not encrypted and communicating user credential unencrypted is never a good idea. LDAP over TLS is supported by appending ldaptls=1 to the ldap attributes in pg_hba.conf file. Please also note that ldaptls=1 only provides secured connection between PostgreSQL server and LDAP server. The connection between PostgreSQL server and client is not using TLS by default, so it needs to be enabled as well. TLS is discussed in details in part 2 of this blog.

5. Authentication with PAM

PAM stands for Pluggable Authentication Module and it operates similarly to password. The default service name is postgresql. First we need to create a linux user (Example based on Ubuntu 18.04).

$ useradd production_user
$ passwd production_user
Changing password for user production_user.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

Create /etc/pam.d/postgresql with the content:

#%PAM-1.0
auth      include      system-auth
ccount    include      system-auth
password  include      system-auth
session   include      system-auth

Create production_user in PostgreSQL server

$ CREATE USER production_user;

Then finally update the pg_hba.conf with pam authentication method.

# TYPE  DATABASE         USER                 ADDRESS                 METHOD
host production_team     production_user      0.0.0.0/0               pam

6. Authentication with Certificate

Authentication with certificate can be applied to all the authentication methods by appending clientcert=1 in method parameters. This is only useful with hostssl type records in pg_hba.conf file and requires that the PostgreSQL server has TLS enabled in postgresql.conf with path to CA certificate specified. We will discuss TLS and certificates in part 2 of the blog in more details.

With clientcert=1 in place, the server will require that the client to send its TLS certificate for verification. The connection will abort if client fails to provide a certificate. The server will verify the common name (CN) in the certificate against the server’s hostname. Both should match. In addition, certificate validity dates will be verified and most importantly, the server will try to determine the chain of trust from the client certificate against the CA certificate configured in the server to determine if the client can be trusted.

# TYPE  DATABASE         USER                 ADDRESS                 METHOD
hostssl production_team     production_user      0.0.0.0/0            pam clientcert=1

7. Role-Based Access Control

Role-based access control refers to the process of verifying database access permissions based on the pre-defined roles and user privileges. PostgreSQL supports role-based access in several levels, such as table, function, procedural language and user levels. I will explain the concept in table and user level access control that follow the general guidelines below:

• A user with super user privilege can do any activities in the database
• A user who creates a table owns the table and can set its permission
• A user needs to belong to a proper role to perform administrative operations such as create another user or role
• Other users need proper permissions to view or operate on a table created by another user.

When a PostgreSQL database cluster has been initialized, a super user will be created by default that equals to the system user that initializes the cluster. This super user is the starting point to define other role and other users and privileges to ensure proper database access.

8. Assign Table and Column Level Privileges to Users

The GRANT clause supported in PostgreSQL is used to configure the access privileges (official documentation here: https://www.postgresql.org/docs/current/sql-grant.html). GRANT is a very universal clause that can be used to add access privileges to tables, databases, roles, table spaces…etc. The opposite of GRANT is REVOKE, which removes privileges (official documentation here: https://www.postgresql.org/docs/current/sql-revoke.html). In this blog, I will use GRANT on table and role level. When a table is created, it is assigned an owner. The owner is normally the user that executed the creation statement. The initial state is that only the owner (or a superuser) can do anything with the table. To allow other users to use it, privileges must be granted.

There are many types of privileges that can be granted to a table or a table column. The image below is taken directly from the official PostgreSQL documentation that lists all the available privileges and their applicable objects.

Consider a simple SQL command example below that assigns table and column access privileges to other users

$ GRANT SELECT ON table1 TO userA;
$ GRANT SELECT ON table2 TO userA;
$ GRANT SELECT ON table3 TO userA;

$ GRANT UPDATE ON table1 TO userB;
$ GRANT INSERT ON table2 TO userB;
$ GRANT INSERT ON table3 TO userB;

$ GRANT UPDATE ON table2 TO userC;
$ GRANT SELECT(column1), UPDATE(column3) ON table3 TO userC;

The above SQL commands can be illustrated as:

9. Assign User Level Privileges as Roles

A ROLE is an entity that can own database objects and have database privileges; a role can be considered a “user”, a “group”, or both depending on how it is used.(official documentation here: https://www.postgresql.org/docs/current/sql-createrole.html). Similar to a table, a created role can be altered with the ALTER clause or deleted with the DROP clause.

Please note that when a role is created initially, the permission to LOGIN is not allowed by default and has to be manually set such that the users belonging to this role can log in to the database server. The same can be done with CREATE USER clause, which allows LOGIN by default. So the following 2 commands are essentially the same

$ CREATE ROLE username LOGIN;
$ CREATE USER username;

The following image is taken directly from the official PostgreSQL documentation that lists all the privilege keywords that can be associated to a role.

Consider the following simple example that creates 3 users and 4 different roles having different user level access privileges.

/* Create 3 users */
$ CREATE USER userA;
$ CREATE USER userB;
$ CREATE USER userC;

/* Create 4 roles */
$ CREATE ROLE role1 LOGIN CREATEDB CREATEROLE;
$ CREATE ROLE role2 WITH PASSWORD '12345678' LOGIN REPLICATION;
$ CREATE ROLE role3 LOGIN CREATEDB INHERIT;
$ CREATE ROLE role4 LOGIN CONNECTION LIMIT 5 ;

$ GRANT role1 TO userA;
$ GRANT role1 TO userB;
$ GRANT role3 TO userC;
$ GRANT role4 TO userC;

postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of      | Description 
-----------+------------------------------------------------------------+----------------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}             | 
 userA     |                                                            | {role1}        | 
 userB     |                                                            | {role1}        | 
 userC     |                                                            | {role3, role4} | 
 role1     | Replication, Create DB, Create role                        | {}             | 
 role2     | Replication                                                | {}             | 
 role3     | Create DB                                                  | {}             | 
 role4     | 5 connections                                              | {}             | 

$ GRANT SELECT ON table1 TO role1;
$ GRANT SELECT ON table2 TO role1;
$ GRANT SELECT ON table3 TO role1;

$ GRANT UPDATE ON table1 TO role1;
$ GRANT INSERT ON table2 TO role1;
$ GRANT INSERT ON table3 TO role1;

$ GRANT UPDATE ON table2 TO role3;
$ GRANT SELECT(column1), UPDATE(column3) ON table3 TO role3;

Use the \du+ meta command to see all the roles that have been created with summary of the attributes associated with each role. To see the full list of attributes per role, use the SQL command SELECT * FROM pg_roles;.

postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 
 userA     |                                                            | {}        | 
 userB     |                                                            | {}        | 
 userC     |                                                            | {}        | 
 role1     | Replication, Create DB, Create role                        | {}        | 
 role2     | Replication                                                | {}        | 
 role3     | Create DB                                                  | {}        | 
 role4     | 5 connections                                              | {}        | 

10. Assign Table Level Privileges via Roles

Section 3.1 illustrates privilege assignments directly to each individual users, which is desirable in smaller database servers. Imagine a larger database server where there could potentially be hundreds of users exist in the entire database cluster. Managing the table level privileges would get quite complicated and tedious. Luckily, PostgreSQL supports assigning users to roles for better privilege management

Following the examples in section 3.2, we can use the GRANT command again to assign users to roles. Note that the Member of will display the relationship between users and roles after we have related them with GRANT clause.

$ GRANT role1 TO userA;
$ GRANT role1 TO userB;
$ GRANT role3 TO userC;
$ GRANT role4 TO userC;

postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of      | Description 
-----------+------------------------------------------------------------+----------------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}             | 
 userA     |                                                            | {role1}        | 
 userB     |                                                            | {role1}        | 
 userC     |                                                            | {role3, role4} | 
 role1     | Replication, Create DB, Create role                        | {}             | 
 role2     | Replication                                                | {}             | 
 role3     | Create DB                                                  | {}             | 
 role4     | 5 connections                                              | {}             | 

Following the examples in section 3.1, we can use the GRANT command again to assign table level privileges to roles that we have created instead of to users directly

$ GRANT SELECT ON table1 TO role1;
$ GRANT SELECT ON table2 TO role1;
$ GRANT SELECT ON table3 TO role1;

$ GRANT UPDATE ON table1 TO role1;
$ GRANT INSERT ON table2 TO role1;
$ GRANT INSERT ON table3 TO role1;

$ GRANT UPDATE ON table2 TO role3;
$ GRANT SELECT(column1), UPDATE(column3) ON table3 TO role3;

The above SQL commands can be illustrated as:

11. Role Inheritance

INHERIT and NOINHERIT are one of the special attributes that can be assigned to a role. When a role (say role 1) contains INHERIT attribute and is a member of another role (say role 2). All the attributes existing in both role 1 and role 2 will be applied to the user.

Consider a simple example below:

$ CREATE ROLE role1 LOGIN CREATEDB REPLICATION;
$ CREATE ROLE role2 LOGIN CREATEROLE INHERIT;
$ GRANT role1 TO role2;
$ GRANT role2 TO userA;

Which can be visualized as:

In this case, role2 is created with INHERIT, userA will be assigned the privileges defined in both role1 and role2.

12. Summary

In this blog, we went over several mechanisms in postgreSQL that allows a database administrator to configure the authentication of incoming user connections and the privilege configuration in table, column and user level via the concept of roles. PostgreSQL provides pg_hba.conf file that configures simple authentication and supports stronger authentication methods against remote authentication services such as GSSAPI, kerberos, RADIUS, PAM and LDAP..etc. So far we have only talked about authentication and authorization (AA) in PostgreSQL terms, in part 2 of this blog, I will explain the general concept of data encryption, how to secure data communication between server and client with TLS and how to achieve encryption on storage devices.