ops db

PostgreSQL database user and role management. All commands connect to the wg_digests PostgreSQL database. Supports idempotent user creation, RBAC role management, and grant auditing.

Single Target

Every ops db subcommand operates against the wg_digests PostgreSQL database. There is no --database flag — connection details are sourced from environment configuration.

Subcommands

Subcommand	Description
schemas	List PostgreSQL schemas
users	List PostgreSQL users with schema access
grants	Show a user's schema and table grants
roles	List PostgreSQL roles and their members
create-role	Create a PostgreSQL role (idempotent)
add-to-role	Add a user to a role
remove-from-role	Remove a user from a role
audit-roles	Audit schemas for missing wasteology_admin / wasteology_readonly grants
test-connection	Test a user's database connection and show accessible schemas
create-user	Create a PostgreSQL user with schema access
create-schema	Create a PostgreSQL schema
change-password	Change a PostgreSQL user's password
grant-schema	Grant access on additional schemas to an existing user
revoke-schema	Revoke all access on schemas from a user

---

Common Workflows

👤

Onboard a New User

create-user provisions an account with a starter role, then grant-schema adds extra schema access as needed.

🔐

Audit Permissions

roles, grants, and audit-roles together answer "who can read what" without leaving the terminal.

🧪

Verify Access

test-connection confirms a user can actually authenticate and lists every schema they can reach.

---

Inspection Commands

ops db schemas

List every schema in the wg_digests database.

Enumerate all PostgreSQL schemas

Terminal

$ops db schemas

ops db users

List PostgreSQL users along with the schemas they have access to.

Show every user and their schema access map

Terminal

$ops db users

ops db grants

Show a single user's schema- and table-level grants in detail.

Drill into one user's full grant map

Terminal

$ops db grants alice

Inherited Grants

This command resolves inherited grants via pg_auth_members, not just direct role_table_grants entries. That means grants picked up through wasteology_admin or pg_read_all_data show up too.

ops db roles

List every PostgreSQL role and its members.

See which users belong to which roles

Terminal

$ops db roles

ops db test-connection

Test a user's database connection and list the schemas they can actually reach.

Verify connectivity and effective access for one user

Terminal

$ops db test-connection alice

---

User Management

ops db create-user

Create a PostgreSQL user with schema access. Idempotent — running it against an existing user updates role assignments without erroring out.

Provision a new user with read-only access

Terminal

$ops db create-user alice --role wasteology_readonly

ops db change-password

Rotate a user's password. Prompts interactively for the new value.

Change a user's password (prompted)

Terminal

$ops db change-password alice

Shell-Safe Passwords

Generated passwords must avoid $, !, `, and \ — these characters break in double-quoted bash strings and downstream tooling. Prefer alphanumeric + a small punctuation set.

ops db grant-schema

Grant a user access on one or more additional schemas.

Add a schema to an existing user's access list

Terminal

$ops db grant-schema alice analytics

ops db revoke-schema

Revoke all access on a schema from a user.

Remove a user's access to a schema entirely

Terminal

$ops db revoke-schema alice analytics

---

Role Management

ops db create-role

Create a PostgreSQL role. Idempotent — re-running against an existing role is a no-op.

Create a role (safe to re-run)

Terminal

$ops db create-role wasteology_readonly

ops db add-to-role

Add a user to a role, granting them every privilege the role carries.

Promote a user into an admin role

Terminal

$ops db add-to-role alice wasteology_admin

ops db remove-from-role

Remove a user from a role.

Demote a user out of a role

Terminal

$ops db remove-from-role alice wasteology_admin

---

Schema Management

ops db create-schema

Create a new PostgreSQL schema in wg_digests.

Provision a new schema

Terminal

$ops db create-schema analytics

Audit After Creating

Use ops db audit-roles after creating a new schema to ensure wasteology_admin and wasteology_readonly roles have the correct grants.

ops db audit-roles

Scan every schema for missing wasteology_admin / wasteology_readonly grants. Highlights schemas where the standard roles do not have the expected privileges so you can repair drift.

Find schemas where the standard roles are missing grants

Terminal

$ops db audit-roles

When to Run

Run after creating new schemas, after dbt model deployments that create new tables, and as part of periodic RBAC reviews.