Listed on

PostgreSQL CLI utility with SSL

The Postgres CLI utility can sometimes prove a little painful to use when trying to connect to a server. In this case the sticking point was require SSL on the server.
The solution was to use a URI for the connection string:

psql 'postgresql://user%40domain:mypasswd@svr.example.com:5432/postgres?sslmode=require&dbname=somedb'

This example shows a number of interesting things:
  • a username with an embedded 'at' symbol;
  • a hardcoded password - you can force a prompt for a password with the -W;
  • a port address (5432 is the default);
  • forcing sslmode to required; and,
  • selecting a database
Many of these things can also be done via environment variables, specific command line options or configuration files.