pgum — PostgreSQL user & database manager

A single-file bash wrapper around psql for the everyday admin chores: creating users and databases, wiring users to databases, and expressing privileges as CRUD letters instead of hand-rolled GRANT statements.

pgum db-create shop --owner shop_admin --user shop_app --access rw --private
pgum grant analyst --db shop --access ro --schema all
pgum perm-show shop_app --db shop
pgum db-drop staging --backup --terminate

Install

chmod +x pgum
sudo install -m 755 pgum /usr/local/bin/pgum   # or add this directory to PATH

Requires bash 3.2+, psql, and pg_dump for db-drop --backup. No bash 4 features and no GNU-only tools, so the bash macOS and the BSDs already ship is enough — nothing to install first.

Installing psql

Only the client is needed — pgum talks to a server over the network, so the client-only packages below are enough (they include pg_dump).

OS	Command
macOS	brew install libpq && brew link --force libpq
Debian / Ubuntu	sudo apt install postgresql-client
Fedora / RHEL / Rocky	sudo dnf install postgresql
Arch	sudo pacman -S postgresql-libs
Alpine	sudo apk add postgresql-client
openSUSE	sudo zypper install postgresql
FreeBSD	sudo pkg install postgresql16-client
Windows (WSL)	use the Debian/Ubuntu line inside WSL
Windows (native)	winget install PostgreSQL.PostgreSQL.17, or the EDB installer with Command Line Tools selected

pgum is a bash script, so on Windows run it from WSL or Git Bash. The bash 3.2 that macOS ships works as-is.

Verify with psql --version. For a newer client than the distro ships, both Debian/Ubuntu and RHEL-family systems have official PostgreSQL repositories: https://www.postgresql.org/download/.

pgum recipes      # walkthroughs for the most common tasks
pgum --help       # complete reference

New here? RECIPES.md has verified, copy-pasteable guides for the usual jobs — an app database with its own admin, read-only users, full access to one database, group roles, password rotation, safe removal, auditing.

Connection settings

Drop a .env in the project and pgum picks it up automatically — no flags:

DATABASE_URL=postgres://postgres:secret@127.0.0.1:5432/shop

pgum user-list                       # uses ./.env
pgum grant analyst --access ro       # --db defaults to shop, from the URL

Precedence, highest first:

1. command-line options (--host, --port, --admin-user, --maint-db)
2. values from the .env file
3. the surrounding environment (PGHOST, PGPORT, PGUSER, PGPASSWORD, …)

The file is looked for in this order, first match wins: --env-file FILE, $PGUM_ENV_FILE, ./.env, then a .env next to the pgum script. --no-env skips the lookup entirely, and -v reports which file was used.

Recognised keys — either a connection string, or the individual libpq variables, which fill in whatever the URL leaves out:

DATABASE_URL=postgres://app:secret@db.example.com:5432/shop?sslmode=require
# POSTGRES_URL and PGUM_DSN work the same way

PGHOST=  PGPORT=  PGUSER=  PGPASSWORD=  PGDATABASE=  PGSSLMODE=  PGPASSFILE=
PGUM_MAINT_DB=postgres          # database used for catalog queries

• Every other key is ignored, so an application's existing .env works as-is — NODE_ENV, SECRET_KEY and friends are simply skipped.
• The file is parsed, never sourced, so it cannot execute code.
• Values may be single- or double-quoted; an unquoted value runs to the end of the line, so quote anything containing #. export KEY=value is accepted.
• Percent-encode @ : / ? # and spaces in a URL password (%40 %3A %2F %3F %23 %20); they are decoded before use.
• The database named in the connection string becomes the default for --db in grant/revoke/perm-show, and is used as the maintenance database if the configured one cannot be reached — which is what managed Postgres services usually require.

See .env.example. If the file holds a password, chmod 600 .env and keep it out of version control.

Commands

Users

