Привет, Хабр! Я Алексей Шаманов, старший системный архитектор в ГК “Цифра”, занимаюсь проектированием решений на базе платформы ZIIoT (платформа для создания решений управления производственными процессами). Сегодня предлагаю поговорить о настройке PostgreSQL.

В ходе миграции с MS SQL Server на PostgreSQL организации сталкиваются со значительными проблемами при обеспечении безопасности и управляемости инфраструктуры: управление пользователями и ролями, автоматическое назначение полномочий, создание ролей для аудита, контроль предоставленных полномочий в динамически изменяющихся средах — интеграция в существующую инфраструктуру предприятия... Всего этого не то, чтобы "нет", но первое впечатление при переходе обычно удручающее.

Поговорим о настройке PostgreSQL

Особенно тяжело все это происходит при миграции legacy-приложений на классической клиент-серверной архитектуре с непосредственным подключением пользователей к БД и реализацией как минимум части ролевой модели безопасности в ней же. Но и без этого, например, реализация требований информационной безопасности по наличию персонифицированных УЗ для администрирования СУБД и/или создание выделенной роли «аудит» с правами на чтение объектов в сколько-нибудь крупной организации становится той ещё головной болью.

Попробуем её не то, чтобы «решить», но некоторым образом «облегчить» (Разумеется, создавая при этом новые проблемы).

Первое приходящее на ум решение — использовать ad\ldap. Решение работающее, однако в реальности — не самое лучшее, т.к. подключение пользователя требует доступа до ldap сервера (DC в большинстве окружений). Это может создавать проблемы со стабильностью работы (Дополнительные инфраструктурные зависимости) и, что даже более важно, не обеспечивает собственно управления пользователями. Да, в этом случае создавать соответствующие роли в БД и назначать полномочия необходимо вручную, LDAP в данной схеме обеспечивает только аутентификацию пользователей. Да и с безопасностью в этом случае всё обстоит не то чтобы безоблачно — для подключения требуется интерактивный ввод пароля со всеми вытекающими.

Более правильно использовать для аутентификации специализированный протокол (SSPI), а управление ролями осуществлять отдельно. Такой подход ограничивает сетевые взаимодействия между конечным сервером и контроллером домена.

Схема несколько упрощенная, но в целом правильная. Как видно, приложению (Серверу СУБД) в данном случае вообще не требуется доступ к контроллеру домена для проверки. Более того, в этой схеме ни сам пароль, ни его хэш по сети не передаются.

Предполагаю, что если уж мы озаботились управлением пользователями на этом уровне, то linux-сервера у нас уже введены в домен (УЗ компьютера в AD есть, прямое и обратное разрешение имен работает, время синхронизировано, krb5.conf сформирован корректно и т.д.) и все, что нам требуется — создать соответствующий объект в AD, привязать к нему SPN сервиса, выгрузить ключи с маппингом в keytab-файл и настроить postgresql на его использование.

Генерацию keytab-файла можно выполнить как с windows-сервера с помощью ktutil, так и с linux с использованием соответствующего аналога:

apt install msktutil
 msktutil --create --server <dc01.test.lc> --realm TEST.LC --keytab postgres.keytab --dont-expire-password --service  postgres/postgres.test.lc --host postgres.test.lc

При этом обратите внимание - в качестве сервиса используется определенное при компиляции PostgreSQL (–with-krb-srvnam=, если вдруг кому потребуется изменить) значение (По умолчанию, соответственно ‘postgres’), в качестве УЗ, к которой привязывается SPN (Service Principal Name) используется УЗ компьютера с отключенной автоматической сменой пароля (Убирает часть проблем с политикой паролей/требований по смене пароля. В случае, если политика ИБ не позволяет такие фокусы, можно использовать сервисную УЗ с минимальными полномочиями и автоматическую перегенерацию keytab’а по расписанию).

Далее, настраиваем, собственно, postgreSQL:

cp ./postgres.keytab /etc/postgresql/16/main
 chown postgres:postgres /etc/postgresql/16/main/postgres.keytab
 chmod 400 /etc/postgresql/16/main/postgres.keytab

И редактируем postgresql.conf:

krb_server_keyfile = '/etc/postgresql/16/main/postgres.keytab'
 krb_caseins_users = on # Должно решать часть проблем с case-sensivity - kerberos REALM у нас UPPERCASE, помним?

Настраиваем доступ в pg_hba.conf:

hostgssenc all             all             all                     gss include_realm=1 krb_realm=TEST.LC map=krbmap

