DEV Community

Cover image for Stop Running psql Commands by Hand — Build a REST API for PostgreSQL User Management
Pranay Ravi
Pranay Ravi

Posted on

Stop Running psql Commands by Hand — Build a REST API for PostgreSQL User Management

#postgres #python #devops #platformengineering

If you manage PostgreSQL databases across multiple environments, you've probably done this:

  1. SSH to the DB host (or connect via psql)
  2. Run CREATE USER jsmith CONNECTION LIMIT 20 PASSWORD '...'
  3. Slack the password to the developer
  4. Forget to log it anywhere
  5. Repeat for every environment, every onboarding, every access request

It's tedious, error-prone, and leaves zero audit trail. Here's a better way.

What I Built

pg-user-api is a lightweight Flask REST API that wraps PostgreSQL user provisioning in clean HTTP endpoints. You register your databases once in a SQLite inventory, then any tooling — CI pipelines, internal portals, Ansible playbooks, or a plain curl — can create and manage users across environments without ever touching psql.

GitHub: pcraavi/PostgreSQL-user-creation-API

The Problem It Solves

In teams that span dev, QA, UAT, and prod, you end up with different patterns of users:

  • App service accounts — named after the host/port combo (web01_8080)
  • Kubernetes workload accounts — named after env prefix + farm (dv_gearservice)
  • Individual dev/QA accounts — low connection limits, scoped to non-prod
  • Read-only analyst accounts — prod only, no DDL
  • DBA accounts — CREATEDB CREATEROLE LOGIN, rarely provisioned

Each type has different CONNECTION LIMIT values, privilege levels, and naming conventions. Encoding these patterns in an API means the rules are consistent, repeatable, and auditable.

Architecture

The project is intentionally small — five Python files and a requirements list:

pg_user_api/
├── app.py              # Flask app — all endpoints
├── auth.py             # HTTP Basic Auth (constant-time compare)
├── database.py         # SQLite registry + audit log
├── notifications.py    # Notification stubs (Webex / Slack / Email)
├── seed_db.py          # One-time setup: creates DB + sample records
└── requirements.txt
Enter fullscreen mode Exit fullscreen mode

Two credential pairs, clearly separated:

  • PG_API_USER / PG_API_PASS — who can call this API (your team/tooling)
  • PG_ADMIN_USER / PG_ADMIN_PASS — the PostgreSQL DBA role that executes DDL

The DBA credentials never appear in API URLs or response bodies. Callers only need the API credentials plus env and database.

SQLite as a config-free registry:

Rather than a static YAML or environment file listing every hostname, databases are registered once in a db_registry table:

Column Description
env dev / qa / uat / prod
db_name PostgreSQL database name
hostname FQDN or IP of the host
port PostgreSQL port (default 5432)
active 1 = active, 0 = skip

Every endpoint looks up the hostname dynamically from this registry. No hardcoded connection strings anywhere in application code.

The Endpoints

All endpoints are GET with query params (by design — simple to curl, simple to call from automation scripts).

GET /                            # Health check, no auth required
GET /api/v1/registry             # List registered databases
GET /api/v1/users/all            # List all PostgreSQL roles on a database
GET /api/v1/users/app            # Create VM/container service account
GET /api/v1/users/app-k8s        # Create Kubernetes workload account
GET /api/v1/users/devqa          # Create individual dev/QA user
GET /api/v1/users/devlead        # Create dev-lead user
GET /api/v1/users/readonly       # Create read-only user
GET /api/v1/users/dba            # Create DBA user (CREATEDB + CREATEROLE)
GET /api/v1/users/reset          # Reset a user's password
GET /api/v1/users/search-path    # Update search_path for a user
GET /api/v1/users/find           # Look up a specific user
Enter fullscreen mode Exit fullscreen mode

Every user-creation endpoint returns the same structured response:

{
  "username": "web01_8080",
  "password": "generatedSecurePassword",
  "status": "user created",
  "hostname": "pg-dev-01.example.com",
  "database": "myapp_dev",
  "port": "5432",
  "env": "dev"
}
Enter fullscreen mode Exit fullscreen mode

Password is returned once at creation time. The API uses secrets.token_urlsafe(16) for generation — no insecure random module.

Running It

1. Clone and install 

git clone https://github.com/pcraavi/PostgreSQL-user-creation-API.git
cd PostgreSQL-user-creation-API
pip install -r requirements.txt
Enter fullscreen mode Exit fullscreen mode

2. Seed the registry 

python seed_db.py
Enter fullscreen mode Exit fullscreen mode

This creates pg_registry.db with sample entries. Edit SAMPLE_RECORDS in seed_db.py to point at your real PostgreSQL hosts.

3. Set credentials via environment variables 