Command	Purpose
user-create <user>	create a login role (password prompted, generated, or read from stdin/env/file)
user-list	roles with attributes, connection limits, expiry and group memberships
user-info <user>	one role in detail, including per-database privileges
user-password <user>	rotate a password
user-alter <user>	change role attributes, add/remove group membership
user-rename <user> <new>	rename a role; grants and ownership follow it
user-drop <user>	drop a role, reassigning or dropping what it owns

# interactive prompt (default on a terminal)
pgum user-create alice

# generated password, printed once
pgum user-create svc --generate

# from stdin — never appears in the process list
printf '%s' "$SECRET" | pgum user-create svc --password-stdin

# create and wire up to a database in one step
pgum user-create reporting --generate --db shop --access ro --schema all

# group role: grant once, then move people in and out
pgum user-create readers --no-login
pgum grant readers --db shop --access ro
pgum user-create bob --generate --in-role readers
pgum user-alter erin --in-role readers
pgum user-alter erin --not-in-role readers

# suspend without deleting
pgum user-alter alice --no-login

Rotating a password that is in use

ALTER ROLE ... PASSWORD only affects authentication from that point on, so rotating is safe even for a role with live traffic: sessions that are already open keep working, and only reconnects need the new value.

# rotate the database and the connection string together
NEW=$(openssl rand -base64 32 | tr -dc 'A-Za-z0-9' | head -c 24)
printf '%s' "$NEW" | pgum user-password app_rw --password-stdin
printf 'DATABASE_URL=postgres://app_rw:%s@db.example.com:5432/shop\n' "$NEW" > .env

Rotating the password of the role pgum itself connects as works too — the running command is already authenticated — but the .env is stale afterwards, so pgum warns and names the file to fix. Passwords from --generate are alphanumeric, so they need no percent-encoding inside a URL.

Postgres has no notion of two valid passwords for one role, so a redeploy that reconnects before its config is updated will fail. For true zero-downtime rotation, use two roles that are both members of a group role owning the objects, and move traffic between them.

Databases

Command	Purpose
db-create <db>	create a database, optionally creating and granting users too
db-list	databases with owner, encoding, size, connections and grantees
db-assign <db> <user>	give an existing user access to an existing database
db-drop <db>	drop a database, with confirmation and an optional backup

# database with its own owner, an extra schema, and two app users
pgum db-create billing --owner billing_admin --schema core,audit \
     --user billing_rw --user billing_ro --access rw

# --private revokes CONNECT from PUBLIC, so only granted roles can reach it
pgum db-create shop --owner shop_admin --private

# attach an existing user (CONNECT + schema privileges)
pgum db-assign shop analyst --access ro

# hand over ownership as well
pgum db-assign shop shop_admin --owner

Permissions

Command	Purpose
grant <user> --db DB	grant CRUD letters across the selected schemas
revoke <user> --db DB	take privileges back (--purge for everything)
chown <user> --db DB	make the user own the schemas and everything in them
perm-show <user> --db DB	report effective privileges, including inherited ones

chown covers what grants cannot: ALTER and DROP are reserved to an object's owner. An app that pushes its own schema (Payload, Prisma, Drizzle, Rails) needs both — see recipe 13.

pgum grant myapp --db shop --access rw,ddl --schema all --future-schemas
pgum chown myapp --db shop

The CRUD model

Privileges are expressed as letters and applied to every object kind in the selected schemas:

letter	meaning	tables / views	sequences	functions	schema
c	create rows	INSERT	USAGE, SELECT	–	–
r	read rows	SELECT	SELECT	–	–
u	update rows	UPDATE	USAGE, SELECT	–	–
d	delete rows	DELETE	–	–	–
t	truncate	TRUNCATE	–	–	–
x	execute routines	–	–	EXECUTE	–
n	new objects (DDL)	–	UPDATE	–	CREATE

Presets: ro=r, rw=crud, dml=crud, ddl=n, all=crudtxn, none= connect only. Combine with commas — --access rw,ddl, --access r,x.