Обратите внимание: на данный момент мы не используем для подключения SSL, ограничиваясь стандартным gssencryption, но при этом требуем указания kerberos realm и ограничиваем его нашим тестовым доменом TEST.LC — > пользователи из других realm’ов в данной конфигурации подключиться не смогут. Дополнительно можно ограничить список адресов, с которых могут подключаться интерактивные пользователи и т.д. Но в данный момент это не требуется

Т.к. работать с именами вида user@REALM, мягко говоря, не слишком удобно, дополнительно настраиваем маппинг GSS-пользователей на пользователей БД в pg_ident.conf, благо файл хорошо прокомментирован и его редактирование не вызывает проблем:

krbmap /^(.*)@TEST\.LC$ \1

Следом создаем соответствующую роль в PostgreSQL:

create role pguser with login;

Перезапускаем PostgreSQL и проверяем доступ от имени созданного пользователя, и вот тут возникают первые проблемы. Под linux’ом подключение не проходит без явного указания имени пользователя в строке подключения с помощью ключа -U, в логах при этом:

pguser@test.lc@postgres LOG:  no match in usermap "krbmap" for user "pguser@test.lc" authenticated as "pguser@TEST.LC"  

krb_caseins_users у меня влияния не оказал, настройка маппинга на что-нибудь вроде “/^(.?)@.*$” тоже не помогла. В сети что-то вроде встречается, но ответа на вопрос не дает.

pguser@test.lc@uno:~$ psql -h postgres.test.lc -d postgres -U pguser
 psql (16.6 (Ubuntu 16.6-0ubuntu0.24.04.1))
 GSSAPI-encrypted connection
 Type "help" for help.

  postgres=>

С Windows машины все оказалось еще забавней:

c:\Program Files\PostgreSQL\16\bin>psql -h postgres.test.lc -d postgres
 psql: error: connection to server at "postgres.test.lc" (192.168.1.58), port 5432 failed: FATAL:  no pg_hba.conf entry for host "192.168.1.57", user "pguser", database "postgres", SSL encryption

При явном требовании gss encryption все становится понятно:

c:\Program Files\PostgreSQL\16\bin>psql -h postgres.test.lc  -d "gssencmode=require"
 psql: error: gssencmode value "require" invalid when GSSAPI support is not compiled in

Бум! Нет, пересобирать windows-версию postgres мы не будем, просто поменяем для теста ‘hostgssenc’ на ‘host’ в pg_hba.conf и — та-даам! Все работает:

c:\Program Files\PostgreSQL\16\bin>psql -h postgres.test.lc -d postgres
 psql (16.6)
 WARNING: Console code page (866) differs from Windows code page (1251)
          8-bit characters might not work correctly. See psql reference
          page "Notes for Windows users" for details.
 postgres=>

Gss шифрования в windows-сборке psql нет, а аутентификация — есть, такие дела. Но мы всегда можем сделать вид, что «Да не больно-то и хотелось!» и прикрутить SSL (что создаст отдельных размеров головную боль… но о ней нет ни слова в этой висе).

На этом в общем простая часть заканчивается – Иван Иванов успешно сообщает СУБД, что он – это он. Теперь необходимо «как-то» создать этого Ивана в базе и на основании каких-то свойств/признаков назначить ему определенный набор прав.

Управление пользователями и все-все-все

Если создавать роли руками в сколько-нибудь нагруженном окружении сложно, но можно, то обеспечивать их корректность на протяжении всего жизненного цикла УЗ в компании «врукопашную» уже нет. Необходимо автоматизировать данный процесс тем или иным образом.

В качестве основы для выполнения работ предлагается использовать ldap2pg, выполняющий синхронизацию данных между ldap (AD) и PostgreSQL. Запускаемая с помощью cron система получает подмножество ролей (Пользователей LDAP) из AD, фактически предоставленные полномочия в PostgreSQL и синхронизирует эти множества. 

Рассмотрим «худший» случай, при котором:

1. Доменным администраторам СУБД нужны права суперпользователя на сервере PostgreSQL.

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

3. Аналитики должны иметь доступ на чтение ко всем БД.

4. Разработчики должны иметь права на создание объектов в своих БД.

5. К части БД пользователи ходят непосредственно под своими УЗ с разделением прав.

Создаем тестовую среду:

//роли и базы для работы микросервисов с аутентификацией по паролю
 create role zif_test1 with login password 'test';
 create role zif_test2 with login password 'test';
 create
 database zif_test1 owner zif_test1;
 create
 database zif_test2 owner zif_test2;
 // БД для сбора витрин данных аналитиками
 create
 database showcase;
 // База для работы доменных пользователей с GSS
 create
 database selfcare;
 // База для тестирования
 create
 database test;

В Active Directory создаем пользователей и группы:

