How do I fix 'permission denied for table'?

Likely cause: GRANT was given on the database but not on the schema or tables. Fix: grant USAGE on the schema and SELECT on tables; use ALTER DEFAULT PRIVILEGES for future tables.

How do I fix 'pg_stat_statements shows no rows'?

Likely cause: the extension is not in shared_preload_libraries or not created. Fix: add it to shared_preload_libraries, restart, then CREATE EXTENSION.

How do I fix 'role still connects over the limit'?

Likely cause: superusers bypass connection limits. Fix: limits apply to non-superusers; also check pooler-level limits.

How do I fix 'GRANT on all tables misses new tables'?

Likely cause: the grant covered existing tables only. Fix: set ALTER DEFAULT PRIVILEGES so future tables inherit the grants.

How do I fix 'cannot drop a role'?

Likely cause: objects are still owned by it. Fix: REASSIGN OWNED BY the role to a successor, then DROP OWNED BY, then drop the role.

Database Administration | Roles, GRANT & Monitoring

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.

Learning Objectives

Create roles separated by function (readonly, app, admin).
Grant least privilege with GRANT on databases, schemas, and tables.
Enable pg_stat_statements for query monitoring.
Find slow queries by mean execution time.
Cap connections per role to protect the server.

Environment & Prerequisites

Prerequisites

PostgreSQL 16 with superuser access to configure roles
pg_stat_statements extension available
A workload generating varied queries to monitor
Assumed skill: basic SQL and psql usage

flowchart TB Admin[admin role] --> RW[readwrite role] RW --> RO[readonly role] RO --> U[app users]

Step-by-Step Execution

1. Create roles and grant least privilege

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

Expected output:

CREATE ROLE / GRANT

What this teaches: Separating readonly from app roles means a reporting query can never accidentally write; least privilege limits the blast radius of every credential.

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;

Expected output:

query                          mean_exec_time
SELECT * FROM orders WHERE...  142.6 ms

What this teaches: pg_stat_statements aggregates real execution stats across all sessions; sorting by mean time surfaces the queries worth optimizing first. On a fresh install, CREATE EXTENSION alone fails: add shared_preload_libraries = 'pg_stat_statements' to postgresql.conf and restart the server (sudo systemctl restart postgresql) before creating the extension.

3. Limit connections per role

CREATE ROLE app LOGIN; ALTER ROLE app CONNECTION LIMIT 20;

query                          mean_exec_time
SELECT * FROM orders WHERE...  142.6 ms

Expected output:

CREATE ROLE
ALTER ROLE

What this teaches: Connection caps stop one misbehaving service from exhausting max_connections and locking everyone else out. The cap is set per role at the database level; a pooler enforces its own ceiling on top.

4. Prove the readonly role cannot write

CREATE ROLE analyst LOGIN PASSWORD 'TempPass123!'; GRANT readonly TO analyst;

psql -h 127.0.0.1 -d appdb -U analyst -c "INSERT INTO orders (customer_id) VALUES (1);"

Expected output:

ERROR:  permission denied for table orders

What this teaches: The group role carries the privileges and the login role inherits them for testing. If password authentication fails, add an scram-sha-256 pg_hba line for 127.0.0.1 and reload the configuration before testing.

How It Works

Role-based access control separates identity from permission: roles group privileges, and logins inherit them. GRANT CONNECT plus schema USAGE plus table SELECT composes the minimal set a reader needs; anything more is unnecessary risk.

pg_stat_statements is a shared-library extension that normalizes and aggregates query statistics server-wide. Unlike slow-query logs it costs little overhead and answers 'which queries cost the most total time' directly, which is the right question for optimization triage.

Connection limits are a bulkhead: each role gets a ceiling so a connection leak or traffic spike in one service degrades that service, not the whole database. Combined with a pooler, caps turn connection storms into queued waits instead of outages.

flowchart TD SA["superuser"] --> RA["role: readonly
CONNECT + SELECT"] SA --> RB["role: app
+ INSERT/UPDATE"] SA --> RC["role: admin
DDL + maintenance"] RB --> CAP["CONNECTION LIMIT 20"] MON["pg_stat_statements
slow query watch"] --> SA

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.

Troubleshooting

Symptom	Likely cause	Fix
permission denied for table	GRANT was given on the database but not on the schema or tables	grant USAGE on the schema and SELECT on tables; use ALTER DEFAULT PRIVILEGES for future tables
pg_stat_statements shows no rows	the extension is not in shared_preload_libraries or not created	add it to shared_preload_libraries, restart, then CREATE EXTENSION
role still connects over the limit	superusers bypass connection limits	limits apply to non-superusers; also check pooler-level limits
GRANT on all tables misses new tables	the grant covered existing tables only	set ALTER DEFAULT PRIVILEGES so future tables inherit the grants
cannot drop a role	objects are still owned by it	REASSIGN OWNED BY the role to a successor, then DROP OWNED BY, then drop the role

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.

Key Takeaways

Separate roles by function; never share one superuser credential across services.
Least privilege composes: CONNECT, USAGE, then only the needed table rights.
pg_stat_statements finds the queries worth optimizing by total cost.
Connection limits are bulkheads against one service starving the rest.
ALTER DEFAULT PRIVILEGES keeps grants applying to future tables.

Practice Challenges

Challenge 1: Read-only analyst

Goal: Create an analyst role that can SELECT from reporting schema but cannot see the users table at all.

Hint: Grant USAGE on the schema, SELECT on specific tables only, and revoke public defaults if present.

Challenge 2: Slow query triage

Goal: From pg_stat_statements, find the query with the highest total time and propose the fix.

Hint: Sort by total_exec_time, not just mean; a fast query run millions of times can dominate.

PostgreSQL Administration: Roles, Privileges, and Monitoring

Objective & Context

Learning Objectives

Environment & Prerequisites

Prerequisites

Step-by-Step Execution

1. Create roles and grant least privilege

2. Enable query monitoring

3. Limit connections per role

4. Prove the readonly role cannot write

How It Works

How It Works

Validation & Testing

Troubleshooting

Key Results

Key Takeaways

Practice Challenges

Challenge 1: Read-only analyst

Challenge 2: Slow query triage

Related Labs