3 Ways to Secure your Postgres Database

PostgreSQL, or simply Postgres, is an open-source relational database management system that has evolved to host a range of capabilities over the last almost 30 years of development.

Postgres is the database of choice for Big Tech including AWS, Google and Microsoft for their service offerings. Use of Postgres by these companies also ensures continual development and innovation making it robust and scalable among its other characteristics.

Over the years, Postgres has gained the support of a variety of internal tools, for reasons like security, high availability, search and inclusion of advanced data types among others.

Being one of the most powerful databases out there, and managing heaps of secure data, it’s essential for a lot of internal tools to be directed towards robust security. These tools range from authentication to monitoring and auditing. Let’s understand how these work and how you can use them for a better security posture.

Role Based Access Control (RBAC)

RBAC in Postgres is a way of managing access to the data by a certain user. Roles, created by an administrator, define who can perform what action (CRUD) on what table of the database. Roles are assigned to users and can be easily granted or retracted.

Here is a simple tutorial of how to create two separate user roles, one that assigns a user the access to read data, whereas another which assigns the user to write data in addition to reading the data.

1. CREATE ROLE command. Here we create two roles with assigned names read_only and read_write.

-- Create a role for read-only access
CREATE ROLE read_only;

-- Create a role for read-write access
CREATE ROLE read_write;

1. Having initialized them, we need to assign privileges to these roles. These privileges allow or restrict what the users with the role can do. Here is a break down of the commands—
   1. The GRANT SELECT ON ALL TABLES instructs Postgres to grant the privilege of selecting (and in turn returning) data in all the available tables in the public schema (SCHEMA public). It then assigns these privileges to the role read_only.
   2. Similarly, the GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES gives the right to select (read), insert (add a new entry), update (modify an existing entry) or delete an entry on all the tables in the public schema. It then assigns these privileges to the role read_write.

-- Grant SELECT (read) privileges to the read_only role
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Grant SELECT, INSERT, UPDATE, DELETE (read-write) privileges to the read_write role
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

1. Once you have certain roles with privileges, the next step is to now assign the role to a user. Let’s assume that two new users joined the organization with the Postgres database and they need to be assigned each of the newly created roles.
   1. First we create a new user (user_read_only) for the read_only role and use the GRANT command to assign them to this role.
   2. Then, we create another user (user_read_write) for the read_write role and assign them to this role.

-- Create a new user and assign them to the read_only role
CREATE USER user_read_only WITH PASSWORD 'password';
GRANT read_only TO user_read_only;

-- Create a new user and assign them to the read_write role
CREATE USER user_read_write WITH PASSWORD 'password';
GRANT read_write TO user_read_write;

1. This will grant the users respective privileges and Postgres will throw an error in case a user attempts a SQL operation outside of their assigned roles.
2. Once the user has been assigned a role, for each session that they want to use the role, it is necessary to use the SET ROLE command to access the privileges of that role. This is important because a user can have multiple roles and also ensures that unintended actions remain to a minimum.

-- Set the role to read_only to use its privileges
SET ROLE read_only;

Transport Layer Security (TLS)

TLS ensures that all communication from the client to the database is properly encrypted. Postgres allows the admin to enable SSL for security. To enable SSL with a Postgres database, configuration is required, both on the client as well as the server side.

1. For the server side configuration, we need to enable SSL and specify the paths to your SSL certificate files in the postgresql.conf file. Adding the following lines enable SSL and point PostgreSQL to the necessary certificate and key files.

ssl = on
ssl_cert_file = '/path/to/server.crt'
ssl_key_file = '/path/to/server.key'
ssl_ca_file = '/path/to/root.crt'

1. Next, we need to set Host-Based Authentication by configuring the pg_hba.conf file. Here we define necessary access rules for users and ip addresses. You can add your IP to enable ssl access, e.g. the following configuration forces SSL for all users connecting from the specified IP range using password authentication.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl all             all             192.168.1.0/24          md5

1. Once you have set up SSL on the server side, you can securely connect to the server from the client’s end as shown below.

psql "host=somehost dbname=mydb user=myuser sslmode=require"

1. From the SQL shell, you can test your SSL connection by running the following command. It should return true if the SSL is functional.

SELECT ssl_is_used();

Row-Level Security (RLS)

Postgres allows the admin to define Row Level Security (RLS) on tables. RLS allows the admin to control access to rows in a database table based on the user executing the query.

1. To enable RLS, you need to start with enabling it on the database table.

ALTER TABLE table_with_pii ENABLE ROW LEVEL SECURITY;

1. Once RLS is enabled on a table, the admin can then create access policies to define access to the data by users. For example, a policy that restricts any but read access to managers would look like this.

CREATE POLICY view_department_policy ON departments
FOR SELECT
USING (manager = current_user);

Read through the official Postgres docs here.

Other tools

Besides securing access and communication there are some other important considerations that help improve the security posture for your database.

1. Column and Data Encryption - Postgres’s pgcrypto module defines rules to use public-private encryption to protect sensitive data in your database tables.
2. Connection Limitations - Postgres has a limit of 115 concurrent database connections (15 reserved for superusers at all times). The admin can define and reduce this limit to limit access for better security.
3. Logging - Postgres allows logging all activity and lets the admin configure properties like exporting and rotating logs for effective management.

Postgres is an advanced RDBMS that has evolved over 30 years of development. It has a array of internal (and third party) modules that can be used to improve security over your sensitive data.

These tools when used together, improve your organization’s data security posture. Well defined access policies and roles can help manage insider threats and reduce the attack surface by a lot. Using SSL for server-client communication ensures that the communication is properly encrypted.

Security teams need to configure these tools after initializing the server and constantly need to update policies as the needs evolve for better security at all times.

Scaling and Managing Multiple Databases

As organizations grow, they increasingly manage more databases (multiple database servers in many cases) and it becomes increasingly complex to manage security posture for each of the servers separately. Hence, growing organizations and security teams rely on third-party services to securely manage database servers and protect data comprehensively.

Managing multiple databases and their security posture might include defining and managing roles and policies, and ensuring compliance at all times. To define better policies, it is also important to first identify the sensitive data and where it is stored. Security teams also monitor user activity to identify unusual activities.

Adaptive is an all comprehensive data security platform by design, that takes care of all the organization’s data security requirements. Adaptive identifies where the sensitive data lies, not just in Postgres databases but all resources across your infrastructure. It then defines policies and safeguards around the sensitive data to protect it at all costs. With our Privileged Access Management (PAM), roles can be configured across all of the organization’s resources instead of having to manually define them at the database level separately.

Adaptive integrates with all your cloud resources and extends its PAM to these for a better security posture.