# Windows PowerShell
$env:PG_API_USER   = "pgadmin"
$env:PG_API_PASS   = "Ch@ngeMe2024!"
$env:PG_ADMIN_USER = "role_create"
$env:PG_ADMIN_PASS = "your_pg_password"
Enter fullscreen mode Exit fullscreen mode 
# Linux / macOS
export PG_API_USER="pgadmin"
export PG_API_PASS="Ch@ngeMe2024!"
export PG_ADMIN_USER="role_create"
export PG_ADMIN_PASS="your_pg_password"
Enter fullscreen mode Exit fullscreen mode

4. Start 

python app.py
# Listening on http://localhost:5000
Enter fullscreen mode Exit fullscreen mode

Example Calls

Create a service account for a VM running on port 8080:

curl -u pgadmin:Ch@ngeMe2024! \
  "http://localhost:5000/api/v1/users/app?env=dev&database=myapp_dev&servername=web01&port=8080"
Enter fullscreen mode Exit fullscreen mode

Creates user web01_8080 with CONNECTION LIMIT 200.

Create a Kubernetes workload account:

curl -u pgadmin:Ch@ngeMe2024! \
  "http://localhost:5000/api/v1/users/app-k8s?env=dev&database=myapp_dev&env_prefix=dv&farmname=gearservice"
Enter fullscreen mode Exit fullscreen mode

Creates user dv_gearservice.

Reset a forgotten password:

curl -u pgadmin:Ch@ngeMe2024! \
  "http://localhost:5000/api/v1/users/reset?env=prod&database=myapp_prod&username=analyst01"
Enter fullscreen mode Exit fullscreen mode

Generates a new secrets.token_urlsafe(16) password and applies it immediately. Returns the new password in the response.

Idempotency

All create endpoints check pg_catalog.pg_roles before issuing CREATE USER. If the role already exists, the API returns "status": "user already exists" and exits cleanly. Safe to call from automation without worrying about duplicate creation errors.

Audit Log

Every operation (create, reset, search_path change) is written to a audit_log table in the same pg_registry.db SQLite file:

sqlite3 pg_registry.db \
  "SELECT * FROM audit_log ORDER BY performed_at DESC LIMIT 10;"
Enter fullscreen mode Exit fullscreen mode

You get a timestamped record of who called what, on which database, with what outcome. Useful for access reviews and incident investigations.

Notification Hooks

notifications.py ships with ready-to-uncomment stubs for Webex Teams, Slack, and email. Wire in your webhook URL or SMTP config, then call send_notification() from any endpoint to push alerts to your team when accounts are created or passwords reset.

Security Notes

  • Intended for internal / intranet use — put it behind a VPN or API gateway, not on the open internet.
  • For internet-facing deployments, swap HTTP Basic Auth for JWT or an API key header.
  • pg_registry.db contains your real hostnames — it's in .gitignore and should stay off version control.
  • The DBA password never appears in URLs, query strings, or logs.

What I'd Add Next

A few things on the roadmap:

  • GRANT/REVOKE endpoints — privilege management beyond account creation
  • Schema-level grants — GRANT SELECT ON ALL TABLES IN SCHEMA patterns
  • Token-based auth — drop-in replacement for Basic Auth
  • Docker packaging — docker run for teams that don't want to manage Python deps
  • Structured audit export — JSON or CSV export of the audit log for compliance workflows

Wrapping Up

If your team provisions PostgreSQL users more than a few times a month, wrapping it in an HTTP interface pays for itself quickly. The audit trail alone is worth it.

The full source is at github.com/pcraavi/PostgreSQL-user-creation-API. It's MIT-licensed — fork it, adapt the user types to your org's naming conventions, and wire in your notification channels.

Questions or suggestions? Drop them in the comments or open an issue on GitHub.

Top comments (2)

Collapse
 
gimi5555 profile image
Gilder Miller •

Great!
SQLite will serialize writes, so concurrent API calls won’t corrupt data, but they’ll queue and hit database is locked under load.
Flask + multiple workers makes this worse because each instance competes for the same file lock.
So at scale this design becomes a bottleneck, not just a minor delay.
Postgres or a queue-based worker model fits better for this kind of provisioning API.
What happens when 20 CI pipelines trigger user creation at the same time?

Collapse
 
pranay_raavi profile image
Pranay Ravi • • Edited

Great point — SQLite serialization is a bottleneck at scale. This is a lightweight starter version meant to get folks going; production deployments should back the registry and audit log with Postgres itself. The pattern holds, just swap the persistence layer.

In this case though, pg_registry.db is read-only at runtime; it's seeded once with your database inventory and rarely touched after that, may be every time new databases are created. The concurrent load only hits the actual PostgreSQL targets, not SQLite