In this article:

Creating a Custom Schema in the Current Database

Features of Working with PostgreSQL DBMS

Example of Ready PostgreSQL DBMS Script in Linux OS

Preparing PostgreSQL Server

PostgreSQL server is prepared by the DBMS administrator.

IMPORTANT. To ensure system security before preparing the PostgreSQL server, check settings of the lc_messages parameter in the postgresql.conf configuration file. The PostgreSQL message language should match with operating system language settings.

To prepare the PostgreSQL server, execute scripts using any of the available methods, for example, via the pgAdmin application. Take into account the following features on executing the scripts:

The USER_NAME, DATABASE_NAME and SCHEMA_NAME parameters should be written in the upper case.
Parameter names are case sensitive and should be enclosed in quotation marks. If quotation marks are missing, parameter names will be converted to lower case during script execution.

Scripts should be executed by the DBMS administrator with the SUPERUSER privilege if the PostgreSQL server is prepared using the psql console.

TIP. To prepare PostgreSQL server in Linux OS, use ready script. After executing the script the following is created: the NEW_USER database user with the NEW_USER password, the NEW_DATABASE database, the repository with the NEW_DATABASE identifier, and the SERVICE_USER service user with the SERVICE_USER password.

Creating a Database User

To create a database user, execute the script:

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

Where:

USER_NAME. Name of the created user.

IMPORTANT. The ADMIN user name must not be used.

USER_PASSWORD. Password for the created user.

Creating a Database

To create a database, execute the script:

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

ALTER DATABASE "DATABASE_NAME" SET lo_compat_privileges = 'on';

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

Where:

USER_NAME. Name of the previously created user.
DATABASE_NAME. Name of the created database.

IMPORTANT. A database is created via the postgres service database by default. To proceed with DBMS server preparation, connect to the created database.

Creating a Custom Schema in the Current Database

The public schema is used by default. When working with the public schema take into account the features given in the Features of Working with PostgreSQL DBMS section.

To create a custom schema in the current database, execute the script:

CREATE SCHEMA "SCHEMA_NAME" AUTHORIZATION "USER_NAME";

Where:

USER_NAME. Name of the previously created user.
SCHEMA_NAME. Custom schema identifier.

After executing the operations a custom schema will be created. The user created at Step 1 will be a schema owner.

To use the custom schema as a default schema, execute the script:

SET search_path TO "SCHEMA_NAME";

After executing the operations, the custom schema will be used instead of the public default schema.

NOTE. For details about working with custom schemas, see PostgreSQL documentation.

Support of the Lo Data Type

The Lo data type is used to save multibyte information in PostgreSQL. By default, after a database is created, support of working with this data type is disabled. To enable the support of the Lo data type, execute the script:

SET search_path TO "SCHEMA_NAME";

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;

Where:

SCHEMA_NAME. Custom schema identifier.

NOTE. If one or several custom schemas are used, execute the script for each schema. If a custom schema is not created, execute the script without specifying the SCHEMA_NAME parameter. The public schema is used by default.

After all steps are executed successfully, proceed to repository creation.

Support of Domain Groups

When domain groups are added in the security manager prepare PostgreSQL server for their support.

NOTE. Support of domain groups in PostgreSQL is available if there are groups as roles and users included in these roles on DBMS level.

To enable support of domain groups in PostgreSQL:

Create the script for users and groups synchronization from Active Directory or Astra Linux Directory using the Git version control system.

Linux OS Windows OS

In Linux OS open the command line and execute the commands:

Debian-based distributions:

apt-get install ruby libpq-dev git make gcc libffi-dev ruby-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

RedHat-based distributions:

dnf install ruby libpq-devel git make gcc libffi-devel ruby-devel redhat-rpm-config
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

ALT Linux:

apt-get install ruby libpq-devel git make gcc5 libffi-devel ruby-tools postgresql-devel libruby-devel
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

In Windows OS, first of all, install the latest version of RubyInstaller to work with the Ruby programming language used for script generation.

Open the Git CMD and execute the commands:

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

After executing the operations, the pg-ldap-sync folder with latest script source files will be added from Git, and the script will be generated.

Create the lsync.yml configuration file manually. The example of the lsync.yml file if Active Directory is used:

