Enable SSL connections on PostgreSQL server

Question

I am trying to connect my PowerBI with a postgreSQL database. For this reason I need to enable the SSL connections. So far, I have created the server.key and server.crt files as referred in the documentation I have also modified the postgresql.conf file with the following parameters:

#authentication_timeout = 1min      # 1s-600s
ssl = on                # (change requires restart)
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
                    # (change requires restart)
#ssl_prefer_server_ciphers = on     # (change requires restart)
#ssl_ecdh_curve = 'prime256v1'      # (change requires restart)
ssl_cert_file = 'server.crt'        # (change requires restart)
ssl_key_file = 'server.key'     # (change requires restart)
#ssl_ca_file = ''           # (change requires restart)
#ssl_crl_file = ''          # (change requires restart)
#password_encryption = on
#db_user_namespace = off
#row_security = on

But I have no success, what am I missing?

Postgres version: 9.5 WIndows 8

I am using ODBC to test if the connection with SSL actually works, and as you can see it doesnt:

I would like to figure out how to get this running, but I also would be happy if I can force Power BI not to user SSL connection. If there is someone who knows how to do it, let me know!

EDIT More information: pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
hostssl    all             all             127.0.0.1/32            md5
# IPv6 local connections: host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

Can't you just turn off the requirement? In your screen shot you have SSL mode set to require. Can't you change that to allow or disable? — Panama Jack
– Panama Jack, Commented Dec 9, 2015 at 15:49
I am using ODBC to test if the SSL conection actually works. I need it for the connection with Power BI — Blanca Hdez
– Blanca Hdez, Commented Dec 9, 2015 at 16:00
Are you using a self-signed cert? If so, it might not work. You may need to get a certificate from an authorized certificate authority. — Alexander
– Alexander, Commented Dec 9, 2015 at 16:21
@Alexander Yes, I was trying it with a self-signed cert. How could I get it from an authorized authority? (which one?) — Blanca Hdez
– Blanca Hdez, Commented Dec 9, 2015 at 16:41
Usually you would buy one from a certificate authority. There are several of them, but Namecheap.com is a source I know of. A new service that looks very cool is Let's Encrypt (letsencrypt.org/howitworks), which lets you get free SSL certificates. I would recommend giving Let's Encrypt a try first since it's free. After you get the cert, replace your self-signed one with that one. — Alexander
– Alexander, Commented Dec 9, 2015 at 16:47

Dennis Hort · Accepted Answer · 2017-05-08 21:14:01Z

You don't mention having re-built Postgres from source as it says is required in the first paragraph of the documentation you cite:

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time (see Chapter 15).

(emphasis mine)

If you are trying to make it work from a pre-packaged distribution, such as an RPM or Debian package, SSL support is not compiled in, so is not available to you.

You need to be sure you have OpenSSL installed, and rebuild PostgreSQL from source, adding the --with-openssl command line option to the source tree configuration as described in 15.4. Installation Procedure.

See Chapter 15. Installation from Source Code for a more complete description of the necessary installation procedure.

Collectives™ on Stack Overflow

Enable SSL connections on PostgreSQL server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related