{"id":83191,"date":"2026-09-23T11:23:44","date_gmt":"2026-09-23T05:53:44","guid":{"rendered":"https:\/\/2thenew.online\/blog\/?p=83191"},"modified":"2026-09-29T14:50:58","modified_gmt":"2026-09-29T09:20:58","slug":"postgresql-user-management-its-not-that-difficult","status":"publish","type":"post","link":"https:\/\/2thenew.online\/blog\/postgresql-user-management-its-not-that-difficult\/","title":{"rendered":"PostgreSQL User Management: It\u2019s Not That Difficult"},"content":{"rendered":"
\n
\n

PostgreSQL User Management: A DBA Tool Built for Everyone<\/h1>\n

Managing PostgreSQL roles and privileges is powerful but notoriously opaque.
\nThis tool wraps every psql<\/code> command you would ever need into a guided,
\neight-page Streamlit application so your entire team can operate safely,
\nwithout memorising SQL syntax.<\/p>\n<\/section>\n

\n

<\/p>\n

\n

The Problem With psql for Non-DBAs<\/h2>\n

PostgreSQL has a layered privilege model: you need CONNECT<\/code> on the database,
\nUSAGE<\/code> on the schema, and SELECT<\/code> on individual tables.
\nMiss any layer and nothing works \u2014 but the error message rarely tells you which layer
\nis missing. Senior DBAs know this by muscle memory. Everyone else opens Stack Overflow.<\/p>\n

The most common mistake:<\/strong> granting SELECT<\/code> on a table but forgetting
\nUSAGE<\/code> on the schema. The query fails with “permission denied for schema”<\/em> and
\nnew users spend an hour wondering why. This tool makes that impossible to miss.<\/div>\n

This tool solves that by making the hierarchy visible and guiding every action with
\nplain-English explanations, real-world analogies, and built-in safety checks.<\/p>\n<\/section>\n

\n

<\/p>\n

\n

The Privilege Hierarchy \u2014 Visualised<\/h2>\n

Think of your PostgreSQL cluster as a building. Every door has a lock.<\/p>\n

\n\n\n\n\n\n\n\n\n
Level<\/th>\nAnalogy<\/th>\nPrivileges<\/th>\nWhen Granted<\/th>\n<\/tr>\n<\/thead>\n
Database<\/strong><\/td>\nBuilding entrance key<\/td>\nCONNECT<\/code>, CREATE<\/code>, TEMP<\/code><\/td>\nGranted to almost every role<\/td>\n<\/tr>\n
Schema<\/strong><\/td>\nFloor access card<\/td>\nUSAGE<\/code>, CREATE<\/code><\/td>\nGranted per team or domain<\/td>\n<\/tr>\n
Table<\/strong><\/td>\nRoom-level permissions<\/td>\nSELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, DELETE<\/code>, TRUNCATE<\/code>, REFERENCES<\/code>, TRIGGER<\/code><\/td>\nFine-grained per role<\/td>\n<\/tr>\n
Sequence<\/strong><\/td>\nKey to the number dispenser<\/td>\nUSAGE<\/code>, SELECT<\/code>, UPDATE<\/code><\/td>\nNeeded for auto-increment columns<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

The app enforces this order \u2014 it will not let you grant table-level privileges
\nuntil the database and schema levels are confirmed.<\/p>\n<\/section>\n

\n

<\/p>\n

\n

Eight Pages. One Workflow.<\/h2>\n
\n
\n
\n

1. View Users<\/h3>\n

read<\/span><\/p>\n<\/div>\n

Browse all database roles with login attributes, connection limits, validity,
\nand live privilege summary in one searchable table.<\/p>\n

Best for: Anyone auditing access<\/p>\n<\/div>\n<\/div>\n

\n
\n

2. Create User<\/h3>\n

write<\/span><\/p>\n<\/div>\n

Wizard-style form with inline attribute guides. Covers password, connection
\nlimit, validity date, and role membership with plain-English explanations for each field.<\/p>\n

Best for: DBAs, DevOps<\/p>\n<\/div>\n<\/div>\n

\n
\n

3. Manage Privileges<\/h3>\n

write<\/span><\/p>\n<\/div>\n

Four tabs: Database, Schema, Table, and Roles Attached. Grant or revoke
\nat every level with a visual privilege hierarchy and common-mistake callouts.<\/p>\n

Best for: DBAs, team leads<\/p>\n<\/div>\n<\/div>\n

\n
\n

4. Default Privileges<\/h3>\n

advanced<\/span><\/p>\n<\/div>\n

Set ALTER DEFAULT PRIVILEGES<\/code> so future objects inherit the right
\npermissions automatically. Includes a step-by-step when-to-use guide.<\/p>\n

Best for: DBAs setting up new schemas<\/p>\n<\/div>\n<\/div>\n

\n
\n

5. Privilege Reference<\/h3>\n

read<\/span><\/p>\n<\/div>\n

In-app cheat-sheet mapping each PostgreSQL privilege to real-world analogies.
\nNo docs tab-switching needed.<\/p>\n

Best for: Everyone<\/p>\n<\/div>\n<\/div>\n

\n
\n

6. Role Templates<\/h3>\n

write<\/span><\/p>\n<\/div>\n

One-click role provisioning: Read-Only Analyst, Developer, Data Engineer,
\nAdmin Assistant, App Service Account. Every template ships with a best-for guide.<\/p>\n

Best for: DBAs onboarding new team members<\/p>\n<\/div>\n<\/div>\n

\n
\n

7. Alter \/ Drop User<\/h3>\n

write<\/span><\/p>\n<\/div>\n

Safe attribute editing and role dropping with dependency checks, safety
\nwarnings, and plain-English explanations of each attribute.<\/p>\n

Best for: DBAs<\/p>\n<\/div>\n<\/div>\n

\n
\n

8. Migrate Users<\/h3>\n

advanced<\/span><\/p>\n<\/div>\n

Cross-server role migration with a clear what-migrates \/ what-does-not panel.
\nCovers role attributes, memberships, and privileges.<\/p>\n

Best for: DBAs during migrations<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/section>\n

\n

<\/p>\n

\n

Role Templates \u2014 Onboard in Seconds<\/h2>\n

The most time-consuming part of user management is deciding which privileges a new colleague needs.
\nFive pre-built profiles answer that with a best-for guide and a checklist of exactly what gets created.<\/p>\n

\n\n\n\n\n\n\n\n\n\n
Template<\/th>\nWhat It Grants<\/th>\nBest For<\/th>\n<\/tr>\n<\/thead>\n
Read-Only Analyst<\/strong><\/td>\nCONNECT<\/code> + USAGE<\/code> + SELECT<\/code> on all tables<\/td>\nBI tools, reporting queries<\/td>\n<\/tr>\n
Developer<\/strong><\/td>\nSELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, DELETE<\/code> on app schemas<\/td>\nBackend services in non-prod<\/td>\n<\/tr>\n
Data Engineer<\/strong><\/td>\nCREATE<\/code> schema, full table CRUD, TRUNCATE<\/code><\/td>\nETL pipelines, data loaders<\/td>\n<\/tr>\n
Admin Assistant<\/strong><\/td>\nRead on most tables, limited write on reference data<\/td>\nOps teams that need light write access<\/td>\n<\/tr>\n
App Service Account<\/strong><\/td>\nMinimal CRUD scoped to the application schema only<\/td>\nProduction application connections<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/section>\n
\n

<\/p>\n

\n

The DBA Guidance Layer<\/h2>\n

Every page ships a right-side panel powered by dba_help.py<\/code> \u2014 a centralised library
\nof analogies, warnings, and step-by-step guides. The content is context-sensitive: open
\nManage Privileges and you see the privilege hierarchy; open Migrate Users and you see
\nwhat migrates versus what you must re-create manually.<\/p>\n

The guidance panel is read-only<\/strong>. It never changes your database \u2014
\nit explains what the active form will do before you click.<\/div>\n
\n
\n
\n

Analogies<\/h3>\n<\/div>\n
Every privilege explained as a real-world lock-and-key metaphor, not just SQL syntax.<\/div>\n<\/div>\n
\n
\n

Common Mistakes<\/h3>\n<\/div>\n
Callouts for the top five errors: the schema USAGE trap, sequence permissions, public schema risks.<\/div>\n<\/div>\n
\n
\n

Step Guides<\/h3>\n<\/div>\n
Numbered walkthroughs for Default Privileges, cross-server migration, and role templates.<\/div>\n<\/div>\n
\n
\n

Attribute Glossary<\/h3>\n<\/div>\n
Plain-English definitions of SUPERUSER<\/code>, CREATEROLE<\/code>, INHERIT<\/code>, BYPASSRLS<\/code>, and every role attribute.<\/div>\n<\/div>\n<\/div>\n<\/section>\n
\n

<\/p>\n

\n

Migrate Users Across Servers<\/h2>\n

The Migrate Users page handles the scenario every DBA dreads: moving role definitions
\nfrom an old cluster to a new one. Supply source and target connection details, pick the
\nroles to migrate, and the tool scripts the transfer \u2014 with a clear side-by-side summary
\nof what will and will not be carried over.<\/p>\n

\n
\n
\n

\u2713 What Migrates<\/h3>\n<\/div>\n
\n