# 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 manually before
# pg_ldap_sync can run.
# LDAP server connection parameters based on the AD example. In the username specify the user who has connection permissions (almost any domain user and password)
# see also: http://net-ldap.rubyforge.org/Net/LDAP.html#method-c-new
ldap_connection:
    host: domain-controller.domain.com
    port: 389
    auth:
        method: :simple
        username: username@domain.com
        password: password
        # encryption:
        # method: :simple_tls
# User search options, in this case users are taken from the OU=Users, DC=domain, DC=com section
ldap_users:
   base: OU=users,DC=domain,DC=com
   # LDAP filter (according to RFC 2254)
   # defines to users in LDAP to be synchronized
   # then goes section occurrence filter, only users are searched by object classes, the user can also set up selection by other attributes
   filter: (&(objectClass=person)(objectClass=organizationalPerson)(givenName=*)(sn=*)(sAMAccountName=*))
   # this attribute is used as PG role name
   # the attribute is set, from which user name is created in DBMS
   name_attribute: sAMAccountName
   # lowercase name for use as PG role name
   # set up conversion of user name to lower case
   lowercase_name: false
   # uppercase name for use as PG role name
   # set up conversion of user name to upper case (preferably for work with platform)
   uppercase_name: false
# Search parameters for LDAP groups which should be synchronized
# All the same for groups
ldap_groups:
    base: OU=users,DC=domain,DC=com
    filter: (cn=domain.*)
    # 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: false
    # this attribute must reference to all member DN's of the given group
    member_attribute: member
    # True if use Astra Linux Domain
    ald_domain: false
# Connection parameters to PostgreSQL server
# PostgreSQL server connection parameters
# see also: http://rubydoc.info/gems/pg/PG/Connection#initialize-instance_method
pg_connection:
    host: database_host
    dbname: postgres
    user: postgres
    password: postgres
pg_users:
    # Filter for identifying LDAP generated users in the database.
    # User filter for creating and checking if a separate role is required, in this case ldap_users; users in it will be compared with the search results and will be removed or added in case of differences
    # 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.
    # All the same for groups
    # 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:

NOTE. When creating the lsync.yml file in Astra Linux Directory use the cn value instead of sAMAccountName and set the ald_domain parameter to True. The ald_domain parameter is used to support domain groups in Astra Linux Directory.

Check validity of the created lsync.yml file.
Execute the script:

For single-time synchronization of users and groups start the script using the command:

/usr/local/bin/pg_ldap_sync -c <Full path to configuration file>/lsync.yml

After executing the operations the script is started and synchronization is executed once.

For periodic synchronization of users and groups:

Linux OS Windows OS

In Linux OS set up automatic script start using the Cron (Command Run ON) utility - the system for automatic scheduled program and script start on server:

Execute the command to open and edit the text editor that contains a list of executed commands:

crontab -e

Execute the command to add a string to the text editor, executing of which adds the lsync.yml configuration file:

*/<number of minutes> * * * * /usr/local/bin/pg_ldap_sync -c <full path to configuration file>/lsync.yml

Add an additional empty string.
Save changes in the text editor.

After executing the operations the script will start with the specified frequency, for example, every 5 minutes.

In Windows OS set up automatic script start using Windows task scheduler.

Distribute privileges between domain groups in the security manager. After this permissions for repository tables are granted for users of the corresponding groups.

After executing the operations, the users included in the added domain groups will be able to connect to and work with the repository.

Updating Repository

In Prognoz Platform 8.1, the script related to the registration of the lo type was fixed. If a repository based on PostgreSQL (version 9.3 or later) was created in earlier versions of Prognoz Platform, it must be updated by running the following script:

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

Features of Working with PostgreSQL DBMS

If the default public schema is used for work, all repository users will be able to create tables. It may cause issues with user updates and separation of access permissions of the users who are repository administrators. To avoid possible errors it is recommended to create all objects only under the user created at Step 1 and who is owner and administrator of the ADMIN schema.

Default configuration for PostgreSQL servers includes autovacuum - clearing of space occupied by data deleted from tables using the VACUUM SQL command. Statistic that is used by scheduler to select query execution method is also updated. If it is supposed that intensive work consisting in changing data in tables, it is recommended to set up configuration in such a way that autovacuum is executed in server low-load time (night, weekend). The setup can be executed using the documentation at the developer website.

Example of Ready PostgreSQL DBMS Script in Linux OS

The example contains all parts of selected scripts for the PostgreSQL DBMS:

Download and unpack the Script_example_public.zip archive if the public schema is used.
Download and unpack the Script_example_custom_scheme.zip archive if a custom schema is used.