Connection string builder

PostgreSQL supports two native connection string formats defined by libpq. The URI format follows RFC 3986: postgresql://user@host:port/dbname?param=value. The keyword/value format uses space-separated key=value pairs: host=... port=... dbname=... user=.... Both are functionally identical and support the same parameters — any parameter you can set in one format works in the other. The URI format is more common in modern applications, environment variables (like DATABASE_URL), and cloud platform dashboards because it fits in a single string that is easy to copy and paste between configuration systems. The keyword/value format is used by psql, pg_dump, pg_restore, and other PostgreSQL command-line tools — you can pass it directly as the connection argument. One important difference: in URI format, special characters in values (especially passwords) must be percent-encoded (@ becomes %40, / becomes %2F), while keyword/value format uses single-quote escaping (password='my pass'). If your password contains @, #, /, %, or spaces, the keyword/value format is less error-prone. There is also a third variant worth knowing: the postgres:// scheme (without the ql suffix) is accepted as an alias by libpq and most drivers, though postgresql:// is the officially documented form. Most modern drivers accept both URI and keyword/value formats interchangeably.

Never embed passwords directly in connection strings, source code, configuration files checked into version control, or Docker build arguments. PostgreSQL and libpq provide several secure alternatives. The PGPASSWORD environment variable is the simplest — set it in your deployment environment (Kubernetes secret, cloud provider secrets manager, CI/CD variable) and libpq-based drivers will pick it up automatically. The .pgpass file (~/.pgpass on Unix, %APPDATA%\postgresql\pgpass.conf on Windows) stores credentials in hostname:port:database:username:password format and must have 0600 permissions. For cloud deployments, prefer IAM-based authentication: AWS RDS IAM authentication generates short-lived tokens via aws rds generate-db-auth-token, GCP Cloud SQL uses the Cloud SQL Auth Proxy, and Azure Database for PostgreSQL supports Azure AD tokens. For Kubernetes, mount passwords as secrets into environment variables rather than config maps. If you absolutely must pass a password in a connection string (some ORMs require it), read it from the environment at runtime: postgresql://user:${PGPASSWORD}@host/db — but be aware that the expanded string may appear in process listings or crash dumps.

PostgreSQL defines six SSL modes in order of increasing security. disable: no SSL at all, traffic is plaintext — only appropriate for trusted private networks or Unix sockets. allow: try non-SSL first, fall back to SSL if the server requires it — rarely useful, gives no security guarantee. prefer (the default): try SSL first, fall back to plaintext if the server does not support it — this is dangerously misleading because a man-in-the-middle can downgrade the connection silently. require: always use SSL, but do not verify the server certificate — encrypts traffic but does not prevent MITM attacks. verify-ca: use SSL and verify the server certificate is signed by a trusted CA — prevents connecting to a rogue server, but does not check that the certificate matches the hostname. verify-full: use SSL, verify the CA, and verify the hostname in the certificate matches the connection target — the strongest option and the one you should use in production. For most production deployments, use verify-full with sslrootcert pointing to your CA certificate. If you are connecting to a managed service (RDS, Cloud SQL, Azure) download their CA bundle. For local development, disable or require is usually fine.

Connection poolers sit between your application and PostgreSQL, and they impose constraints on what your connection string and application can do. PgBouncer in transaction pooling mode (the most common production configuration) does not support prepared statements across transactions, SET commands that persist, LISTEN/NOTIFY, advisory locks, or temporary tables. If your driver uses implicit prepared statements (like pgx in Go or psycopg3 in pipeline mode), you need to disable them: in psycopg3, set prepare_threshold=0; in pgx, use default_query_exec_mode=simple_protocol; in node-pg, disable prepared statements via { statement_timeout: 0 } or use the pg-native binding. Your connection string for PgBouncer targets the bouncer port (typically 6432, not 5432) and the virtual database name configured in pgbouncer.ini. Pgpool-II is more transparent for most operations but has its own quirks with load balancing — add target_session_attrs=read-write in your connection string if you need to ensure writes go to the primary. Both poolers benefit from application_name being set so you can identify connections in pgbouncer show clients or pgpool show pool_nodes. Keep connect_timeout short (5-10s) because the pooler adds latency on top of the actual PostgreSQL connection time.