Preparing PostgreSQL Server

TIP. To prepare a 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

Creating a Database

Creating a Custom Schema in the Current Database

Support of Domain Groups

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

NOTE. Domain group support 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:

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

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.

2. 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.
# Parameters of connection with LDAP server in the AD example. Indicate in username the user who has connection permissions (almost any domain user name 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 settings, in this case users are taken from the section OU=Users, DC=domain, DC=com
ldap_users:
   base: OU=users,DC=domain,DC=com
   # LDAP filter (according to RFC 2254)
   # defines to users in LDAP to be synchronized
   # the following is the section occurrence filter, only proper users are searched by object classes, one can also set search by other attributes
   filter: (&(objectClass=person)(objectClass=organizationalPerson)(givenName=*)(sn=*)(sAMAccountName=*))
   # this attribute is used as PG role name
   # attribute is set, which will be used to create user name 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 (recommended for working 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
# Parameters of connection to PostgreSQL server
# 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 to create and check whether a separate role is required, in this case ldap_users, users in this role will be compared with the search results and will be removed 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.

3. Check validity of the created lsync.yml file.

4. 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:

5. 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.

Features of Working with PostgreSQL DBMS

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.