PostgreSQL Administration: Roles, Privileges, and 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 16with superuser access to configure rolespg_stat_statementsextension available- A workload generating varied queries to monitor
- Assumed skill: basic SQL and psql usage
Step-by-Step Execution
The outputs on this page come from a re-run on 14 November 2026 in an Ubuntu 24.04 container with PostgreSQL 16 (arm64). Sample files, users, and hosts were created for the run, so names, numbers, and timestamps reflect that run.
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;Output:
CREATE ROLE
GRANT
GRANT
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;Output:
CREATE EXTENSION
query | mean_exec_time
-----------------------------------------------------+--------------------
CREATE EXTENSION pg_stat_statements | 15.426718
SELECT status, count(*) FROM orders GROUP BY status | 4.944794
SELECT count(*) FROM orders WHERE customer_id = $1 | 3.0708766666666665
(3 rows)
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 the CREATE EXTENSION succeeds but the SELECT fails with ERROR: pg_stat_statements must be loaded via shared_preload_libraries. Load the module first and restart: sudo -u postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'", then sudo systemctl restart postgresql. The output above was captured after that, with a few queries run first so there was something to rank; mean_exec_time is in milliseconds.
3. Limit connections per role
CREATE ROLE app LOGIN; ALTER ROLE app CONNECTION LIMIT 20;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);"Output:
CREATE ROLE
GRANT ROLE
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
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.
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 PRIVILEGESso future tables inherit grants. - pg_stat_statements empty: add it to
shared_preload_librariesand 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.
Frequently Asked Questions
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.
Cite This Page
Citation formats
Plain text
Taki Sadik, "PostgreSQL Administration: Roles, Privileges, and Monitoring", TYFSADIK, https://tyfsadik.org/blog/labs/databases/database-admin (accessed 2026-10-22).
BibTeX
@misc{sadik-database-admin,
author = {Sadik, MD. Taki Yasir Faraji},
title = {PostgreSQL Administration: Roles, Privileges, and Monitoring},
year = {2026},
url = {https://tyfsadik.org/blog/labs/databases/database-admin},
urldate = {2026-10-22}
}
APA
Sadik, M. T. Y. F. (2026). PostgreSQL Administration: Roles, Privileges, and Monitoring. TYFSADIK. https://tyfsadik.org/blog/labs/databases/database-admin