PostgreSQL User Management: A DBA Tool Built for Everyone
Managing PostgreSQL roles and privileges is powerful but notoriously opaque.
This tool wraps every psql command you would ever need into a guided,
eight-page Streamlit application so your entire team can operate safely,
without memorising SQL syntax.
The Problem With psql for Non-DBAs
PostgreSQL has a layered privilege model: you need CONNECT on the database,
USAGE on the schema, and SELECT on individual tables.
Miss any layer and nothing works — but the error message rarely tells you which layer
is missing. Senior DBAs know this by muscle memory. Everyone else opens Stack Overflow.
SELECT on a table but forgettingUSAGE on the schema. The query fails with “permission denied for schema” andnew users spend an hour wondering why. This tool makes that impossible to miss.
This tool solves that by making the hierarchy visible and guiding every action with
plain-English explanations, real-world analogies, and built-in safety checks.
The Privilege Hierarchy — Visualised
Think of your PostgreSQL cluster as a building. Every door has a lock.
| Level | Analogy | Privileges | When Granted |
|---|---|---|---|
| Database | Building entrance key | CONNECT, CREATE, TEMP |
Granted to almost every role |
| Schema | Floor access card | USAGE, CREATE |
Granted per team or domain |
| Table | Room-level permissions | SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER |
Fine-grained per role |
| Sequence | Key to the number dispenser | USAGE, SELECT, UPDATE |
Needed for auto-increment columns |
The app enforces this order — it will not let you grant table-level privileges
until the database and schema levels are confirmed.
Eight Pages. One Workflow.
1. View Users
read
and live privilege summary in one searchable table.
Best for: Anyone auditing access
2. Create User
write
limit, validity date, and role membership with plain-English explanations for each field.
Best for: DBAs, DevOps
3. Manage Privileges
write
at every level with a visual privilege hierarchy and common-mistake callouts.
Best for: DBAs, team leads
4. Default Privileges
advanced
ALTER DEFAULT PRIVILEGES so future objects inherit the rightpermissions automatically. Includes a step-by-step when-to-use guide.
Best for: DBAs setting up new schemas
5. Privilege Reference
read
No docs tab-switching needed.
Best for: Everyone
6. Role Templates
write
Admin Assistant, App Service Account. Every template ships with a best-for guide.
Best for: DBAs onboarding new team members
7. Alter / Drop User
write
warnings, and plain-English explanations of each attribute.
Best for: DBAs
8. Migrate Users
advanced
Covers role attributes, memberships, and privileges.
Best for: DBAs during migrations
Role Templates — Onboard in Seconds
The most time-consuming part of user management is deciding which privileges a new colleague needs.
Five pre-built profiles answer that with a best-for guide and a checklist of exactly what gets created.
| Template | What It Grants | Best For |
|---|---|---|
| Read-Only Analyst | CONNECT + USAGE + SELECT on all tables |
BI tools, reporting queries |
| Developer | SELECT, INSERT, UPDATE, DELETE on app schemas |
Backend services in non-prod |
| Data Engineer | CREATE schema, full table CRUD, TRUNCATE |
ETL pipelines, data loaders |
| Admin Assistant | Read on most tables, limited write on reference data | Ops teams that need light write access |
| App Service Account | Minimal CRUD scoped to the application schema only | Production application connections |
The DBA Guidance Layer
Every page ships a right-side panel powered by dba_help.py — a centralised library
of analogies, warnings, and step-by-step guides. The content is context-sensitive: open
Manage Privileges and you see the privilege hierarchy; open Migrate Users and you see
what migrates versus what you must re-create manually.
it explains what the active form will do before you click.
Analogies
Common Mistakes
Step Guides
Attribute Glossary
SUPERUSER, CREATEROLE, INHERIT, BYPASSRLS, and every role attribute.Migrate Users Across Servers
The Migrate Users page handles the scenario every DBA dreads: moving role definitions
from an old cluster to a new one. Supply source and target connection details, pick the
roles to migrate, and the tool scripts the transfer — with a clear side-by-side summary
of what will and will not be carried over.
✓ What Migrates
- + Role name and attributes
- + Password hash (MD5 or SCRAM)
- + Role memberships
- + Connection limit
- + Validity dates
↻ Must Re-Create Manually
- ― Object privileges (GRANT statements)
- ― Default privileges
- ― Schema ownership
- ― Row-level security policies
Safety and Security Model
🔒 No Stored Passwords
💾 Saved Connections
🔄 Transaction Safety
ensure_clean_conn() to roll back any aborted transaction before retrying, preventing silent partial-state bugs.✅ Dependency Checks
Getting Started
pip install -r requirements.txtstreamlit run app.py
4. Connect
Enter host, port, database, username, and password on the connection screen.
5. Save the connection
Project Structure
| File / Folder | Purpose |
|---|---|
app.py |
Entry point, navigation, saved-connection manager |
db_utils.py |
psycopg2 helpers, ensure_clean_conn(), all DB queries |
dba_help.py |
All right-panel content: analogies, warnings, guides |
pages/1_View_Users.py |
User table browser with attribute tooltips |
pages/2_Create_User.py |
Wizard form with inline attribute guide |
pages/3_Manage_Privileges.py |
4-tab privilege management UI |
pages/4_Default_Privileges.py |
ALTER DEFAULT PRIVILEGES interface |
pages/5_Privilege_Reference.py |
In-app privilege cheat-sheet |
pages/6_Role_Templates.py |
One-click role provisioning |
pages/7_Alter_Drop_User.py |
Safe user modification and drop |
pages/8_Migrate_Users.py |
Cross-server role migration |
requirements.txt |
streamlit, psycopg2-binary, pandas |
What Could Come Next
The core feature set is stable. Here are natural extensions for the future:
Built by a DBA, For Everyone Else
PostgreSQL is one of the most powerful databases in the world — but its privilege
model is also one of the hardest to operate safely without deep SQL knowledge.
This tool bridges that gap: it gives DBAs a faster workflow, and gives everyone
else a safe, guided path to the same outcomes.
Python 3.8+
PostgreSQL 12+
Streamlit 1.x