I'm new on SSL, so I've started by reading lot of tutorials, topics, forum... There is lot of information, and it's a bit complicated. I'm trying to make a SSL connection between my computer and a Postgresql instance on a different server. To do that, I've worked step by step.
Check that the connection between the two is working without SSL
- connect myself by command line: OK !
psql "hostaddr=X.X.X.X port=5432 user=postgres dbname=my_db"
- connect myself by a DB visual tool (TeamSql, it's like PgAdmin or MysqlWorkbench): OK !
Generate keys and certificates; I've followed a tutorial online (everything following in "[]" is a parameter that I've hide)
Create the server key
openssl genrsa -des3 -out server.key 1024
chmod 400 server.key
chown postgres.postgres server.key
openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=FR/ST=[MyCountry]/L=[MyCity]/O=[MyCompany]/CN=postgres/emailAddress=[[email protected]]'
Assume that the root certificate is the same
cp server.crt root.crt
After that, generate client side. First, the key
openssl genrsa -des3 -out /tmp/postgresql.key 1024
Then the CSR
openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr -subj '/C=FR/ST=[MyCountry]/L=[MyCity]/O=[MyCompany]/CN=postgres'
And finally the CRT
openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
- Activation of SSL on server side. In the PG_HBA.CONF file, add a specific line to handles SSL connections from outside
In the POSTGRESQL.CONF file, modify those lines:
listen_addresses = '*'
[...]
ssl = on
[...]
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
Retrieves postgresql.key, postgresql.crt and root.crt files on my computer, in the /home/user/.postgresql/ folder. Also changed rights on the postgresql.key file (600)
Try the first step again. In case of command line, everything is ok. But with the visual tool, even with the SSL option activated (and using the same three files) there is two results:
with "Reject Unauthorized" option set to OFF: it's working
with "Reject Unauthorized" option set to ON: got an error
Hostname/IP doesn't match certificate's altnames: "IP: X.X.X.X is not in the cert's list: "
(with X.X.X.X my server IP)
So my question is: did I do everything fine? Why there is a difference between the command line connection and the visual tool one? Is the certificates generation's ok?
Of course, I don't want a real CA Certification. I'm looking for a self-signed one, but even if I've followed different tutorials, it's still not working... Thanks for your help and have a good weekend
