Подготовка сервера PostgreSQL

Процедура подготовки сервера PostgreSQL производится администратором СУБД.

Важно. Параметры USER_NAME и DATABASE_NAME должны быть прописаны в верхнем регистре.

Выполнение скриптов происходит любым из доступных способов, например, с помощью приложения «pgAdmin», доступного на официальном сайте pgAdmin.

Создание пользователя БД

Пользователь создается при помощи скрипта, который выполняется в клиентской части СУБД:

CREATE ROLE "USER_NAME" LOGIN ENCRYPTED PASSWORD 'USER_PASSWORD' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

ALTER DATABASE "DATABASE_NAME" SET lo_compat_privileges TO on;

где:

• USER_NAME. Имя создаваемого пользователя (схемы);

• USER_PASSWORD. Пароль для создаваемого пользователя (схемы);

• DATABASE_NAME. Имя базы данных.

Создание БД

Создание базы данных можно произвести с помощью следующего скрипта, выполняемого в клиентской части СУБД:

CREATE DATABASE "DATABASE_NAME" WITH OWNER = "USER_NAME" ENCODING = 'UTF8' TABLESPACE = pg_default  LC_COLLATE = default LC_CTYPE = default CONNECTION LIMIT = -1;

GRANT ALL ON DATABASE "DATABASE_NAME" TO "USER_NAME";

где:

• USER_NAME. Имя пользователя, созданного ранее;

• DATABASE_NAME. Имя базы данных.

Создание схемы в текущей БД

Создание схемы можно произвести с помощью следующего скрипта, выполняемого в клиентской части СУБД:

CREATE SCHEMA SCHEMA_NAME;

где:

• SCHEMA_NAME. Имя схемы.

Примечание. Более подробную информацию по работе со схемами смотрите в документации PostgreSQL.

Поддержка типа Lo

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

CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal IMMUTABLE STRICT;

CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidsend' LANGUAGE internal IMMUTABLE STRICT;

CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend, INTERNALLENGTH = 4, PASSEDBYVALUE );

CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;

CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;

CREATE OR REPLACE FUNCTION lo_manage() RETURNS pg_catalog.trigger AS '$libdir/lo' LANGUAGE C;

После того как все шаги выполнены успешно можно перейти к созданию репозитория.

Поддержка доменных групп

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

Примечание. Поддержка доменных групп в PostgreSQL доступна, если на уровне СУБД содержатся группы в виде ролей и пользователи, входящие в эти роли.

Для поддержки доменных групп в PostgreSQL:

1. Соберите скрипт синхронизации пользователей и групп из Active Directory (pg-ldap-sync) с помощью системы управления версиями Git.

git clone https://github.com/afsight/pg-ldap-sync.git
cd pg-ldap-sync
gem install bundler -v 1.16
bundle 1.16 install
bundle install
bundle exec rake install

apt-get install ruby libpq-dev git make gcc libffi-dev
git clone https://github.com/afsight/pg-ldap-sync.git
cd pg-ldap-sync
gem install bundler -v 1.16
bundle 1.16 install
bundle install
bundle exec rake install

После выполнения действий будет добавлена папка pg-ldap-sync с последними исходными файлами скрипта из Git, установлены компоненты Ruby и выполнена сборка скрипта.

2. Создайте файл конфигурации lsync.yml с настройками, указанными в примере:

# With this sample config the distinction between LDAP-synchronized
# groups/users from is done by the membership to ldap_user and
# ldap_group. These two roles has to be defined manally before
# pg_ldap_sync can run.
# Параметры соединения с сервером LDAP на примере нашего AD в username пользователь у которого есть 
права на подключение, практически любой доменный пользователь и пароль
# see also: http://net-ldap.rubyforge.org/Net/LDAP.html#method-c-new
ldap_connection:
  host: FSPRMDC01.fs.fsight.world
  port: 389
  auth:
    method: :simple
    username: domainuser@fs.fsight.world
    password: password
    # encryption:
    #method: :simple_tls
# Настройки поиска пользователей, в данном случае пользователи берутся из раздела 
OU=Users,OU=FSight Organization,DC=fs,DC=fsight,DC=world
ldap_users:
  base: OU=Users,OU=FSight Organization,DC=fs,DC=fsight,DC=world
  # LDAP filter (according to RFC 2254)
  # defines to users in LDAP to be synchronized
  # далее идет фильтр вхождений в раздел, ищутся только собственно пользователи по классам объектов,
