PostgreSQL Privilege & Security

PostgreSQL’s permission model is both fine‑grained and flexible, allowing you to craft a database that meets strict security requirements without sacrificing usability. By combining role‑based access control, object‑level privileges, row‑level security, and robust connection authentication, you can create a resilient foundation for your applications.

Role Management: Users, Groups, and Attributes

In PostgreSQL, the terms user and role are interchangeable. A role can be granted the LOGIN attribute, turning it into a user that can connect to the database. The recommended pattern is to create role groups for shared permissions and then assign individual users to those groups.

-- Create a login role (user)
CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPass123';

-- Create a non‑login role that acts as a group
CREATE ROLE app_readonly;

-- Grant the group to the user
GRANT app_readonly TO app_user;

Common role attributes include:

• SUPERUSER – full control over the cluster; use sparingly.
• CREATEDB / CREATEROLE – allow creation of databases or other roles.
• LOGIN – permits connection to a database.
• REPLICATION – enables streaming replication sessions.

By keeping SUPERUSER roles to a minimum and delegating responsibilities through groups, you enforce the principle of least privilege.

Object Privileges: GRANT and REVOKE

Privileges are applied at several object levels:

Example grants:

-- Allow a role to use a schema
GRANT USAGE ON SCHEMA sales TO app_readonly;

-- Give a user read/write access to a table
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;

-- Revoke all privileges from a sensitive table
REVOKE ALL ON TABLE salary_data FROM app_user;

To inspect current permissions, you can query information_schema.role_table_grants or use the \dp meta‑command in psql.

Avoiding the Pitfalls of PUBLIC

PUBLIC is an implicit role that every user inherits. Historically, the default public schema allowed all users to create objects, which can lead to accidental privilege escalation. It is a best practice to revoke unnecessary rights:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

For future objects, set default privileges so that new tables automatically grant the desired permissions:

ALTER DEFAULT PRIVILEGES IN SCHEMA sales
    GRANT SELECT ON TABLES TO app_readonly;

Row‑Level Security (RLS) for Data Isolation

When multiple tenants or departments share a single table, RLS lets you enforce visibility rules at the row level. Enable RLS on the table and create a policy that references a session variable:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::int);

After connecting, set the tenant identifier:

SET app.tenant_id = '1001';

Key points to remember:

• Table owners are exempt from RLS unless FORCE ROW LEVEL SECURITY is specified.
• Roles with BYPASSRLS or SUPERUSER bypass all policies.

Connection Security: pg_hba.conf and SSL

The pg_hba.conf file controls client authentication. Each line specifies connection type, database, user, client address, and authentication method.

# TYPE    DATABASE  USER      ADDRESS       METHOD
hostssl   shop      app_user  10.0.0.0/24   scram-sha-256
local     all       postgres                peer

Recommendations

• Use scram-sha-256 for password authentication; avoid trust and md5.
• Prefer hostssl to enforce encrypted connections; enable ssl = on in postgresql.conf.
• Restrict listen_addresses to known hosts and pair with firewall rules for network isolation.

Security Best Practices

1. Least Privilege – Operate applications with the lowest permission set necessary; never connect as postgres or a superuser.
2. Role Grouping – Organize roles by function (e.g., app_readonly, app_editor) and grant users membership; this simplifies bulk changes.
3. Audit Logging – Enable log_statement = 'ddl' or log_statement = 'mod' to capture schema changes and data modifications. Use pg_stat_activity to spot anomalous connections.
4. Password Hygiene – Enforce strong, rotating passwords. Store credentials securely (e.g., secrets manager) rather than in plain scripts.
5. Patch Management – Keep PostgreSQL up to date with the latest minor releases; security patches are often released in these updates.

Putting It All Together

A secure PostgreSQL deployment is an ongoing effort. Start by defining a clear role hierarchy and applying the least privilege principle. Use object privileges to control access at the schema and table levels, and employ RLS for fine‑grained data isolation. Secure the wire with SSL and a well‑configured pg_hba.conf. Finally, maintain vigilance through logging, monitoring, and timely updates.

By embedding these practices early in your project lifecycle and revisiting them regularly, you can safeguard your data while preserving the flexibility that PostgreSQL offers.