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

Чтобы отключить SSL:

1. Попытаться подключится выбрав PostgreSQL и введя хост сервера и БД.

2. Вводим пользователя и пароль

3. Выпадает ошибка: При чтении данных от поставщика произошла ошибка: 'Удаленный сертификат недействителен согласно результатам проверки подлинности.

4. Идем: Файл — Параметры и настройки — Настройки источника данных

5. Выбираем наш хост — Править разрешения — Снимаем галочку Шифрование

Но это небезопасно так как данные передаются открытым способом!

Потому что Windows – это Windows, и он не может проверить ваш самоподписанный сертфикат.

Эта инструкция предполагает, что вы выполняли действия описанные здесь или устанавливани при помощи скрипта Автоустановки.

Создаем Renewal Hook:

Смотрим путь расположения конфигурационного файла:

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

Открываем файл в nano по полученному пути, например:

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

Смотрим путь к data_directory в postgresql.conf — это пункт data_directory

#------------------------------------------------------------------------------
# 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)

Создаем файл:

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

example.com — заменить на ваш домен.

/var/lib/pgsql/data — заменить на путь к data_directory вашего 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

Делаем скрипт исполняемым:

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

Выполним его:

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

Редактируем кофиг, комментируем старые ssl_cert_file и ssl_key_file и добавляем новые пути:

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

# вид секции SSL после изменения

# - 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

Перезапускаем PostgreSQL:

service postgresql restart

Подключаем перевыпуск сертификата (не делаем этого если перевыпуск уже подключен):

crontab -e

Вам нужно заменить 42 в этой строке на другое число в диапазоне между 0 и 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/