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.
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.
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.
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
Sujith Kumar has over 25 years of professional experience in the IT industry. Sujith has been extensively involved in designing and delivering innovative solutions for the Fortune 500 companies in the United States and across the globe for disaster recovery and high availability preparedness initiatives. Recently after leaving Quest Software/Dell after 19 years of service he was working at Cirro, Inc. focusing on database management and security. His main focus and area of interest is anything data related.
Sujith has a Master of Science in engineering degree from Texas A&M University and a Bachelor of Science in engineering degree from Bangalore University and has published several articles in referred journals and delivered presentations at several events.
Proper data security begins with a strong foundation. Find out what you're standing on with a free deep-dive into the security of your Structured and Unstructured Data, Active Directory, and Windows infrastructure.Read more
Start a Free Stealthbits Trial!
No risk. No obligation.