Дисклеймер

Скажу сразу, красивой схемы раздачи прав через группы у меня без написания расширений не получилось, но даже получившееся решение сильно упростило жизнь.

Общий принцип получился следующий:

1. создаем группу в службе каталогов, членство в которой будет давать право авторизации в СУБД

2. в экземпляре СУБД добавляем авторизацию через ldap, но с фильтром членства в группе указывающим на право доступа

3. так как для авторизации у нас в обязательном порядке пользователь уже должен быть в СУБД - каждые x минут bash-скрипт добавляет пользователей из группы в АД в СУБД

Как происходит авторизация:

1. Пользователь подключается к Postgres

2. Происходит проверка в LDAP логина/пароля

3. Происходит проверка что учетная запись находится в нужной группе

4. Происходит допуск в СУБД

Ответы на вопросы "А почему ...?"

Вопрос - "А почему не сделать добавление пользователей централизованно?"

Ответ - Так сделано из-за простоты и надежности.

Вопрос - "А почему bash, а не python/go/мой любимый язык?"

Ответ - потому что эти языки тянут за собой зависимости, необходимые для их работы. Плюс порог вхождения в этот механизм сложнее, а следовательно инженеров, которые могут это быстро починить - меньше

Вопрос - "А почему ...?"

Ответ - задайте ваш вопрос в комментариях - я постараюсь ответить

Включение авторизации через ldap

Для полноценной авторизации требуется выполнить следующие шаги:

1. Создать группу в AD со следующим именем company.ru/Access_groups/PostgreSQL/pg_admin_<имя сервера>

2. На сервере PostgreSQL внести правки в настройки pg_hba

1. Если сервер не в кластере Patroni - изменения вносятся в текущий файл pg_hba.conf - путь к нему можно узнать, выполнив в БД запрос

show hba_file;

2. Если сервер в кластере Patroni, то приведённые ниже строки добавляются в раздел postgres.pg_hba конфигурации кластера через команду

patronictl -c /etc/patroni/patroni.yml edit-conf

Добавляем следующие 2 правила самыми первым - правила обрабатываются по порядку до первого совпадения (здесь и далее подставляем валидные значения вместо значений в скобках <> )

host  all    all   0.0.0.0/0       ldap ldapserver="srv-dc01.company.ru" ldapbasedn="ou=admins,dc=company,dc=ru" ldapbinddn="cn=pg_ad_read,ou=PostgreSQL,ou=Service_accounts,dc=company,dc=ru" ldapbindpasswd="SuperSecretPassword"  ldapsearchfilter="(&(objectClass=user)(memberOf=cn=pg_admin_<имя сервера>,ou=PostgreSQL,ou=Access_groups,DC=company,DC=ru)(sAMAccountName=$username))

Если нужно дать доступ пользователям, расположенным в нескольких местах то нужно добавить два правила, указывающих в разные места

host  all    all   0.0.0.0/0       ldap ldapserver="srv-dc01.company.ru" ldapbasedn="ou=customers,dc=company,dc=ru" ldapbinddn="cn=pg_ad_read,ou=PostgreSQL,ou=Service_accounts,dc=company,dc=ru" ldapbindpasswd="SuperSecretPassword"  ldapsearchfilter="(&(objectClass=user)(memberOf=cn=pg_admin_<имя сервера>,ou=PostgreSQL,ou=Access_groups,DC=company,DC=ru)(sAMAccountName=$username))

В этом правиле:

host означает подключения к серверу извне

- первое all - все базы

- второе all - все пользователи

- 0.0.0.0/0 - из всех сетей

- ldap - метод авторизации

- ldapserver - адрес сервера AD

- ldapbasedn - путь, где будет производиться поиск пользователей (весь домен указывать нельзя, чем меньше область поиска - тем лучше, так как в большом домене поиск будет занимать слишком много времени)

- ldapbinddn - DN специального доменного пользователя pg_ad_read, который нужен для того, чтобы запросить состав доменной группы - сделать это можно только авторизовавшись

- ldapbindpasswd - пароль этого пользователя

- ldapsearchfilter - фильтр для поиска пользователя - проверки его членства в группе и его логина

Для того, чтобы изменённые правила применились, достаточно выполнить запрос

select pg_reload_conf();

Перезапуск службы (вызывающий падение коннектов и прерывание текущих запросов) не требуется.

Создание на сервере PostgreSQL локальных пользователей

На сервере PostgreSQL создаётся скрипт следующего содержания:

#!/bin/bash
#Проверяем, не является ли сервер репликой в кластере. Если является - то не пытаемся создавать пользователей
REPLICASTATUS=`psql -t -c "select pg_is_in_recovery()"`
if [ $REPLICASTATUS == "f" ]
  then
#Назначаем имя группы в AD исходя из имени сервера
    GROUP_DN="CN=pg_admin_$(hostname -s),ou=PostgreSQL,ou=Access_groups,DC=company,DC=ru"
#Получаем список пользователей в соответствующей группе AD
    ldapsearch -o ldif-wrap=no -xLLL -b "$GROUP_DN" -H 'ldaps://srv-dc01.company.ru:636 ldaps://srv-dc02.company.ru:636' -D pg_ad_read@company.ru -w 'SuperSecretPassword' | grep member | while read member
#Для каждого найденного пользователя получаем DN
    do
#Если DN в формате base64, преобразовываем его в читаемый вид
      if [[ "$member" =~ ^member::.*$ ]]
        then
          DN=$(echo "$member" | awk '{print $2}' | base64 -d - )
        else
          DN=$(echo "$member" | awk '{print $2}')
      fi
#Получаем логин пользователя из атрибута sAMAccountName
      USERNAME=$(ldapsearch -o ldif-wrap=no -xLLL -H 'ldaps://srv-dc01.company.ru:636 ldaps://srv-dc02.company.ru:636' -D pg_ad_read@company.ru -w 'SuperSecretPassword' -b "$DN" sAMAccountName | grep -v "dn:" | grep '.'| awk '{print $2}'| awk '{print tolower($0)}')
#Проверяем, есть ли уже такой пользователь на сервере PostgreSQL
      USERSTATUS=$(psql -tc "SELECT 'exists' FROM pg_user WHERE usename = '$USERNAME'")
#Если есть, сообщаем об этом. Если нет - создаём
      if [[ "$USERSTATUS" =~ .*exists.* ]]
        then echo "Пользователь $USERNAME уже существует"
        else echo "Создаём пользователя $USERNAME" && createuser -se $USERNAME
      fi
    done
  else
    exit 0
fi

Этот скрипт ходит в AD под учётной записью pg_ad_read и смотрит список пользователей соответствующей группы, получает из неё список пользователей и создаёт одноименных локальных пользователей PostgreSQL для возможности последующей авторизации с паролем из AD.

Скрипт помещается на сервер PostgreSQL в cron пользователя postgres и выполняется с указанной при этом частотой.

Важно! Поскольку имена учётных записей в PostgreSQL регистрозависимы и учётки Ivanov.AI и ivanov.ai с точки зрения PG разные, а в с точки зрения AD одинаковые, то это надо учитывать при работе. У нас принято решение что учетки всегда будут в нижнем регистре.

Авторизация в PostgreSQL через доменные группы