PostgreSQL is widely recognized as a reliable, powerful, and highly customizable database system. One of the main reasons for its popularity is its strong security capabilities. As applications grow and handle sensitive data, securing the database becomes a top priority. PostgreSQL provides several advanced security features that help protect your data from unauthorized access and ensure compliance with security best practices.
In this blog, we will take a deep dive into how to use some of the most important PostgreSQL security features — including roles, permissions, SSL encryption, and row-level security (RLS) policies. Each of these components plays a critical role in keeping your database secure and controlling who can see or modify data.
1. How to Use Roles in PostgreSQL
What Are Roles?
Roles in PostgreSQL are fundamental building blocks of database security. A role can be thought of as a user account or a group of users that share the same privileges. In PostgreSQL, roles replace the older concept of separate users and groups — every user is essentially a role with login ability.
By defining roles, database administrators can manage access in a scalable and organized way. For example, instead of granting direct access to each individual user, you create roles like readers, writers, or admins and then assign users to those roles.
Purpose of Roles
The main purpose of roles is to simplify user management and control database access. They allow you to:
* Centralize access control policies.
* Easily add or remove users without changing permission settings repeatedly.
* Create hierarchical role structures (roles can be members of other roles).
How to Create and Use Roles
You create a role in PostgreSQL using the CREATE ROLE command. Here’s an example of creating a role named analyst that can log in and has a password:
CREATE ROLE analyst LOGIN PASSWORD 'securepassword';
This command creates a user called analyst who can connect to the database using the specified password.
You can also create roles without login privileges, which act as groups:
CREATE ROLE reporting_group;
Now, you can add the analyst user to this group:
GRANT reporting_group TO analyst;
This means the analyst inherits any permissions assigned to the reporting group.
Roles are the foundation for managing who can connect to your PostgreSQL database and what they can do. By carefully planning roles and their memberships, you maintain a clean, secure access control system that scales with your team.
2. How to Manage Permissions in PostgreSQL
What Are Permissions?
Permissions (also called privileges) define what actions a role can perform on database objects such as tables, views, functions, and schemas. Permissions help you enforce the principle of least privilege — users get only the access they need, no more.
Purpose of Permissions
The purpose of permissions is to control data access and modification rights at a granular level. You can specify whether a user can:
* Read data (SELECT)
* Insert new data (INSERT)
* Update existing data (UPDATE)
* Delete data (DELETE)
* Create new objects (CREATE)
* Execute functions (EXECUTE)
This control prevents unauthorized or accidental changes and protects sensitive data.
How to Grant Permissions
Suppose you want the analyst role to be able to view data in the sales table but not modify it. You would grant SELECT permission like this:
GRANT SELECT ON sales TO analyst;
This ensures the analyst can only read from the sales table.
If you later want to allow an analyst to add new sales records, you grant INSERT:
GRANT INSERT ON sales TO analyst;
How to Revoke Permissions
Permissions can be taken back if needed, for example:
REVOKE INSERT ON sales FROM analyst;
This removes the ability to insert data.
Permission Inheritance Through Roles
Remember, if an analyst is a member of a group role like reporting_group, any permission granted to reporting_group also applies to the analyst. This inheritance makes it easier to manage large teams.
Permissions provide fine-grained control over who can do what inside the database. By using permissions thoughtfully, you can protect your data and ensure users have the right level of access.
3. How to Secure PostgreSQL Connections with SSL
What is SSL?
SSL (Secure Sockets Layer) is a protocol that encrypts data transmitted between the PostgreSQL server and clients. This encryption prevents attackers from intercepting or modifying sensitive data while it travels over the network.
Purpose of SSL in PostgreSQL
Without SSL, data, including usernames, passwords, queries, and results, is sent in plain text over the network. Anyone with access to the network can potentially capture and read this data, which is a major security risk.
Enabling SSL ensures that:
* All communication between client and server is encrypted.
* Data integrity is maintained.
* The server’s identity is verified via SSL certificates.
How to Enable SSL in PostgreSQL
PostgreSQL supports SSL natively, but it requires configuration:
1. Generate SSL certificates (a private key and a certificate).
2. Place these files on the PostgreSQL server.
3. Update the PostgreSQL configuration file (postgresql.conf) to enable SSL:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
4. Restart PostgreSQL to apply changes.
How Clients Use SSL
When connecting, clients must specify they want to use SSL, for example:
psql "host=yourserver dbname=mydb user=analyst sslmode=require"
The sslmode=require parameter tells PostgreSQL to enforce encrypted connections.
Enabling SSL protects data in transit between your database and application, which is crucial, especially when connections happen over the internet or untrusted networks.
4. How to Use Row-Level Security (RLS) Policies in PostgreSQL
What is Row-Level Security?
Row-Level Security (RLS) allows you to restrict access to specific rows in a table based on the user or role accessing it. This is more precise than traditional table-level permissions and is especially useful for multi-user applications.
Purpose of Row-Level Security
RLS helps you:
* Enforce data privacy on a per-user basis.
* Avoid creating multiple tables for each user.
* Simplify complex access control scenarios inside the database itself.
For example, in a multi-tenant application where each customer should only see their own data, RLS ensures each user sees only their rows in shared tables.
Sure! Below is the revised version of your section with a complete example, including table creation, user roles, enabling RLS, and creating a policy — all explained clearly and included within the context of your blog section.
How to Enable Row-Level Security
To start using Row-Level Security (RLS) on a PostgreSQL table, you first need to enable it using the ALTER TABLE statement. This tells PostgreSQL that you want to control which rows can be accessed based on user-defined policies.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
But before enabling RLS, let’s walk through a complete example to understand how it works in practice.
Full Example: Applying RLS to the Orders Table
Step 1: Create the users table
This will store app users and their unique usernames.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT UNIQUE NOT NULL
);
Step 2: Create the orders table
This table references the users table via user_id.
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
product TEXT NOT NULL,
amount NUMERIC NOT NULL
);
Step 3: Insert sample data
INSERT INTO users (username) VALUES ('alice'), ('bob');
INSERT INTO orders (user_id, product, amount) VALUES
(1, 'Keyboard', 50),
(1, 'Mouse', 25),
(2, 'Monitor', 200),
(2, 'Laptop Stand', 35);Step 4: Enable Row-Level Security
Now enable RLS on the orders table:
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
How to Create Policies
Policies define the rules for which rows are visible or modifiable based on the current database user.
The following policy ensures that users can only see their own orders — i.e., where user_id matches the ID of the currently logged-in role.
CREATE POLICY user_can_view_own_orders ON orders
FOR SELECT
USING (
user_id = (SELECT id FROM users WHERE username = current_user)
);
This works if:
* users.username matches the PostgreSQL login role name (e.g., alice, bob).
* You have created those roles in PostgreSQL:
CREATE ROLE alice LOGIN PASSWORD 'alice_password';
CREATE ROLE bob LOGIN PASSWORD 'bob_password';
Verifying Access
When Alice logs in and runs:
SELECT * FROM orders;
She’ll only see her own orders — the ones where user_id matches her id in the users table.
Likewise, Bob will see only his records when logged in as bob.
This example demonstrates how PostgreSQL enforces access rules row by row, using roles and policies. With RLS, you no longer need to worry about filtering data in your application code — the database itself ensures users see only what they’re allowed to.
What Happens Without RLS?
If RLS is disabled, all users with table access see all rows. Turning on RLS and creating policies restricts this visibility.
Conclusion
Protecting your database requires a careful balance between accessibility and security. PostgreSQL’s advanced features—roles, permissions, SSL, and row-level security—offer powerful tools to control who can access your data, how they use it, and ensure data is safely transmitted.
Roles and permissions manage user access and actions, SSL encrypts data in transit, and row-level security enforces fine-grained control over individual records. Together, these features help you build a secure, reliable database environment that protects sensitive information and meets modern security standards.
Investing time to understand and implement these tools is essential for any organization serious about data protection. With PostgreSQL’s security capabilities, you can confidently safeguard your data today and prepare for future challenges. Learn how to use the pgml extension in PostgreSQL with Docker to easily integrate machine learning capabilities into your database environment. This guide walks you through setting up Docker containers, installing the extension, and running your first ML models inside PostgreSQL.