Подключение Let's Encrypt сертификата к PostgreSQL для подключения PowerBI

To disable SSL:

1. Attempt to connect by selecting PostgreSQL and entering the server host and DB.

2. Enter the user and password.

3. An error occurs: An error occurred while reading data from the provider: 'The remote certificate is invalid according to the validation procedure.'

4. Go to: File — Options and settings — Data source settings

5. Select our host — Edit permissions — Uncheck Encryption

But this is unsafe as the data is transmitted in an open manner!

Because Windows is Windows, it cannot verify your self-signed certificate.

This instruction assumes that you have followed the steps described here or installed using the Auto-install script.

Create a Renewal Hook:

Check the path of the configuration file:

su - postgres -c "psql -c 'SHOW config_file;'"

Open the file in nano using the obtained path, for example:

nano /etc/postgresql/12/main/postgresql.conf

Check the path to data_directory in postgresql.conf — this is the data_directory item

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/12/main'          # use data in another directory
                                                        # (change requires restart)
hba_file = '/etc/postgresql/12/main/pg_hba.conf'        # host-based authentication file
                                                        # (change requires restart)
ident_file = '/etc/postgresql/12/main/pg_ident.conf'    # ident configuration file
                                                        # (change requires restart)

Create a file:

nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy

example.com — replace with your domain.

/var/lib/pgsql/data — replace with the path to your data_directory of postgresql.

#!/bin/bash
umask 0177

export DOMAIN=example.com
export DATA_DIR=/var/lib/pgsql/data

cp /etc/letsencrypt/live/$DOMAIN/fullchain.pem $DATA_DIR/server.crt
cp /etc/letsencrypt/live/$DOMAIN/privkey.pem   $DATA_DIR/server.key
chown postgres:postgres  $DATA_DIR/server.crt $DATA_DIR/server.key

service postgresql restart

Make the script executable:

chmod +x /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy

Run it:

/etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy

Edit the config, comment out the old ssl_cert_file and ssl_key_file and add new paths:

nano /etc/postgresql/12/main/postgresql.conf

# view of the SSL section after changes

# - SSL -

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

#ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
#ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

#ssl_ca_file = ''
#ssl_crl_file = ''
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off

Restart PostgreSQL:

service postgresql restart

Connect the certificate renewal (do not do this if the renewal is already connected):

crontab -e

You need to replace 42 in this line with another number between 0 and 59:

42 */12 * * * certbot renew --quiet --allow-subset-of-names

Now, you have a proper PostgreSQL connector, your database is using SSL so everyone should be happy. So, you publish your report on PowerBI Service and hit refresh.

And then it fails miserably.

https://dataintoresults.com/post/powerbi-and-postgresql-ssl-lets-encrypt-and-gateway/