Postgresql – How to connect to an Amazon PostgreSQL database using SSL

amazon-rdspostgresqlssl

I'm trying to log into an Amazon PostgreSQL DBS using SSL. I have all credentials but my psql version doesn't seem to support SSL. I've tried searching Postgres docs but all I see is how it has native support. Here's my command and output. I'm running the psql command on a CentOS 6.5 box.

psql --host=XXXX.rds.XXXX.com  --sslmode=require --sslrootcert=rds-ssl-ca-cert.pem \
--port=XXXX --username=XXXXX --password --dbname=XXXX

Response:

psql: unrecognized option '--sslmode=require'

So my question is, how do I install a psql client that supports SSL. I don't need the full blown DB support, just the connector, psql.

Thanks

I should add that I'm trying to follow these instructions.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL

Best Answer

Many connection options aren't available as command line flags. That's one of them. Use a connection string, e.g.:

psql "host=XXXX.rds.XXXX.com sslmode=require sslrootcert=rds-ssl-ca-cert.pem port=XXXX user=XXXXX dbname=XXXX"

(Syntax corrected)

Related Solutions

Postgresql – Connecting pgAdmin3 to Postgres on Heroku

Thanks to @araqnid on Stack Overflow for the answer here.

Quoted Verbatim from there:

Open the "Properties" of the Heroku server in pgAdminIII and change the "Maintenance DB" value to be the name of the database you want to connect to. The default setup is suitable for DBAs et al who can connect to any database on the server, but apparently that isn't true in your case.

This worked for me!

Mysql – How to verify I’m using SSL to connect to thesql

From the client, just run status. If this connection is using SSL, you'll get something interesting in the SSL row.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1

Connection id:      12
Current database:
Current user:       replicator@domU-12-31-39-10-54-BD.compute-1.internal
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.30-log MySQL Community Server (GPL)
Protocol version:   10
Connection:     boston.hugskeep.wstudent.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         44 min 49 sec

Threads: 2  Questions: 16  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.005
--------------

mysql>

If this connection is not using SSL, you'll get:

SSL:            Not in use

You can also use:

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.00 sec)

mysql>

But I think the first is more attractive, and sure easier to type.

Best Answer

Related Solutions

Postgresql – Connecting pgAdmin3 to Postgres on Heroku

Mysql – How to verify I’m using SSL to connect to thesql

Related Question