postgres_skill

Overview

This skill configures DigitalOcean Managed Postgres for App Platform apps with production-ready defaults, security isolation, and convenient bindable variables. It supports two patterns: bindable variables for single-app databases and schema isolation for multi-tenant or multi-app clusters. The goal is secure, reproducible setup, user/permission management, and reliable connection configuration.

How this skill works

The skill automates cluster and user creation via doctl or helper scripts, injects bindable variables into App Platform service specs, and offers an alternate flow that creates isolated schemas and users for multi-tenant setups. It includes scripts to grant permissions, generate connection strings, audit schemas/users, and perform a hands-free schema setup that stores credentials in GitHub Secrets. It also documents common fixes for connectivity and permission errors.

When to use it

• Single app per database where a single DB user and database are sufficient (use Bindable Variables).
• Multi-tenant SaaS or multiple apps sharing one Postgres cluster where schema-level isolation is required (use Schema Isolation).
• When you need production defaults like SSL-required connection strings and managed CA certificates.
• When you need automated provisioning, permission grants, and secure secret delivery to CI/CD or GitHub Secrets.
• When troubleshooting connectivity, permission, or connection-pool limits on DigitalOcean Postgres.

Best practices

• Prefer Path A (Bindable Variables) for standard CRUD apps to keep operations simple and use App Platform bindables.
• For multi-tenant or shared clusters, use Schema Isolation: create a schema per tenant and a schema-scoped user. Store credentials in secrets, not logs.
• Always run permission-grant SQL as the cluster admin after creating users—DO-created users have no default schema access.
• Add ?sslmode=require and include CA cert content when building connection strings to enforce secure connections.
• Monitor connection counts and create connection pools if hitting limits; use DO’s managed pooling features or external poolers.

Example use cases

• Create a production Postgres cluster, a database, and a DO-managed user, then inject DATABASE_URL into App Platform via bindable variables.
• Configure a multi-tenant SaaS: run the secure_setup script to create tenant schemas and push credentials to GitHub Secrets for CI/CD.
• Audit existing clusters to list schemas and users, identify permission gaps, and run the grant-permissions SQL to fix permission-denied errors.
• Troubleshoot an App Platform deployment that fails DB migrations due to search_path or relation-not-found errors by checking schema qualifications and mapping ORM config.
• Resolve SSL or connection-limit issues by regenerating connection strings with sslmode and enabling a connection pool through doctl.