PostgreSQL Administration: Roles, Privileges, and Monitoring

IntermediateDatabases~4 hours
GRANTrolespg_stat_statementsmonitoring

I administered PostgreSQL with role-based access control, least-privilege GRANTs, connection limits, and query monitoring via pg_stat_statements. Separating roles by function and tracking slow queries kept the database both secure and observable.

Objective & Context

DBA work is access control plus observability. This lab builds a role hierarchy (readonly, readwrite, admin), applies least-privilege GRANTs, and enables statement statistics to surface the slowest queries, aligning to NIST AC-6 least privilege.

Environment & Prerequisites

  • PostgreSQL 16 with superuser access to configure roles.
  • pg_stat_statements extension available.
  • A workload generating varied queries.

Step-by-Step Execution

1. Create roles and grant least privilege

CREATE ROLE readonly; GRANT CONNECT ON DATABASE appdb TO readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

2. Enable query monitoring

CREATE EXTENSION pg_stat_statements; SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;

3. Limit connections per role

ALTER ROLE app CONNECTION LIMIT 20;
query                          mean_exec_time
SELECT * FROM orders WHERE...  142.6 ms

Validation & Testing

Connect as the readonly role and confirm writes are denied; review pg_stat_statements to identify the slowest query. Pass criteria: roles enforce least privilege, connection limits apply, and the slowest queries are visible for tuning.

Advanced: Troubleshooting
  • New tables not granted: set ALTER DEFAULT PRIVILEGES so future tables inherit grants.
  • pg_stat_statements empty: add it to shared_preload_libraries and restart.
  • Connection exhaustion: use a pooler (PgBouncer) for many short-lived clients.

Key Results

  • Enforced least privilege via a 3-tier role hierarchy.
  • Surfaced the top 5 slowest queries with pg_stat_statements.
  • Capped per-role connections to protect the server.
  • Applied default privileges so new tables stay governed.

Related Labs