Secure Managed Postgres with Private Networking, TLS, and Roles

TL;DR — Lock down a managed Postgres instance in three layers: remove public access in favor of private networking, force clients to use TLS with certificate verification, and stop using the admin user from applications by creating separate login and runtime roles with only the privileges they need. The most common fix is replacing a public hostname in your app with the provider's private endpoint and adding sslmode=verify-full plus the provider CA certificate. Reading time: ~5 min

Goal

When you are done, your application connects to the managed PostgreSQL instance only over private network paths, plaintext/non-verified TLS connections are rejected by policy or client settings, and each app or job uses a dedicated database role that has only the minimum schema/table privileges it needs.

Prerequisites

• Access to your managed Postgres provider dashboard with permission to change networking and database settings
• Access to the VPC/VNet/project network where your app runs
• psql client installed; PostgreSQL client 15+ preferred — check with:

psql --version

• OpenSSL installed — check with:

openssl version

• A shell on the app host, bastion, or CI runner that should be allowed to connect
• The managed Postgres admin username/password or temporary admin token
• The provider's private database endpoint/hostname and CA certificate download URL or PEM file
• The database name, application schema name, and the exact app operations needed: read-only, read/write, migrations, background jobs

Steps

Step 1: Disable public ingress and attach the database to private networking

⚠️ If your application still uses the public database hostname, disabling public access will break connectivity immediately. Update one non-production client to the private endpoint first, then cut over production.

In your provider dashboard, open the managed Postgres instance and set these exact values in the networking section your provider exposes:

• Public access / Public endpoint / Internet access: Disabled
• Private networking / VPC attachment / Private endpoint: Enabled
• Allowed source networks / Security group / Firewall rules: add only the app subnet CIDR and bastion subnet CIDR, for example 10.20.0.0/24 and 10.20.1.0/24
• Port: 5432

If your provider requires CLI/API for firewall rules, the literal value to allow is TCP 5432 from the app subnet only.

From an allowed host, test that the private hostname resolves and the TCP port opens:

export PGHOST="db-private.example.internal"
getent hosts "$PGHOST"
nc -vz "$PGHOST" 5432

You should see the private IP and a successful TCP connect, shaped like:

10.20.3.14      db-private.example.internal
Connection to db-private.example.internal (10.20.3.14) 5432 port [tcp/postgresql] succeeded!

Step 2: Download the provider CA certificate and require verified TLS from clients

Save the provider CA certificate locally on the app host or in your secret store mount path:

install -d -m 0755 /etc/postgres
curl -fsSL "https://provider.example.com/certs/ca.pem" -o /etc/postgres/provider-ca.pem
chmod 0644 /etc/postgres/provider-ca.pem
openssl x509 -in /etc/postgres/provider-ca.pem -noout -subject -issuer -dates

Then test a verified TLS connection with psql:

export PGHOST="db-private.example.internal"
export PGPORT="5432"
export PGDATABASE="appdb"
export PGUSER="admin_user"
export PGPASSWORD='REPLACE_WITH_ADMIN_PASSWORD'
export PGSSLMODE='verify-full'
export PGSSLROOTCERT='/etc/postgres/provider-ca.pem'
psql -c 'select version(), current_user, inet_server_addr();'

You should get one row back. If hostname verification fails, psql exits non-zero with output shaped like:

psql: error: connection to server at "db-private.example.internal" (10.20.3.14), port 5432 failed: server certificate for "*.provider-db.example.com" does not match host name "db-private.example.internal"

If that happens, use the exact hostname covered by the certificate, usually the provider-issued private DNS name, not a custom CNAME.

Step 3: Turn on server-side TLS enforcement in the provider settings

In your provider dashboard, open the database settings/security section and set these exact values if available:

• Require TLS / Enforce SSL: Enabled
• Minimum TLS version: TLS 1.2 or TLS 1.3 if all clients support it
• Client certificate auth: Disabled unless you already operate client cert issuance

If your provider exposes PostgreSQL flags instead of a toggle, set:

rds.force_ssl = 1

or the provider equivalent for "require SSL".

After applying, verify that a non-TLS attempt fails:

PGSSLMODE=disable psql "host=$PGHOST port=5432 dbname=$PGDATABASE user=$PGUSER password=$PGPASSWORD" -c 'select 1;'

You should see a failure shaped like one of these:

psql: error: connection to server at "db-private.example.internal" (10.20.3.14), port 5432 failed: FATAL:  SSL connection is required. Please specify SSL options and retry.

psql: error: connection to server at "db-private.example.internal" (10.20.3.14), port 5432 failed: server does not allow insecure connections

Step 4: Create separate roles for app runtime, read-only access, and migrations

