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

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:

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

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"
}

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

2. Seed the registry

python seed_db.py

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"

# 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"

4. Start

python app.py
# Listening on http://localhost:5000

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"

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"

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"

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;"

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.