158

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 ?

Improve this question
3
  • The port 2345 instead of the default 5432 is intended? Commented Dec 24, 2012 at 13:52
  • Yes. This is the port configured. Commented Dec 24, 2012 at 14:28
  • 5
    If you want the connection to be secure against MITM attacks, use sslmode=verify-full, not just require. Commented Dec 24, 2012 at 14:40

9 Answers 9

Reset to default
179

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I got this connection string syntax from this link. postgresql.org/docs/9.2/static/app-psql.html
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
@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: 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
@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.
|
40 
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
Improve this answer

2 Comments

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.
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.
24

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"
Improve this answer

4 Comments

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
yeah, it works, at that time Google did not have this recommendation, they stoled it from me :D just joking))
you the real MVP, Google plz give a cookie to this man! ❤
I had to use 'host' instead of 'hostaddr' but otherwise worked like a charm. Should IMHO be the accepted answer.
15

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
Improve this answer

4 Comments

for TLS auth you'll also need: PGSSLCERT and PGSSLKEY and drop the -W
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.
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.
That's absolutely true! For windows, I made a bat file in which I define environment variables SET PGPASSWORD=123 SET PGSSLMODE=verify-full SET PGSSLROOTCERT=root_pem.cer SET PGSSLCERT=client_pem.cer SET PGSSLKEY=client.key and then run pgsql.exe with arguments -h -p -U -D. Thus, some of the variables are set through the console environment, and the other part is passed as startup arguments. Thanks!
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}"
Improve this answer

Comments

7

Another pattern that worked with v8 is

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

Improve this answer

Comments

4

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)

Improve this answer

1 Comment

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.
3 
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

Improve this answer

Comments

2

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.