Tools: Complete Guide to Stop Running psql Commands by Hand — Build a REST API for PostgreSQL User Management
What I Built
The Problem It Solves
Architecture
The Endpoints
Running It
1. Clone and install
2. Seed the registry
3. Set credentials via environment variables
4. Start
Example Calls
Idempotency
Audit Log
Notification Hooks
Security Notes
What I'd Add Next
Wrapping Up If you manage PostgreSQL databases across multiple environments, you've probably done this: It's tedious, error-prone, and leaves zero audit trail. Here's a better way. 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 In teams that span dev, QA, UAT, and prod, you end up with different patterns of users: 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. The project is intentionally small — five Python files and a requirements list: Two credential pairs, clearly separated: 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. All endpoints are GET with query params (by design — simple to curl, simple to call from automation scripts). Every user-creation endpoint returns the same structured response: Password is returned once at creation time. The API uses secrets.token_urlsafe(16) for generation — no insecure random module. This creates pg_registry.db with sample entries. Edit SAMPLE_RECORDS in seed_db.py to point at your real PostgreSQL hosts. Create a service account for a VM running on port 8080: Creates user web01_8080 with CONNECTION LIMIT 200. Create a Kubernetes workload account: Creates user dv_gearservice. Reset a forgotten password: Generates a new secrets.token_urlsafe(16) password and applies it immediately. Returns the new password in the response. 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. Every operation (create, reset, search_path change) is written to a audit_log table in the same pg_registry.db SQLite file: You get a timestamped record of who called what, on which database, with what outcome. Useful for access reviews and incident investigations. 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. A few things on the roadmap: 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. Templates let you quickly answer FAQs or store snippets for re-use. 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? 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 Hide child comments as well For further actions, you may consider blocking this person and/or reporting abuse
