CODEWITHSUNDEEP
postgresqlssl-certificate
Blanca Hdez
Blanca Hdez

Reputation: 3563

Enable SSL connections on PostgreSQL server

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: enter image description here

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

Upvotes: 2

Views: 9360

Answers (1)

Dennis Hort
Dennis Hort

Reputation: 21

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.

Upvotes: 2

Related Questions