What is PostgreSQL Security?
PostgreSQL, also commonly known as Postgres, is an open-source relational database system with advanced, enterprise-grade capabilities. It supports relational querying in the form of SQL, as well as non-relational querying in JSON format. You can use PostgreSQL as a primary database for your web and mobile applications, as well for big data analytics systems.
Postgres security is based on three pillars:
- Network-level security including the use of Unix Domain sockets, TCP/IP sockets, and firewalls.
- Transport-level security which enables secure communication with the database using SSL/TLS.
- Database-level security features such as roles and permissions, row level security (RLS), and auditing.
In this article, you will learn:
- PostgreSQL Network-Level Security
- Unix Domain Socket (UDS)
- TCP/IP Socket
- Firewall
- PostgreSQL Transport-Level Security (TLS)
- PostgreSQL Database-Level Security
- Roles and Permissions
- Row Level Security (RLS)
- Auditing
- PostgreSQL Security Best Practices
- Postgres Security with Satori
The information provided in this article and elsewhere on this website is meant purely for educational discussion and contains only general information about legal, commercial and other matters. It is not legal advice and should not be treated as such. Information on this website may not constitute the most up-to-date legal or other information.
The information in this article is provided “as is” without any representations or warranties, express or implied. We make no representations or warranties in relation to the information in this article and all liability with respect to actions taken or not taken based on the contents of this article are hereby expressly disclaimed.
You must not rely on the information in this article as an alternative to legal advice from your attorney or other professional legal services provider. If you have any specific questions about any legal matter you should consult your attorney or other professional legal services provider.
This article may contain links to other third-party websites. Such links are only for the convenience of the reader, user or browser; we do not recommend or endorse the contents of any third-party sites.
PostgreSQL Network-Level Security
The main components of PostgreSQL network-level security are the Unix domain socket, TCP/IP socket, and firewall deployed on the host machine.
Unix Domain Socket (UDS)
UDS is the default feature that lets you connect your PostgreSQL database with other processes running on a Unix-based environment. A UDS looks like a special file in the filesystem, and can only be accessed directly from the machine it is installed on. The owner of the socket is the operating system user the PostgreSQL server uses to run.
By default, a UDS is assigned the same access controls as other files on the filesystem, although it only requires write permissions. You can control and manage a UDS using the unix_socket_group and unix_socket_permissions configuration options. You can also change access controls on the directory hosting the socket to change its permissions.
PostgreSQL lets you create multiple sockets, via the unix_socket_directories option. You can specify several directories, each with its own permissions, to define separate permissions for different applications or groups of users.
TCP/IP Socket
A TCP/IP network socket lets you provide remote systems with access to the PostgreSQL server. This feature is typically needed for applications with multiple services or tiers, as well as to enable remote administration with tools like pgAdmin.
Firewall
It is highly recommended to deploy a firewall on the host machine of a PostgreSQL database. The majority of operating systems come with built-in firewalls, like the Windows Defender Firewall installed on Windows or iptables on Linux. You can also install third-party firewalls to extend protection and capabilities.
A firewall lets you define rules that define what inbound and outbound traffic is allowed. There are several parameters you can use to define these rules, including the local port (the default is 5432 in PostgreSQL), the protocol (IPv6 or TCP), as well as the source address (typically a list of subnets or addresses). To ensure your database remains protected, strictly control external access to the server.
PostgreSQL Transport-Level Security (TLS)
TLS is a secure protocol that enables secure communications over the public Internet. It is natively supported in Postgres, providing encryption for database connection and for data transport. You can use TLS to enable secure authentication for clients accessing your database.
To enable TLS for PostgreSQL connections, you’ll need a server key and certificate. Protect the key and certificate with a passphrase, which can either be entered manually when the server starts, or automatically, by writing a script that uses the ssl_passphrase_command configuration parameter.
Make sure not to use ciphers that are no longer considered secure. Review and ensure you properly configure the following parameters in the postgresql.conf configuration file, which are related to SSL encryption: ssl_ecdh_curve, ssl_ciphers, ssl_min_protocol_version, ssl_dh_params_file, and ssl_ecdh_curve (learn more about these parameters in the documentation).
PostgreSQL Database-Level Security
There are several data-level security measures you can implement to secure your PostgreSQL database, including roles and permissions, row-level security, and auditing.
Roles and Permissions
PostgreSQL comes with a built-in user permissions system designed around the concept of roles. Latest PostgreSQL versions (starting 8.1) define a “role” as a “user”. This means that a database account name is treated as a role, and it comes with a LOGIN attribute that enables the role to connect to the database.
Roles can be assigned other attributes that grant them certain types of privileges. For example, a SUPERUSER can bypass permission checks. A CREATEDB can create databases, and CREATEROLE lets you create other roles.
Row Level Security (RLS)
RLS enables you to specify policies that restrict the visibility of certain rows in a table to specific roles only. To use row-level security, you need to first enable this feature for the table and then define a policy that controls which rows in a table can be accessed by which users.
RLS policies can be complex to set up, and you must consider RLS policies in your index design. But it is important to use them to limit access to data that is protected by compliance standards, such as protected health information (PHI), or to ensure access to personal data by employees is in line with ethical and professional standards.
Learn more in our detailed guide to Postgres row level security
Auditing
You should keep an audit trail to ensure that you have accurate and detailed records. You can monitor several aspects of Postgres, including the server itself, by enabling the verbose logging feature.
For more auditing and monitoring capabilities, you can integrate with third party extensions like pgAudit. If you are self-hosting your PostgreSQL workloads, you need to manually install pgAudit. If you are using managed services like AWS RDS, pgAudit comes as part of the service and you can use it by enabling the feature.
Related content: read our guide to Postgres audit
PostgreSQL Security Best Practices
Here are several best practices that can help you improve PostgreSQL security.
Use one-way encryption for values that do not need to be decrypted
Certain encryption techniques, like AES, can be decrypted. Hash-based encryption techniques, on the other hand, such as MD5, perform one-way encryption. Ensure you use the most appropriate encryption for each type of data—for example, use two-way encryption for data that is needed for analysis, and one-way encryption for user passwords.
Use physical separation to isolate sensitive datasets
If you have datasets that require physical separation, use pg_hba and RBAC to ensure that data in two tables cannot be accessed or viewed simultaneously during a single login session. Note that this breaks SQL joins, and should only be used in appropriate scenarios.
Prevent external connections to the database
You can configure listen_addresses to localhost, or the specific host machine running the application that uses the database. This forces the operating system to reject connection attempts originating from any other machines except the PostgreSQL host or another known host. This can help prevent unauthorized access to your database and any underlying data structures.
Another approach is to disable remote access to your database using pg_hba.conf. If you need remote access, you can use SSH to access the database host machine, then use a local connection. You can also use SSH to configure tunnel access to your PostgreSQL database.
Do not let database logging reveal more than intended information
Ensure you use standard practices to administer your database, to prevent revealing sensitive information in database logs. For example, avoid using the PSQL create user command to create database users, because this will result in passwords displayed in cleartext in your logs. Instead, use the operating system createuser command, which displays passwords in an encrypted format.
Stay on top of critical security updates and patches
Regularly checking for updates and patches is considered a best practice, and it is also a requirement of PCI DSS and other compliance standards. Be sure to regularly and frequently check the official PostgreSQL security page for any critical updates and patches.
Satori and PostgreSQL Security
Satori makes Postgres security a breeze. You’re managing security policies for Postgres and all other data stores in a single interface, including security and access control policies such as dynamic masking and row-level security. Your data users can get access to datasets using a data portal or slack integration, and sensitive data is automatically discovered and classified. Learn more here, or schedule a meeting with one of our experts.
