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 ?

If you want the connection to be secure against MITM attacks, use `sslmode=verify-full`, not just `require`. — Bruno, Dec 24 '12 at 14:40

Daniel Vérité · Accepted Answer · 2012-12-24T15:08:54.213

154

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.

edited Dec 24 '12 at 15:08

answered Dec 24 '12 at 14:17

Daniel Vérité

58,074
15
129
156

I got this connection string syntax from this link. http://www.postgresql.org/docs/9.2/static/app-psql.html – Lolly Dec 24 '12 at 14:26
5

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` – Lolly Dec 24 '12 at 14:33
@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. – Bruno Dec 24 '12 at 14:38
2

@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` – Lolly Dec 24 '12 at 14:52
@annonymous: answer updated to use only a conninfo string with a syntax accepted by psql 8.4 – Daniel Vérité Dec 24 '12 at 15:10
I wasn't aware that psql now supported JDBC-style URLs. Awesome. – Craig Ringer Dec 25 '12 at 04:03
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`? – Jorn Jan 30 '15 at 11:16
@Jorn: see PGSSLCERT and PGSSLKEY environment variables – Daniel Vérité Jan 30 '15 at 13:56
8

@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. – jla Mar 13 '15 at 21:41

score 36 · Answer 2 · edited Oct 29 '19 at 13:39

36

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

edited Oct 29 '19 at 13:39

Philipp Schwarz

18,050
5
32
36

answered May 13 '16 at 15:07

Andrii Batiuk

473
4
3

14

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. – Daniel Vérité Jun 02 '20 at 15:42
4

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. – LEDfan Mar 24 '21 at 15:39

score 12 · Answer 3 · edited Nov 04 '22 at 04:22

12

Well, you could provide all the information with the following command in CLI, if a 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"

edited Nov 04 '22 at 04:22

jayaprakash R

152
3
13

answered Jan 03 '20 at 18:45

Sabuhi Shukurov

1,616
16
17

This is Google Cloud's recommended method, and I can confirm it works. Source: https://cloud.google.com/sql/docs/postgres/connect-admin-ip#connect-ssl – Elouan Keryell-Even Jun 10 '21 at 12:18
1

yeah, it works, at that time Google did not have this recommendation, they stoled it from me :D just joking)) – Sabuhi Shukurov Jun 10 '21 at 17:10
you the real MVP, Google plz give a cookie to this man! ❤ – Elouan Keryell-Even Jun 11 '21 at 08:43
I had to use 'host' instead of 'hostaddr' but otherwise worked like a charm. Should IMHO be the accepted answer. – Tobias Gierke May 16 '23 at 05:50

score 10 · Answer 4 · answered Oct 03 '19 at 08:11

10

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}"

answered Oct 03 '19 at 08:11

Julian Chick

111
1
3

score 10 · Answer 5 · answered Jul 08 '20 at 09:20

10

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

answered Jul 08 '20 at 09:20

FredG

712
7
10

1

for TLS auth you'll also need: `PGSSLCERT` and `PGSSLKEY` and drop the `-W` – Paweł Prażak Dec 09 '20 at 15:41
The `-W` avoids one unused connection attempt. See the [docs](https://www.postgresql.org/docs/13/app-psql.html) 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. ` – questionto42 Sep 06 '21 at 11:50
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. – questionto42 Sep 06 '21 at 15:42

Komu · Answer 6 · 2016-03-10T11:43:49.247

3

psql "sslmode=require host=localhost port=2345 dbname=postgres" --username=some_user

According to the postgres psql documentation, only the connection parameters should go in the conninfo string(that's why in our example, --username is not inside that string)

edited Mar 10 '16 at 11:43

answered Mar 10 '16 at 11:34

Komu

14,174
2
28
22

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. – Austin A Jun 27 '18 at 14:24

WesternGun · Answer 7 · 2019-02-06T16:48:20.463

2

psql -h <host> -p <port> -U <user> -d <db>

and update /var/lib/pgsql/10/data/pg_hba.conf to change the auth method to cert. Check the following link for more information:

https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html

edited Feb 06 '19 at 16:48

answered Feb 06 '19 at 16:41

WesternGun

11,303
6
88
157

score 2 · Answer 8 · answered Jun 16 '21 at 21:35

2

Another pattern that worked with v8 is

psql -h host_name -p port -U user_name "dbname=db sslmode=require"

answered Jun 16 '21 at 21:35

Vijay Hebbar

136
1
3

score 0 · Answer 9 · answered Jun 20 '23 at 13:12

0

You can try to add these to connection string "Persist Security Info=True;SSL Mode=Require"

answered Jun 20 '23 at 13:12

ismail kaşan

11
2

Using psql to connect to PostgreSQL in SSL mode

9 Answers9

Linked