PostgreSQL 13–17 · builders, recipes & DBA tools

connection

🔒 Passwords are never stored. Use ~/.pgpass or PGPASSWORD in your shell. Profiles live only in this browser's localStorage.

builder

pg_dump

  

recipes

security

Roles, audits, SSL, row-level security and logging for PostgreSQL 13–17. RDS/Aurora marks items that only apply there. On RDS the master user is a member of rds_superuser, not a real superuser, and server settings go in the parameter group.

Security role builder

Read-write gets DML and sequence usage, but no DDL or TRUNCATE.
NOLOGIN role that holds the grants.
Gets GRANT CONNECT (more databases below).
Comma-separated, e.g. app, reporting.
Used in ALTER DEFAULT PRIVILEGES FOR ROLE.
Created without a password; set one with \password.

Database CONNECT

Comma-separated, added to the database above. One GRANT per database; databases are cluster-wide, so these run from any database.
Comma-separated roles/users. Members of the group role inherit it.
Off by default. Afterwards only roles with an explicit CONNECT grant (plus owners/superusers) can connect; check app and monitoring users first.

Predefined (system) roles

Comma-separated roles/users. Each grant is skipped with a NOTICE if the predefined role doesn't exist on that server version.
pg_database_owner is not grantable (membership is implicit for the database owner), so it is not offered.
role.sql

Database role model (reader / writer / app_user)

Generates a re-runnable psql script that creates a NOLOGIN owner and three access roles chained by membership. It validates its inputs and stops on the first error, then prints a verification report.

Privilege model
RoleGetsHow
owner {db}_ownerOwns the database, schemas and all objects. Runs DDL and migrations via SET ROLE.NOLOGIN. The admin running the script is granted it (required on RDS: master user → owner).
reader {db}_readerCONNECT; USAGE on the schemas; SELECT on tables, views and sequencesDirect grants plus ALTER DEFAULT PRIVILEGES FOR ROLE owner for future objects
writer {db}_writerreader + INSERT/UPDATE/DELETE (TRUNCATE optional); USAGE/SELECT/UPDATE on sequences; EXECUTE on functionsGRANT reader TO writer (inherits), plus its own grants and default privileges
app_user {db}_app_userwriter + TEMP tables. The role the application connects with; no CREATE anywhere, so no DDL.GRANT writer TO app_user; search_path set to the managed schemas
PUBLICNothing (with the revoke option): no CONNECT/TEMP on the database, no CREATE in schema public, no EXECUTE on new functionsREVOKE … FROM PUBLIC

Keep current owners (existing databases, e.g. Aurora): the owner row above doesn't apply. Objects keep their owners, and each one is granted to reader/writer individually. Default privileges are added FOR ROLE each role that owns objects in the managed schemas (auto-detected or listed), so future objects from any of them are covered. Foreign tables get the same table grants. Querying them also needs a user mapping for the role (or PUBLIC). USAGE on the foreign server is optional; it only lets reader create its own user mappings or foreign tables.

Why membership: each privilege is granted once, so writer can never fall behind reader. The roles must be INHERIT, which the script enforces. A NOINHERIT role would only get the privileges after SET ROLE, which apps don't do. People get access by being granted one of the three roles (GRANT {db}_reader TO alice). RDS/Aurora: there's no superuser, so run it as the master user (rds_superuser, CREATEROLE, CREATEDB). The script grants the owner role to that user, because CREATE DATABASE … OWNER, SET ROLE and ALTER DEFAULT PRIVILEGES FOR ROLE all require it.

Default for -v database_name.
Comma-separated managed schemas, e.g. public, app. The first one hosts the verification test.
Create stops if the DB exists; extend stops if it doesn't.
{db} is replaced with the database name.
Read-only.
Read + DML.
What the application logs in as.
Creates them with LOGIN and no password; set one with \password. The owner never logs in.
REVOKE ALL ON DATABASE, CREATE ON SCHEMA public and EXECUTE on new functions FROM PUBLIC.
No owner role, no ALTER … OWNER, no CREATE DATABASE. Grants each object as it is; default privileges FOR ROLE each object creator.
Comma-separated. Empty: every role that owns objects in the managed schemas.
Querying a foreign table needs SELECT on it plus a user mapping for the querying role (or PUBLIC). Server USAGE is only needed to create foreign tables or your own user mappings.
Off by default: TRUNCATE skips DELETE triggers and RLS.
how to run
role_model.sql

admin tools

Generators for day-to-day DBA work: default privileges, privilege audits, ownership fixes, dropping roles, login users and session/lock troubleshooting. Each one builds its script from the form; nothing runs from the page.

sample data

Practice schema demo (customers, products, categories, orders, order_items) with data loaders and queries from basic to advanced. Run it on a practice database:

psql -d mydb -v ON_ERROR_STOP=1 -f demo.sql

The full script runs the schema, hand-written inserts, generated data (with these counts) and every query. Cleanup is included but commented out.