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  quot on quot  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  quot postgresql   localhost 2345 postgres sslmode require quot   but I am getting        psql  invalid connection option  quot postgresql   localhost 2345 postgres sslmode quot   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

User · Accepted Answer

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

User · Answer

psql -h  lt host gt  -p  lt port gt  -U  lt user gt  -d  lt db gt    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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] Using psql to connect to PostgreSQL in SSL mode

Examples related to postgresql

Examples related to ssl

Examples related to ssl-certificate

Examples related to postgresql-8.4