Preparing PostgreSQL Server

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";

where:

USER_NAME. Name of the previously created user.
DATABASE_NAME. Database name.

Creating a Schema in Current Database

To create a schema, use the following script that runs in the DBMS front end:

CREATE SCHEMA SCHEMA_NAME;

where:

SCHEMA_NAME. Schema name.

NOTE. For details about work with schemas, see PostgreSQL documentation.

Support of the Lo 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, run the following script:

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;

After all steps are executed successfully, go 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 role on DBMS level.

To support domain groups in PostgreSQL:

Generate a script for users and groups synchronization from Active Directory (pg-ldap-sync) using the Git version control system.

Windows OS Linux OS

Before this 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

Open the command line and execute the commands:

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

NOTE. The commands are executed only for Debian-based distribution files.

After executing the commands the pg-ldap-sync folder with latest script source files is added from Git, Ruby components are installed, and the script is generated.

Create the lsync.yml configuration file with settings specified in the example:

# 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 server connection parameters based on our AD in the username 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: FSPRMDC01.fs.fsight.world
  port: 389
  auth:
    method: :simple
    username: domainuser@fs.fsight.world
    password: password
    # encryption:
    #method: :simple_tls
# User search options, in this case users are taken from the section 
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
  # 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 Postgres usually sAMAccountName
  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 normal work with platform)
  uppercase_name: true
# Search parameters for LDAP groups which should be synchronized
# All the same for groups
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 server connection parameters
# 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.
  # 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:

Check validity of the created lsync.yml file.
Run the script executed in DBMS front end:

For single-time users and groups synchronization from Active Directory start th script using the command:
```
/usr/local/bin/pg_ldap_sync -c <full path to configuration file>/lsync.yml
				
```
After execution the operation the script is started and synchronization is executed once.
For regular users and groups synchronization from Active Directory:

Windows OS Linux OS

Set up automatic script run by means of Windows task scheduler.

Set up automatic script run by means of the Cron (Command Run ON) utility that is a system for automatic and scheduled program and script run 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.

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 with registration of the "lo" type was corrected. If a repository based on PostgreSQL DBMS (version 9.3 or later) was created in the earlier versions of Prognoz Platform, it should be updated by means of 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 Public default scheme is used on working, all repository users will be able to create tables. It may cause issues with user updates and taking into account access permissions of users that are repository administrators. To avoid possible errors it is recommended to create all objects only under users included in the Administrators group.

Default configuration for PostgreSQL servers includes autovacuum - clearing of space occupied by data deleted from tables using the VACUUM SQL command. Statistics 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 according to documentation available on the developer's site.