Connecting to PostgreSQL

CardNexus dev guide — IAM auth via SSO, DataGrip + psql.

CardNexus’ Aurora PostgreSQL clusters use AWS IAM Database Authentication for humans. You connect with your existing SSO identity — there are no shared passwords to leak, rotate, or paste into a chat. Tokens last 15 minutes and are refreshed automatically by your client.

This guide covers DataGrip, psql, and the underlying mechanics. If your tool isn’t here but speaks the Postgres protocol, the AWS-side steps still apply.

Overview

tl;dr

1. Set up an AWS SSO profile pointed at the account + permission set you want.
2. aws sso login --profile <name>.
3. Connect VPN.
4. In your client: host = postgres.<env>.internal.cardnexus.com, user = the PG role name (db_admin / db_readwrite / db_readonly) — not your email. AWS-IAM auth, region eu-west-1, SSL on (no-verify mode).

Prerequisites

• AWS CLI v2 — brew install awscli or equivalent.
• VPN connected — postgres lives in private subnets; the hostname only resolves and routes from inside the workload VPC or via Client VPN. See the VPN guide.
• A SSO permission set — granted to you by ops. Determines which PG role(s) you can assume.
• A Postgres client — DataGrip (2024.x+ has native AWS IAM auth) or psql.

SSO profile setup (one-time)

aws configure sso --profile dev-admin
# SSO start URL  : https://cardnexus.awsapps.com/start
# SSO region     : eu-west-1
# Pick the account: 583323753955 (dev)  or  848474831621 (prod)
# Pick the permission set you've been granted (e.g. InfraAdmin, LimitedReadWrite, Readonly)
# Default region : eu-west-1
# Default output : json

[profile dev-admin]
sso_start_url = https://cardnexus.awsapps.com/start
sso_region = eu-west-1
sso_account_id = 583323753955
sso_role_name = InfraAdmin
region = eu-west-1
output = json

Logging in

aws sso login --profile dev-admin

Opens a browser, you complete SSO once, the CLI caches the session in ~/.aws/sso/cache/. Session length depends on the permission set:

Sanity check:

aws sts get-caller-identity --profile dev-admin
# expect: an ARN like arn:aws:sts::583323753955:assumed-role/AWSReservedSSO_InfraAdmin_xxxx/you@cardnexus.com

When the session expires you’ll start seeing ExpiredToken errors. Just re-run aws sso login.

Which PG role do I use?

The username you put in your client is the Postgres role name, not your email. AWS IAM controls which roles you can assume; once you’ve assumed one, Postgres sees you as that role.

What each role can do at the Postgres level:

Audit trail: every rds:GenerateDbAuthToken call is logged in CloudTrail with your SSO identity, even though the PG-wire identity is the shared role name. So select current_user; returns db_readonly for everyone in the readonly group, but CloudTrail can answer “who got a token for db_readonly at 14:32?”.

DataGrip

Tested on DataGrip 2024.x+. Older versions may not have the native AWS auth dropdown — fall back to the psql one-shot token method below.

select current_user, current_database(), version();
-- db_admin | cardnexus | PostgreSQL 17.7 ... aarch64

psql

PGPASSWORD=$(aws rds generate-db-auth-token \
  --hostname postgres.dev.internal.cardnexus.com \
  --port 5432 \
  --username db_admin \
  --region eu-west-1 \
  --profile dev-admin) \
psql "host=postgres.dev.internal.cardnexus.com \
      port=5432 \
      dbname=cardnexus \
      user=db_admin \
      sslmode=no-verify"

pgconnect() {
  # usage: pgconnect <env> <pg-role> <aws-profile>
  # e.g.   pgconnect dev db_readonly dev-admin
  local env=$1 user=$2 profile=$3
  local host="postgres.${env}.internal.cardnexus.com"
  PGPASSWORD=$(aws rds generate-db-auth-token \
    --hostname "$host" --port 5432 --username "$user" \
    --region eu-west-1 --profile "$profile") \
  psql "host=$host port=5432 dbname=cardnexus user=$user sslmode=no-verify"
}

Token’s good for 15 min — if you idle longer than that and the connection drops, reconnect.

Service accounts (app_user)

Backend-api, public-api, and Trigger.dev connect as app_user via a static password (TD can’t IAM-auth from outside AWS). You usually shouldn’t use this — IAM auth is cleaner — but if you need it for a migration script or one-off ETL:

aws secretsmanager get-secret-value \
  --secret-id dev/postgres/app-user-credentials \
  --region eu-west-1 \
  --profile dev-admin \
  --query SecretString --output text | jq -r .url
# prints the full POSTGRES_URI: postgresql://app_user:...@postgres.dev.internal.cardnexus.com:5432/cardnexus?sslmode=no-verify

SSL: why no-verify?

sslmode=require in libpq just means “encrypt the connection”. Most node-postgres setups, however, treat require as “encrypt + verify the cert” — and the Amazon RDS CA bundle isn’t in the default Node trust store. The connection fails the verification step.

sslmode=no-verify (a node-postgres extension that libpq also accepts in many wrappers) keeps the channel encrypted but skips cert validation. Acceptable here because:

• The cluster has publicly_accessible = false and lives in a private subnet.
• The Aurora SG only admits VPC CIDR + the shared VPC (which the VPN SNATs to).
• IAM auth is happening over a network you control — we trust the routing, not the cert chain.

If you want full verify: download the global RDS CA bundle , import it into your client’s trust store, and switch to sslmode=verify-full.

Connection details cheat sheet

Always port = 5432, database = cardnexus. The reader hostname is for queries that don’t need write capability — useful for analytics, but in dev it resolves to the same instance as the writer (single-instance cluster).

Troubleshooting

password authentication failed for user "<your-email>"

You put your SSO email in the User field. Use the PG role name (db_admin / db_readwrite / db_readonly) instead. AWS IAM does the identity mapping; Postgres just sees the role.

password authentication failed for user "db_admin" (or another role)

One of:

• SSO session expired → aws sso login --profile <name>.
• Token expired (15 min) → reconnect; DataGrip refreshes automatically, psql needs a new token.
• Wrong AWS region in the token request — must be eu-west-1.
• SSL not enabled in the client — Aurora rejects IAM auth without SSL.
• rds_iam grant missing on the role — should never happen, but flag in #infra if so.

FATAL: no pg_hba.conf entry for host "..." user "..." SSL off

SSL not configured properly. Verify the SSL tab in DataGrip / the sslmode= param in psql.

connection refused / timeout

Not on VPN, or the VPN doesn’t route to the workload VPC. Check dig +short postgres.dev.internal.cardnexus.com — should return a 10.50.x.x (dev) or 10.52.x.x (prod) address.

AccessDenied: ... is not authorized to perform: rds-db:connect

Your SSO permission set’s IAM policy doesn’t allow connecting as that PG user. Check the table at the top of this guide — e.g. Readonly can only connect as db_readonly, not db_admin. If you’re sure your group should have access and it doesn’t, ping #infra.

ExpiredToken: The security token included in the request is expired

Your SSO session expired. aws sso login --profile <name> and try again.

What’s happening under the hood

Token validity: 15 minutes from generation. DataGrip refreshes transparently while the connection is open. The SSO session above (4-8 h) is what’s actually backing all of this — when it expires, no more tokens.