Using psql to connect to PostgreSQL in SSL mode

Question

I am trying to configure ssl certificate for PostgreSQL server. I have created a certificate file (server.crt) and key (server.key) in data directory and update the parameter SSL to "on" to enable secure connection.

I just want only the server to be authenticated with server certificates on the client side and don't require the authenticity of client at server side. I am using psql as a client to connect and execute the commands.

I am using PostgreSQL 8.4 and Linux. I tried with the below command to connect to server with SSL enabled

       psql "postgresql://localhost:2345/postgres?sslmode=require"

but I am getting

       psql: invalid connection option "postgresql://localhost:2345/postgres?sslmode"

What am doing wrong here? Is the way I am trying to connect to server with SSL mode enabled is correct? Is it fine to authenticate only server and not the client ?

Solution 1

psql below 9.2 does not accept this URL-like syntax for options.

The use of SSL can be driven by the sslmode=value option on the command line or the PGSSLMODE environment variable, but the default being prefer, SSL connections will be tried first automatically without specifying anything.

Example with a conninfo string (updated for psql 8.4)

psql "sslmode=require host=localhost dbname=test"

Read the manual page for more options.

Solution 2

psql --set=sslmode=require -h localhost -p 2345 -U thirunas \
-d postgres -f test_schema.ddl

Another Example for securely connecting to Azure's managed Postgres database:

psql --file=product_data.sql --host=hostname.postgres.database.azure.com --port=5432 \
--username=postgres@postgres-esprit --dbname=product_data \
--set=sslmode=verify-full --set=sslrootcert=/opt/ssl/BaltimoreCyberTrustRoot.crt.pem

Solution 3

Found the following options useful to provide all the files for a self signed postgres instance

psql "host={hostname} sslmode=prefer sslrootcert={ca-cert.pem} sslcert={client-cert.pem} sslkey={client-key.pem} port={port} user={user} dbname={db}"

Solution 4

On psql client v12, I could not find option in psql client to activate sslmode=verify-full.

I ended up using environment variables :

PGSSLMODE=verify-full PGSSLROOTCERT=server-ca.pem psql -h your_host -U your_user -W -d your_db

Solution 5

Well, you cloud provide all the information with following command in CLI, if connection requires in SSL mode:

psql "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=your_host port=5432 user=your_user dbname=your_db" 

View more solutions

Comments

[Lolly]

I got this connection string syntax from this link. postgresql.org/docs/9.2/static/app-psql.html

[Lolly]

I tried with your option too psql -h localhost -p 2345 -U thirunas -d postgres "sslmode=require" -f test_schema.ddl but it says sql: warning: extra command-line argument "sslmode=require" ignored

[Bruno]

@annonymous you're saying you got the syntax for the 9.2 documentation, yet you're also saying you're using version 8.4. What you're using isn't referenced in the 8.4 doc. Try to put "sslmode=require" as the first argument too.

[Lolly]

@Bruno: I got the mistake. Just noticed the version difference in documentation. But still having it in first argument also, I get the same warning. psql -h localhost "sslmode=require" -p 2345 -U thirunas -d postgres -f test_schema.ddl. Warning psql: warning: extra command-line argument "sslmode=require" ignored

[Daniel Vérité]

@annonymous: answer updated to use only a conninfo string with a syntax accepted by psql 8.4

[Craig Ringer]

I wasn't aware that psql now supported JDBC-style URLs. Awesome.

[Jorn]

I'm getting psql: FATAL: connection requires a valid client certificate. I know where my certificates are, but how do I specify that location to psql?

[Daniel Vérité]

@Jorn: see PGSSLCERT and PGSSLKEY environment variables

[jla]

@Wave & others who run into this, the conninfo string is used instead of a database name (-d). You can give the database name after the -d option, or as the first non-option argument on the command line. So -d postgres "sslmode=require" should be either psql [options] -d "dbname=postgres sslmode=require" [other options] or psql [options] "dbname=postgres sslmode=require". You can move many other options into the the conninfo string.

[Austin A]

I thought this too initially but after rereading the Connecting to a Database section, I take An alternative way to specify connection parameters is in a conninfo string, which is used instead of a database name. This mechanism give you very wide control over the connection. as meaning you can shove whatever arguments in the conninfo string. But I also think the docs could be made more clear here as well.

[Daniel Vérité]

This is incorrect. --set=sslmode=require defines a psql variable that is not involved at all in the authentication process. It does nothing to force SSL.

[Paweł Prażak]

for TLS auth you'll also need: PGSSLCERT and PGSSLKEY and drop the -W

[LEDfan]

I can confirm this does not work as pointed out by @DanielVérité. Test with a postgres server having an untrusted cert and using --set=sslmode=verify-full will not complain.

[Elouan Keryell-Even]

This is Google Cloud's recommended method, and I can confirm it works. Source: cloud.google.com/sql/docs/postgres/connect-admin-ip#connect-‌​ssl

[Sabuhi Shukurov]

yeah, it works, at that time Google did not have this recommendation, they stoled it from me :D just joking))

[Elouan Keryell-Even]

you the real MVP, Google plz give a cookie to this man! ❤

[questionto42standswithUkraine]

The -W avoids one unused connection attempt. See the docs and search for -W: -W = --password ... psql will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

[questionto42standswithUkraine]

I can confirm that on Ubuntu, with psql 13.4, I get psql: warning: extra command-line argument "sslrootcert=ca.pem" ignored and psql: warning: extra command-line argument "sslmode=verify-full" ignored. Another thing: The loaded certificate holds for some minutes, you do not need it again and again. But after a longer time, I had to load the pem file again, else I would get the error: psql: error: FATAL: password authentication failed for user "USER" FATAL: no pg_hba.conf entry for host "11.222.33.444", user "USER", database "db", SSL off. With the env vars, this error was avoided.