Connect as the admin user and create roles with exact privileges. Replace appdb and app if your names differ.

CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_runtime LOGIN PASSWORD 'REPLACE_WITH_LONG_RANDOM_SECRET' NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE app_readonly LOGIN PASSWORD 'REPLACE_WITH_LONG_RANDOM_SECRET' NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE app_migrator LOGIN PASSWORD 'REPLACE_WITH_LONG_RANDOM_SECRET' NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION;

GRANT CONNECT ON DATABASE appdb TO app_runtime, app_readonly, app_migrator;
\c appdb

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_runtime, app_readonly, app_migrator;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_runtime;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO app_runtime;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;

GRANT CREATE, USAGE ON SCHEMA public TO app_migrator;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_migrator;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO app_migrator;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO app_runtime;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO app_migrator;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO app_migrator;

Run it with:

psql "host=$PGHOST port=5432 dbname=postgres user=$PGUSER password=$PGPASSWORD sslmode=verify-full sslrootcert=/etc/postgres/provider-ca.pem" -f roles.sql

You should see CREATE ROLE, GRANT, and ALTER DEFAULT PRIVILEGES lines with no ERROR: lines.

Step 5: Point the application at the private endpoint and least-privilege user

Set the application connection string to the private hostname and runtime role. Exact URI shape:

export DATABASE_URL='postgresql://app_runtime:REPLACE_WITH_LONG_RANDOM_SECRET@db-private.example.internal:5432/appdb?sslmode=verify-full&sslrootcert=/etc/postgres/provider-ca.pem'

For migration jobs, use a separate secret:

export MIGRATION_DATABASE_URL='postgresql://app_migrator:REPLACE_WITH_LONG_RANDOM_SECRET@db-private.example.internal:5432/appdb?sslmode=verify-full&sslrootcert=/etc/postgres/provider-ca.pem'

Success means the app starts normally and no longer needs the admin credential anywhere outside break-glass storage.

Verify it works

Run these checks from the app host.

1. Confirm the app user is not admin and TLS is active:

psql "$DATABASE_URL" -c 'select current_user, session_user, ssl from pg_stat_ssl where pid = pg_backend_pid();'

Expected shape:

 current_user | session_user | ssl
--------------+--------------+-----
 app_runtime  | app_runtime  | t
(1 row)

1. Confirm the connection uses a private IP path:

psql "$DATABASE_URL" -c 'select inet_server_addr(), inet_client_addr();'

Expected: RFC1918 addresses such as 10.x.x.x, 172.16-31.x.x, or 192.168.x.x.

1. Confirm least privilege actually blocks dangerous operations:

psql "$DATABASE_URL" -c 'create table should_fail(id int);'

Expected failure:

ERROR:  permission denied for schema public

1. Confirm migrations user can perform DDL:

psql "$MIGRATION_DATABASE_URL" -c 'create table if not exists migration_probe(id int primary key); drop table migration_probe;'

Expected: CREATE TABLE then DROP TABLE.

Common pitfalls

Using a custom DNS name that does not match the server certificate

Mistake: pointing clients at postgres.internal.mycorp when the certificate is issued to a provider hostname.

Symptom:

psql: error: server certificate for "*.provider-db.example.com" does not match host name "postgres.internal.mycorp"

Fix: use the exact provider hostname covered by the cert, or issue a certificate that matches your custom name if your provider supports it.

Disabling public access before the app is on the private network

Mistake: turning off the public endpoint while the app still runs outside the VPC/VNet or still uses the public hostname.

Symptom:

psql: error: connection to server at "db-public.example.com" (...) failed: Connection timed out

Fix: first move the app, runner, or bastion onto the private network and update DATABASE_URL to the private endpoint.

Forgetting ALTER DEFAULT PRIVILEGES

Mistake: granting access on existing tables only.

Symptom: the app works until a migration creates a new table, then queries fail with:

ERROR:  permission denied for table new_table

Fix: run the ALTER DEFAULT PRIVILEGES statements in Step 4 as the role that owns future objects.

Reusing the admin user in the app because it "just works"

Mistake: shipping the provider admin credential in app secrets.

Symptom: accidental schema changes, dropped objects, or broad blast radius during SQL injection or secret leakage.

Fix: rotate the admin password, remove it from app config, and use app_runtime for the app plus app_migrator only in migration jobs.

Setting sslmode=require instead of verify-full

Mistake: encrypting traffic but skipping certificate hostname validation.

Symptom: connections succeed even if DNS points to the wrong server; MITM protection is weaker.

Fix: set sslmode=verify-full and provide sslrootcert=/etc/postgres/provider-ca.pem in the connection string.

This article was written by an AI system and published pending human review. Verify anything you intend to act on.