также можно указать выборку по другим атрибутам
  filter: (&(objectClass=person)(objectClass=organizationalPerson)(givenName=*)(sn=*)(sAMAccountName=*))
  # this attribute is used as PG role name
  # задается атрибут из которого будет формироваться имя пользователя в Postgres обычно sAMAccountName
  name_attribute: sAMAccountName
  # lowercase name for use as PG role name
  # настройка перевода имени пользователя в нижний регистр
  lowercase_name: false
  # uppercase name for use as PG role name
  # настройка перевода имени пользователя в верхний регистр (предпочтительно для нормально работы с платформой)
  uppercase_name: true
# Search parameters for LDAP groups which should be synchronized
# Все тоже самое для групп
ldap_groups:
  base: OU=FSight Organization,DC=fs,DC=fsight,DC=world
  filter: (&(objectClass=group))
  # this attribute is used as PG role name
  name_attribute: cn
  # lowercase name for use as PG role name
  lowercase_name: false
  # uppercase name for use as PG role name
  uppercase_name: true
  # this attribute must reference to all member DN's of the given group
  member_attribute: member
# Connection parameters to PostgreSQL server
# Параметры подключения к серверу PostgreSQL
# see also: http://rubydoc.info/gems/pg/PG/Connection#initialize-instance_method
pg_connection:
  host: server
  dbname: postgres
  user: postgres
  password: password
pg_users:
  # Filter for identifying LDAP generated users in the database.
  # Фильтр пользователей для создания и проверки, нужна отдельная роль, в данном случае ldap_users,
пользователи в ней будут сравниваться с тем что пришло поиском и удаляться или добавляться в 
случае различий
  # It's the WHERE-condition to "SELECT rolname, oid FROM pg_roles"
  filter: oid IN (SELECT pam.member FROM pg_auth_members pam JOIN pg_roles pr ON pr.oid=pam.roleid 
WHERE pr.rolname='ldap_users')
  # Options for CREATE RULE statements
  create_options: LOGIN IN ROLE ldap_users
pg_groups:
  # Filter for identifying LDAP generated groups in the database.
  # Тоже самое для групп 
  # It's the WHERE-condition to "SELECT rolname, oid FROM pg_roles"
  filter: oid IN (SELECT pam.member FROM pg_auth_members pam JOIN pg_roles pr ON pr.oid=pam.roleid 
WHERE pr.rolname='ldap_groups')
  # Options for CREATE RULE statements
  create_options: NOLOGIN IN ROLE ldap_groups
  grant_options:

3. Проверьте валидность созданного файла lsync.yml.

4. Запустите скрипт, выполняемый в клиентской части СУБД:

• для одноразовой синхронизации пользователей и групп из Active Directory запустите скрипт с помощью команды:

  /usr/local/bin/pg_ldap_sync -c <полный путь до файла конфигурации>/lsync.yml

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

• для периодической синхронизации пользователей и групп из Active Directory:

crontab -e

*/<количество минут> * * * * /usr/local/bin/pg_ldap_sync -c <полный путь до файла конфигурации>/lsync.yml

5. Распределите привилегии между доменными группами в менеджере безопасности. После чего для пользователей соответствующих групп будут раздаваться гранты на таблицы репозитория.

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

Обновление репозитория

В Prognoz Platform версии 8.1 был исправлен скрипт, связанный с регистрацией типа lo. Если репозиторий на базе СУБД PostgreSQL (версии 9.3 или выше) был создан в более ранних версиях Prognoz Platform, то его необходимо обновить, выполнив следующий скрипт:

CREATE OR REPLACE FUNCTION losend (lo) RETURNS bytea AS 'oidsend' LANGUAGE internal IMMUTABLE STRICT;

Особенности работы с СУБД PostgreSQL

Если при работе используется схема по умолчанию Public, то у всех пользователей репозитория будет возможность создания таблиц. Это может привести к проблемам обновления пользователей и учёту прав доступа у пользователей, которые являются администраторами репозитория. Для избежания возможных ошибок рекомендуется создавать все объекты только под пользователями, входящими в группу Администраторы.

Конфигурация по умолчанию для серверов PostgreSQL включает в себя функцию автоочистки - освобождение пространства, занимаемого удалёнными из таблиц данными, с помощью SQL-команды VACUUM. Также производится обновление статистики, которая используется планировщиком для выбора способа выполнения запросов. Если предполагается интенсивная работа по изменению данных в таблицах, то рекомендуется настроить конфигурацию таким образом, чтобы автоочистка производилась в периоды малых нагрузок на сервер (ночью, в выходные). Настройку можно производить в соответствии с документацией, доступной на сайте разработчика по адресу https://postgrespro.ru/docs/postgrespro/9.5/routine-vacuuming#autovacuum.