PostgreSQL Security Guide: Authentication, Authorization, RLS, Encryption and Auditing

PostgreSQL security works in five layers: authentication (who can connect), authorization (who can see and change what), row- and column-level access control, encryption (on the network and on disk) and audit logging (who did what). PostgreSQL offers strong built-in features for most of these layers, but the defaults aren’t enough for every environment, and some needs, such as detailed audit logging, require an extension.

This guide is for teams running PostgreSQL in production and for organizations preparing for GDPR, KVKK (Türkiye’s data protection law), banking regulator or ISO 27001 audits. We tested the examples on PostgreSQL 18.

PostgreSQL Security Layers at a Glance

1. Authentication

The pg_hba.conf file decides who can connect to PostgreSQL, from where and how. Rules are read top to bottom, and the first matching rule applies.

We walk through authentication and remote access settings at install time in our PostgreSQL installation guide.

2. Roles and Least Privilege

In PostgreSQL, users and groups are the same concept: roles. A good permission model defines job roles and makes people members of them, instead of granting privileges to individuals directly.

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_rw NOLOGIN;
CREATE ROLE app_ro NOLOGIN;
CREATE SCHEMA sales AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA sales TO app_rw, app_ro;
-- Privileges for tables app_owner creates from now on
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales
  GRANT SELECT ON TABLES TO app_ro;
-- Login roles are members of the job roles
CREATE ROLE web_app LOGIN PASSWORD 'change_me' IN ROLE app_rw;
CREATE ROLE report_user LOGIN PASSWORD 'change_me' IN ROLE app_ro;

From PostgreSQL 15 on, the PUBLIC role can’t create objects in the public schema of new databases. Databases upgraded from older versions may still have that privilege; check and revoke it.

Review privileged roles regularly:

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolreplication, rolbypassrls
FROM pg_roles
WHERE rolsuper OR rolcreaterole OR rolreplication OR rolbypassrls
ORDER BY rolname;

3. Row-Level Security

Row-Level Security (RLS) filters the rows of a table by user. For example, each branch should see only its own customers. Because the filter is enforced in the database rather than in application code, a bug in the application doesn’t expose the data.

CREATE TABLE customers (id int PRIMARY KEY, branch text NOT NULL, name text NOT NULL);
INSERT INTO customers VALUES (1, 'istanbul', 'Acme'), (2, 'ankara', 'Beta'), (3, 'istanbul', 'Gama');

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
CREATE POLICY branch_isolation ON customers
  USING (branch = current_setting('app.branch', true));

CREATE ROLE branch_user LOGIN PASSWORD 'change_me';
GRANT SELECT ON customers TO branch_user;

-- As branch_user:
SET app.branch = 'istanbul';
SELECT id, name FROM customers;
--  id | name
--   1 | Acme
--   3 | Gama

Watch three things when you turn on RLS:

• The table owner and superusers are exempt from policies. Use ALTER TABLE ... FORCE ROW LEVEL SECURITY to make the owner subject to them too.
• Roles with BYPASSRLS skip policies; check regularly who holds it.
• Performance: the policy condition is added to every query; index the columns it uses.

4. Column-Level Access and Masking

Column privileges let you grant access to only some columns of a table:

CREATE TABLE employees (id int, full_name text, tckn text, salary numeric);
REVOKE ALL ON employees FROM PUBLIC;
GRANT SELECT (id, full_name) ON employees TO app_ro;

Here the app_ro role can’t read the national ID number (tckn) or salary columns, and SELECT * fails with a permission error. When data should be partly shown rather than fully hidden, for example only the last four digits of an ID number, you use masking. We cover masking methods for test environments in our PostgreSQL data masking guide.

5. Encryption

6. Audit Logging

PostgreSQL’s server logs can record connections, disconnections and DDL commands. The basic settings:

ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_statement = 'ddl';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d from %h ';
SELECT pg_reload_conf();

When audit requirements go further, for example recording who accessed sensitive tables with which query, the open source pgAudit extension is used. pgAudit produces detailed logs in the format auditors expect, at session level (classes such as all reads and writes) or object level (access to specific tables only).

Checklist for Data Protection Audits

PostgreSQL Security with Aryasoft

Aryasoft’s senior PostgreSQL experts map your environment’s permission matrix, review authentication and encryption settings, configure audit logging to your organization’s requirements and report the findings in priority order. With our experience in regulated sectors such as finance and energy, we help you keep the evidence auditors ask for ready from day one. Details are on our PostgreSQL services page. For general production rules, see our PostgreSQL best practices post.

Frequently Asked Questions

Sources

The technical details in this guide were checked against the following official documentation in November 2026.

1. PostgreSQL documentation: The pg_hba.conf File
2. PostgreSQL documentation: Database Roles
3. PostgreSQL documentation: Predefined Roles
4. PostgreSQL documentation: Row Security Policies
5. PostgreSQL documentation: CREATE FUNCTION (Writing SECURITY DEFINER Functions Safely)
6. PostgreSQL documentation: Secure TCP/IP Connections with SSL
7. PostgreSQL documentation: pgcrypto
8. PostgreSQL documentation: Error Reporting and Logging
9. pgAudit