psql Auth: PGPASSWORD, .pgpass, and Service URIs
Set PGPASSWORD or a .pgpass file so psql authenticates without prompting and hanging a CI job.
psql prompts for a password by default, which deadlocks a non-interactive pipeline. The three ways to supply it are the PGPASSWORD environment variable, a .pgpass file, or a password embedded in a connection URI.
What it does
psql resolves the password in a fixed order: an explicit URI or PGPASSWORD, then a .pgpass file, then an interactive prompt. Supplying it through the environment or .pgpass keeps the secret off the command line where ps could read it.
Common usage
# environment variable (simplest in CI)
export PGPASSWORD=secret
psql -h db -U app -d appdb -c "SELECT 1"
# .pgpass: hostname:port:database:username:password (chmod 600)
echo "db:5432:appdb:app:secret" > ~/.pgpass && chmod 600 ~/.pgpass
psql -h db -U app -d appdb -c "SELECT 1"
# URI with embedded credentials
psql "postgresql://app:secret@db:5432/appdb" -c "SELECT 1"Options
| Mechanism | What it does |
|---|---|
| PGPASSWORD | Environment variable read by libpq |
| ~/.pgpass | File of host:port:db:user:password lines (chmod 600) |
| PGHOST / PGUSER / PGDATABASE | Default connection parameters via env |
| postgresql://user:pass@host/db | Full connection URI |
| PGSSLMODE | Control TLS: disable, require, verify-full |
In CI
Put the password in a masked secret and export it as PGPASSWORD; do not put it in the visible command. If a .pgpass file is not chmod 600, libpq ignores it and falls back to prompting, which hangs the job. Managed runners inject secrets as environment variables, which maps cleanly onto PGPASSWORD.
Common errors in CI
"FATAL: password authentication failed for user \"app\"" means the wrong password, or the URI user does not match -U. "fe_sendauth: no password supplied" means no password source was found and the terminal is non-interactive. "WARNING: password file \"/root/.pgpass\" has group or world access" means the file was not chmod 600, so psql ignored it.