• USAGE on the schema is always granted alongside any letter, and CONNECT on the database unless --no-connect.
• Sequence USAGE is included with c/u because inserting into a serial column calls nextval().
• --schema all expands to every non-system schema; --table narrows a grant to named relations.

Objects created later

By default a grant also covers future objects, via ALTER DEFAULT PRIVILEGES keyed on the schema owner. Because PostgreSQL applies default privileges only to objects created by that specific role, point --for-role at whichever role your migrations run as if it differs:

pgum grant app_rw --db shop --access rw --for-role migrator
pgum grant app_rw --db shop --access rw --no-future    # existing objects only

perm-show prints a Default privileges section with a when_created_by column, so a mismatch here is visible rather than mysterious.

Schemas created later

--schema only covers schemas that exist when the grant runs — ALTER DEFAULT PRIVILEGES ... IN SCHEMA requires the schema to already be there. If migrations create schemas, add --future-schemas, which sets database-wide defaults so new schemas grant access automatically:

pgum grant app_rw --db shop --access rw --schema all --future-schemas

Without it, a schema added by a later migration produces permission denied for schema <name> until the grant is re-run. See RECIPES.md § Troubleshooting.

Revoke is deliberately conservative

Sequence privileges are implied by several letters — both INSERT and UPDATE need sequence USAGE — so one is only withdrawn when every letter implying it is part of the same revoke. Otherwise revoke --access u would silently break a still-granted c. Schema USAGE and database CONNECT are likewise left alone.

pgum revoke app_rw --db shop --access u,d              # SELECT and INSERT keep working
pgum revoke analyst --db shop --access all --purge     # cut off completely

Safety

• Injection-safe. Identifiers are always double-quoted (internal quotes doubled) and literals single-quoted with standard_conforming_strings forced on. A password of p'a$$w0rd\x'; DROP TABLE items; -- is stored verbatim as a password and executes nothing.

• Passwords stay out of ps. SQL goes to psql on stdin, never via -c. Prefer --password-stdin, --password-env or --password-file; --password warns because the value is visible in the process list.

• --dry-run prints a runnable psql script (with \connect lines) instead of touching the server, so a change can be reviewed, stored, or replayed:

  pgum --dry-run grant svc --db shop --access all > change.sql
  psql -f change.sql

• db-drop requires the database name typed back, shows owner, size, table count and open connections first, refuses postgres/template0/template1 without --i-know-what-i-am-doing, and refuses an in-use database unless --terminate. --backup takes a pg_dump snapshot after confirmation and aborts the drop if the dump fails.

• user-drop sweeps every database (REASSIGN OWNED / DROP OWNED BY) and hands over owned databases first, so DROP ROLE does not fail half-way. Owned objects are dropped unless --reassign-to is given.

• -y/--yes skips confirmations for unattended runs; confirmation answers can also be piped in.

• db-drop prints the target server alongside the database details, so a .env pointing somewhere unexpected is visible before you confirm.

Because identifiers are always quoted, names are case-sensitive: App and app are different roles. A near-miss produces a hint rather than a bare "does not exist".

Testing

Two suites run against a throwaway server and assert that privileges actually behave as documented — they connect as each created role and check that permitted statements succeed and forbidden ones are denied.

Suite	Covers
test/regress.sh	full lifecycle: provisioning, CRUD enforcement, partial revoke, rotation, guard rails, backup/restore — 29 checks
test/recipes.sh	every recipe in RECIPES.md, exactly as written, plus the permission denied for schema troubleshooting cases — 80 checks

docker run -d --name pgum-test -e POSTGRES_PASSWORD=testpw -p 55432:5432 postgres:18-alpine
bash test/regress.sh
bash test/recipes.sh
docker rm -f pgum-test

Both are re-runnable (they reset their own fixtures) and pass --no-env, so a .env in the working directory cannot redirect them at a real server.