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

edfecdf435a299f4d66ef7f5ec657ad0.jpeg

Дисклеймер

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

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

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 одинаковые, то это надо учитывать при работе. У нас принято решение что учетки всегда будут в нижнем регистре.

© Habrahabr.ru