Managed PostgreSQL access
How managed Postgres databases are accessed, how roles and connection strings work, and what to check about backups before you need them.
- Difficulty
- Intermediate
PostgreSQL is the database behind most custom applications — where the actual records live. "Managed" means a provider handles the server, patching, and backups, and hands you a connection string.
Quick summary
Access is a connection string containing a username, password, host, and database name. Treat it as a secret of the highest order — it is direct access to all your data. Give each application its own database role with only the permissions it needs, and verify that backups exist and can actually be restored.
The connection string
A single URL carrying everything needed to connect:
postgresql://username:password@host:5432/databasenameThis one string is total access
Anyone holding it can read, modify, or delete every record. It belongs in environment variables — never in source code, a shared document, or a chat message. When someone with access leaves, rotate the password.
Most providers offer both an internal string (reachable only from services in the same private network — faster and safer) and an external one (reachable from anywhere). Prefer internal for application traffic, and restrict external access by IP where the provider supports it.
Roles and least privilege
Postgres manages access through roles. One role can be a single login, a group of logins, or both — Postgres stopped treating users and groups as separate things a long time ago. Sensible separation:
| Role | Permissions | Used by |
|---|---|---|
| Owner / admin | Full control, schema changes | Migrations, administration |
| Application | Read and write data, no schema changes | The running app |
| Read-only | SELECT only | Reporting, analytics, debugging |
Handing every consumer the admin role is common and unnecessary. A reporting dashboard with a read-only role cannot cause an outage.
Backups
The part everyone assumes is handled.
Confirm automatic backups are enabled and note the retention window. Free and entry-level tiers sometimes have none.
Understand point-in-time recovery. Higher tiers can restore to a specific moment, which matters enormously if a bad migration ran at 14:32.
Take periodic dumps you control, stored outside the provider — for example in S3. Provider backups vanish with the provider account.
Test a restore. Restore into a scratch database and confirm the data is intact. An untested backup is an assumption.
Deleting a managed database is usually permanent
Provider consoles make deletion a couple of clicks, and free tiers can expire automatically after a fixed period. Take and download a dump before any destructive action, and before any plan change or migration.
Migrations
Schema changes are applied through migrations — versioned scripts checked into source control. Two rules prevent most incidents: back up immediately before running one in production, and test it against a copy of production-shaped data first. A migration that works on an empty development database can lock a large table for minutes on a real one.
Common questions
Can we get a copy of our data?
Yes, always. A standard pg_dump produces a portable file that restores
into another PostgreSQL server running the same major version or a newer
one. (Going backwards to an older major version is the one direction
PostgreSQL doesn't guarantee.) Your data is not locked into a provider, and
you're entitled to a copy at any time — ask and we'll produce one.
Should staging use the production database?
No. Test code pointed at live data eventually deletes something real, or emails real customers. Staging should use its own database, seeded with anonymised data if realistic volume is needed.
Why is the application suddenly slow?
Common causes: a missing index on a table that has grown, connection-pool exhaustion, or a query that scans the whole table. Managed providers expose slow-query statistics — that's the place to start rather than guessing.
How do we rotate the database password safely?
Add a second role with the same permissions, point every application and service at it, redeploy them, confirm nothing is still connecting as the original role, then remove the original. This is the sequence Render documents as a zero-downtime rotation, because the two credentials overlap. Changing the password in place gives you no overlap: anything still holding the old one fails the next time it connects.
Related guides
Need a hand?
Learn more
Last updated
ClamAV & scanning uploaded files
What ClamAV does, why applications that accept file uploads need malware scanning, and how it fits into a wider upload-security approach.
Managed Redis access
What Redis is used for in an application, how access and connection strings work, and why it should never be your only copy of anything.