PostgreSQL is considered one of the world’s most powerful, and advanced object-relational database management systems. It’s highly scalable, extensible, and an excellent choice for handling large datasets, especially in production environments.
However, documented security vulnerabilities according to the CVE database make it a soft target for hackers. In this guide we explore different measures that you can implement to fortify the security of the PostgreSQL server and establish a safeguard against potential breaches.
Lock Down Port Level Access to PostgreSQL
Network access to the database should be limited to just a few ports; the database port itself ( port 5432 ) and a management port, preferably the SSH port. All other ports with network access should be locked down to reduce the attack surface.
Thus, deploying a firewall is highly recommended on the host machine running the PostgreSQL server. A firewall allows you to define what inbound or outbound network traffic is allowed. There are various ways of doing this including opening the default PostgreSQL port 5432 and defining the protocol (whether IPv4 or IPv6). In addition, you can set a source address, or a subnet of hosts that are allowed to access the database server.
Typically, modern Linux distributions come with a preinstalled firewall application to filter traffic. For Debian / Ubuntu derivatives, we have the UFW, while Firewalld is the default firewall in Red Hat distributions such as RHEL, Fedora, Rocky Linux, and AlmaLinux.
On Debian/ Ubuntu systems running UFW firewall, allow inbound traffic on ports 5432 and 22 as shown:
sudo ufw allow 5432/tcp sudo ufw allow 22/tcp
Then reload the firewall to apply the changes made.
sudo ufw reload
On systems running Firewalld such as Red Hat, Fedora, and Rocky, open ports 5432 and 22 as follows:
sudo firewalld --add-port=5432/tcp --zone=public --permanent sudo firewalld --add-port=22/tcp --zone=public --permanent
Then reload the firewall for the changes to come into effect.
sudo firewalld --reload
If you are running the Iptables firewall utility, run the following commands to allow SSH and PostgreSQL connections respectively.
iptables -A INPUT -p tcp -m state --state NEW --dport 22 -j ACCEPT iptables -A INPUT -p tcp -m state --state NEW --dport 5432 -j ACCEPT
Next, allow all outbound connections and drop any other inbound traffic.
iptables -A OUTPUT -j ACCEPT iptables -A INPUT -j DROP iptables -A FORWARD -j DROP
Disable Remote Access to PostgreSQL
This measure alone wards off a substantial host of other attack vectors which include brute force and man-in-the-middle attacks.
To restrict access to localhost only, modify the
postgresql.conf file and set
To access the configuration file, run the following command. Be sure to replace
14 with the version number of the PostgreSQL database server installed.
sudo vim /etc/postgresql/14/main/postgresql.conf
Next, locate the
listen_addresses directive and set it to
listen_addresses = 'localhost'
Then restart the PostgreSQL service to effect the change made.
sudo systemctl restart postgresql
Configure Allowed Hosts
Alternatively, you can allow specific hosts to connect to the database server instead of entirely disabling remote connections. This ensures that only authorized users can make a connection to the database server.
To achieve this, access the
sudo vim /etc/postgresql/14/main/pg_hba.conf
Under the comment section that describes how to allow non-local connections, insert an entry that includes the public IP address of the database server along with the database and the user allowed to access the database.
host sample_db cherry client_ip_address/24 md5
Let’s have a look at the values in detail.
host attribute indicates that a TCP/IP connection will be used to connect to the database.
sample_db entry specifies the database that the user will connect to. Multiple databases can be specified by separating the database names using a comma.
cherry entry indicates the user that is allowed to make a connection. And just like the database name, multiple users can be specified by separating their names using commas.
client_ip_address entry is the public IP address of the client machine that is connecting to the server.
Once you’re done, save and exit the file. Next, edit the
sudo vim /etc/postgresql/14/main/postgresql.conf
listen_addresses line and specify the public IP address of your database server denoted by the
listen_addresses = 'localhost, server-ip'
Once done, save the changes and close the file. Then restart PostgreSQL for the changes to come into effect.
sudo systemctl restart postgresql
Set up SSL with PostgreSQL
PostgreSQL provides native support for SSL connections. SSL encrypts client/server communications and provides enhanced security. By default, SSL is not configured, and you have to manually do it yourself.
Check out our guide on how to Configure SSL on PostgreSQL.
Proper Management Of Users and Group Roles
So far, we have looked at how you can secure PostgreSQL at a network level. At a database level you can also harden your server by proper assignment of privileges to users and roles.
In information security the golden rule is to always apply the least privilege principle. This implies granting users just the right number of permissions to accomplish a task.
The same applies to user and role management. Users should be granted the minimum privileges that don’t allow too much access.
Users, roles, and groups are synonymous in PostgreSQL, with the only difference being that users have login permissions by default. To be more specific, a user is a role with CONNECT privilege.
CREATE USER and
CREATE ROLE statements can be used to create a user. The user, however, must have a LOGIN role. When created using the
CREATE USER statement, the user is automatically granted the LOGIN role.
For example, in the following SQL command, the LOGIN role is assigned to the
CREATE USER cherry_user WITH ENCRYPTED PASSWORD 'secret';
After creating the user or role, invoke the following SQL query to verify if the user can log in.
SELECT rolcanlogin from pg_roles where rolname = 'cherry_user';
The output returns a boolean value that indicates whether the user can log in or not. The value
t indicates that the user can log in and
f shows the converse.
However, when you create a user using the
CREATE ROLE statement, you must explicitly add the
LOGIN attribute to allow the user to log in.
CREATE ROLE cherry_user LOGIN;
A role defines a user, a group, or a set of permissions assigned to a group or user for various objects in the database. It's an entity that can have database privileges and own database objects.
CREATE USER and
CREATE GROUP statements have been replaced by the more general,
CREATE ROLE with specific properties to distinguish database users.
A database user is a role with the LOGIN privilege by default. The
postgres user is the most privileged database user in the PostgreSQL server. It possesses a special privilege called
SUPERUSER that grants it unrestricted access to the database, just like root account on Linux.
Role attributes are directives on the role itself that specify some of the core privileges it has on a database level. Attributes can be specified when creating a role, or modified later on.
Examples of role attributes that can be applied to a role include:
LOGIN: This allows database users to connect to the database cluster. As we have seen earlier, the
CREATE USER statement automatically adds this attribute, while
CREATE ROLE command does not.
CREATEDB: The attribute allows the role to create new databases.
CREATEROLE: This allows the role to create, alter and delete other roles. In addition, it allows the role to assign or alter role membership. The only exception is that a role with the
CREATEROLE attribute cannot alter
SUPERUSER roles without the
SUPERUSER: Allows the role to circumvent all permission checks with an exception of a right to log in. Note that only
SUPERUSER roles can create roles with this attribute.
REPLICATION: This allows the role to initiate streaming replication. Roles with this attribute must also bear the
INHERIT: Determines whether the role can inherit the privileges of roles it is a member of.
For more check out PostgreSQL's documentation on role attributes.
To view the roles and role attributes assigned to users run the following query.
From the output, you can see that the postgres has a list of database roles including the
superuser role that provides it with unrestricted access to the database.
Granting Role Permissions
Rather than assigning access privileges to users/roles individually, the most recommended approach is to create a group role and grant other roles (map individual users) membership of this group.
To demonstrate this, we have a table called
database_inventory that contains records of database servers managed by different system administrators.
Below is the table schema.
CREATE TABLE database_inventory ( os text, description text, ip_address text, staff text );
We have already added some records, and this is how the table looks.
Suppose you want to allow your sysadmins, say, Mike, Bob, and Alice to view the table but not modify it.
Instead of individually assigning permissions to view the table to the users here’s how you would go about it.
First, create a group role that does not have login abilities by itself and then grant it SELECT privileges on the schema.
CREATE ROLE sysadmins; GRANT SELECT ON database_inventory TO sysadmins;
Next, create three user accounts that will inherit
sysadmins permissions once logged into the database.
CREATE USER bob WITH ENCRYPTED PASSWORD 'P@ss321'; CREATE USER mike WITH ENCRYPTED PASSWORD 'P@ss321'; CREATE USER alice WITH ENCRYPTED PASSWORD 'P@ss321';
Lastly, assign the
sysadmins role to the user accounts.
GRANT sysadmins TO bob, mike, alice;
Once connected to the database, Bob, Mike, and Alice will inherit privileges of the "sysadmins" group role and be able to view records in
database_inventory table but not modify it.
To verify this, we will switch to a user called ‘
bob’ and view the table using the
SELECT * FROM database_inventory;
The query prints out the entire table. However, any attempt to update the records in the table results in an error since the user only has read-only rights on the table.
In addition to granting roles or permissions to users on tables, you can implement tighter policies on a row level. Row-Level Security (RLS) is a security mechanism that controls access to rows in a database table based on the current user’s authorization.
Records from tables are displayed based on who the user is, and which records the user has access to. The objective of Row-Level Security is to allow specific users access to their data only without giving the privilege to view or modify other users' records.
To demonstrate the Row-Level Security concept, we will build on our previous table example. First, you need to enable Row-Level Security on the table.
ALTER TABLE database_inventory ENABLE ROW LEVEL SECURITY;
Next, we will create a policy called
modify_servers that will restrict users to only view their records based on the
staff field in the table. The
SELECT keyword grants users permission to query their own records. The
USING expression compares the currently logged-in username with the
staff column in the table.
CREATE POLICY modify_servers on database_inventory FOR SELECT TO sysadmins USING (staff = current_user);
To put our policies to the test, we will switch over to one of the database users, in this case
\c postgres bob
When the user queries the entire table, he views his record only and not of other users. The same happens to other users as well. They can only access records that belong to them based on the policies applied.
As a rule of thumb, applying critical security updates and patches is recommended for software applications. This addresses vulnerabilities and loopholes that can be exploited to gain access to the databases. In addition, staying on top of security updates is a prerequisite in PCI-DSS compliance if your PostgreSQL-based web application stores credit card data.
In this guide, we have looked at different levels of addressing database security. We have looked at network-level security which includes the implementation of firewalls and TCP/IP sockets, transport-level security that enables secure communication using SSL/TLS and, lastly, database-level security which involves assigning permissions to users using roles and permissions alongside row-level security.
Check out PostgreSQL’s security information page for additional security considerations.