Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://stackoverflow.com/questions/14021998/using-psql-to-connect-to-postgresql-in-ssl-mode below:

Using psql to connect to PostgreSQL in SSL mode

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 ?

20.1k1919 gold badges110110 silver badges207207 bronze badges

asked Dec 24, 2012 at 13:25

36.8k4545 gold badges122122 silver badges163163 bronze badges

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.

answered Dec 24, 2012 at 14:17

62.1k1616 gold badges133133 silver badges159159 bronze badges

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

answered May 13, 2016 at 15:07

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" 

answered Jan 3, 2020 at 18:45

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 8, 2020 at 9:20

86999 silver badges1010 bronze badges

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 3, 2019 at 8:11

Another pattern that worked with v8 is

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

answered Jun 16, 2021 at 21:35

18622 silver badges33 bronze badges

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)

answered Mar 10, 2016 at 11:34

15.2k22 gold badges3232 silver badges2323 bronze badges

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

answered Feb 6, 2019 at 16:41

13k99 gold badges118118 silver badges188188 bronze badges

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

answered Jun 20, 2023 at 13:12

Start asking to get answers

Find the answer to your question by asking.

Explore related questions

See similar questions with these tags.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4