# Создаем группы
 New-ADGroup -Name "postgres_admins"
             -SamAccountName postgres_admins 
             -GroupCategory Security
             -GroupScope Global
             -Path "OU=postgres,OU=test,DC=test,DC=lc" 
             -Description "Администраторы postgresql"
 New-ADGroup -Name "postgres_readers"
             -SamAccountName postgres_readers 
             -GroupCategory Security
             -GroupScope Global
             -Path "OU=postgres,OU=test,DC=test,DC=lc" 
             -Description "Пользователи postgresql с правами на чтение"
 New-ADGroup -Name "postgres_writers"
             -SamAccountName postgres_writers 
             -GroupCategory Security
             -GroupScope Global
             -Path "OU=postgres,OU=test,DC=test,DC=lc" 
             -Description "Пользователи postgresql с правами на запись"
 New-ADGroup -Name "postgres_developers" 
             -SamAccountName postgres_developers 
             -GroupCategory Security
             -GroupScope Global
             -Path "OU=postgres,OU=test,DC=test,DC=lc" 
             -Description "Пользователи postgresql с правами на создание объектов в БД"
 # Создаем пользователей
 New-ADUser -Name "Иван Иванов"
            -GivenName "Иван"
            -Surname "Иванов"
            -SamAccountName "user1"
            -UserPrincipalName "user1@test.lc"
            -AccountPassword (ConvertTo-SecureString "P@ssw0rd123" -AsPlainText -Force)
            -Path "OU=Users,OU=test,DC=test,DC=lc"
            -Enabled $true
 New-ADUser -Name "Петр Петров"
            -GivenName "Петр"
            -Surname "Петров"
            -SamAccountName "user2"
            -UserPrincipalName "user2@test.lc"
            -AccountPassword (ConvertTo-SecureString "P@ssw0rd123" -AsPlainText -Force)
            -Path "OU=Users,OU=test,DC=test,DC=lc"
            -Enabled $true
 New-ADUser -Name "Сидор Сидоров"
            -GivenName "Сидор"
            -Surname "Сидоров"
            -SamAccountName "user3"
            -UserPrincipalName "user3@test.lc"
            -AccountPassword (ConvertTo-SecureString "P@ssw0rd123" -AsPlainText -Force)
            -Path "OU=Users,OU=test,DC=test,DC=lc" `
            -Enabled $true
 # Добавление пользователя в группу
 Add-ADGroupMember -Identity "postgres_readers" -Members "user1"
 Add-ADGroupMember -Identity "postgres_writers" -Members "user2"
 Add-ADGroupMember -Identity "postgres_developers" -Members "user3"

Т. к. в PostgreSQL нет разделения между понятиями “пользователь” и “роль”, но допускаются т. н. role nesting, предполагается следующая модель предоставления полномочий: 

1. Создается статическая роль reader с правами на чтение всех объектов в СУБД и запретом входа в систему.

2. Создается статическая роль writer с правами на запись всех объектов в СУБД с запретом входа в систему. Роль включается в роль reader.

3. Создается статическая роль developer с правами на создание объектов в СУБД с запретом входа в систему. Роль включается в роль writer.

4. Конечные пользователи импортируются из LDAP с правом на интерактивный вход в систему (WITH LOGIN) и включаются в одну из статических ролей на основании членства в группах pgreader, pgwriter, pgowner.

5. Статические роли, созданные для конкретных микросервисов блеклистятся в программе по префиксу роли zif_*

6. Члены группы pgadmins получают опцию SUPERUSER.

С созданием этакой "матрешки" из вложенных ролей. Создаем файл конфигурации ldap2pg.yml

version: 6 postgres: roles_blacklist_query: [ postgres, pg_*, zif_*, ldap2pg, sa ] databases_query: "SELECT datname FROM pg_database where datname not in ('template0', 'template1') and datname not like 'zif_%' ;" privileges: # Define ro privilege group with read-only grants ro: - connect - __select_on_tables__ - __select_on_sequences__ - __usage_on_schemas__ - __usage_on_types__ # rw privilege group lists write-only grants rw: - temporary - __all_on_tables__ - __all_on_sequences__ # ddl privilege group lists DDL only grants. ddl: - __create_on_schemas__ rules: - description: "Setup static roles and grants." roles: - names: - postgres_readers options: NOLOGIN - name: postgres_writers # Grant reading to writers parent: postgres_readers options: NOLOGIN - name: postgres_developers # Grant read/write to owners parent: postgres_writers options: LOGIN: no CREATEDB: yes grant: - privilege: ro role: postgres_readers - privilege: rw role: postgres_writers - privilege: ddl role: postgres_developers - description: "Postgres administrators" ldapsearch: base: DC=test,DC=lc filter: > (&(memberOf=CN=postgres_admins,OU=postgres,OU=test,DC=test,DC=lc) (!(userAccountControl:1.2.840.113556.1.4.803:=2))(accountExpires>=0)) role: name: '{sAMAccountName}' options: LOGIN parent: postgres - description: "Postgres readers" ldapsearch: base: OU=test,DC=test,DC=lc filter: > (&(memberOf=CN=postgres_readers,OU=postgres,OU=test,DC=test,DC=lc) (!(userAccountControl:1.2.840.113556.1.4.803:=2))(accountExpires>=0)) role: name: '{sAMAccountName}' options: LOGIN parent: postgres_readers - description: "Postgres writers" ldapsearch: base: OU=test,DC=test,DC=lc filter: > (&(memberOf=CN=postgres_writers,OU=postgres,OU=test,DC=test,DC=lc) (!(userAccountControl:1.2.840.113556.1.4.803:=2))(accountExpires>=0)) # Не забудьте проверить активна ли уз role: name: '{sAMAccountName}' options: LOGIN parent: postgres_writers - description: "Postgres developers" ldapsearch: base: OU=test,DC=test,DC=lc filter: > (&(memberOf=CN=postgres_developers,OU=postgres,OU=test,DC=test,DC=lc) (!(userAccountControl:1.2.840.113556.1.4.803:=2))(accountExpires>=0)) role: name: '{sAMAccountName}' options: LOGIN parent: postgres_developers Для доступа к СУБД и домену используем переменные окружения, помещенные в .env файл:

shaman@test.lc@uno:~$ cat .env PGHOST=uno.test.lc PGPORT=5432 PGDATABASE=postgres PGUSER=sa PGPASSWORD=1234 LDAPURI=ldap://dc01.test.lc LDAPBASEDN=DC=test,DC=lc LDAPBINDDN=CN=binduser,CN=Users,DC=test,DC=lc LDAPPASSWORD=Test123

И можно синхронизировать полномочия командой: ldap2pg --config ./ldap2pg.yml

Если результат соответствует желаемому, необходимо добавить опцию ‘–real’ для выполнения реальной синхронизации, ну или соответствующим образом поправить конфигурацию.

При анализе результатов обратите внимание, что стандартные способы просмотра полномочий (+, + и т.д.) не показывают информацию о вложенных ролях, для получения актуального списка необходимо использовать что-то вроде:

WITH RECURSIVE x AS
                   (SELECT member::regrole, roleid::regrole AS role, member::regrole  ' -> '  roleid::regrole AS path
                    FROM pg_auth_members AS m
                    UNION ALL
                    SELECT x.member::regrole, m.roleid::regrole, x.path  ' -> '  m.roleid::regrole
                    FROM pg_auth_members AS m
                            JOIN x ON m.member = x.role)
 SELECT member, role, path
 FROM x
 ORDER BY member::text, role::text;

   |         member     |         role         |                        path|
 ---------------------+----------------------+----------------------------------------------------------------------
  pg_monitor          | pg_read_all_settings | pg_monitor -> pg_read_all_settings
  pg_monitor          | pg_read_all_stats    | pg_monitor -> pg_read_all_stats
  pg_monitor          | pg_stat_scan_tables  | pg_monitor -> pg_stat_scan_tables
  postgres_developers | postgres_readers     | postgres_developers -> postgres_writers -> postgres_readers
  postgres_developers | postgres_writers     | postgres_developers -> postgres_writers
  postgres_writers    | postgres_readers     | postgres_writers -> postgres_readers
  shaman              | postgres             | shaman -> postgres
  user1               | postgres_readers     | user1 -> postgres_readers
  user2               | postgres_readers     | user2 -> postgres_writers -> postgres_readers
  user2               | postgres_writers     | user2 -> postgres_writers
  user3               | postgres_developers  | user3 -> postgres_developers
  user3               | postgres_readers     | user3 -> postgres_developers -> postgres_writers -> postgres_readers
  user3               | postgres_writers     | user3 -> postgres_developers -> postgres_writers

Начиная с PostgreSQL 16 можно ограничить использование полномочий суперпользователя для выполнения синхронизации при условии, что набор объектов в БД меняется относительно редко и ролевая модель статична и ведется вручную. В этом случае для подключения к БД необходимо создать отдельную роль с полномочиями CREATEROLE и предоставить ей полномочия администратора на управляемые роли: 

CREATE ROLE 'ldap2pg' WITH LOGIN CREATEROLE PASSWORD 'test';
 GRANT postgres_readers TO ldap2pg WITH ADMIN OPTION;

После выполнения необходимых настроек создаем отдельного пользователя и включаем запуск синхронизатора через cron с требуемой частотой запуска.

Вуаля!