Creating user databases on nodes

Expand all | Collapse all

For the components of the identity service to work, you need to configure the connection to the PostgreSQL databases on the primary and backup nodes and manually create the databases that will store information about LDAP users and groups in PostgreSQL version 15. For more information on managing databases, please refer to documentation on the official PostgreSQL website.

To set up a connection to PostgreSQL databases:

1. Connect to the primary node.
2. Install PostgreSQL by running the following command:

   sudo apt install postgresql -y

3. Stop PostgreSQL to make configuration changes by running the following commands:

   sudo systemctl disable postgresql

   sudo systemctl stop postgresql

4. Go to the /etc/postgresql/15/main directory, open the pg_hba.conf file for editing and add the following lines to the replication section:

   host replication all <IP address of the primary node> trust

   host replication all <IP address of the backup node> trust

5. In the /etc/postgresql/15/main directory, open the postgresql.conf file and change the value of the wal_level setting while commenting out the unix_socket_directories line as follows:

   wal_level = replica

   #unix_socket_directories

6. Go to the /etc/parsec directory, open the mswitch.conf file for editing and change the value of the zero_if_notfound parameter to yes to connect to the databases.
7. Switch to the backup node and configure PostgreSQL by repeating all the steps above.

The PostgreSQL database connection is configured. Proceed to create databases for the user identity service components.

To create databases for user information:

1. Connect to the primary node and start PostgreSQL by running the following command:

   sudo systemctl start postgresql

2. Connect to the PostgreSQL console by running the following command:

   sudo -u postgres psql

3. Create the 'uaws' user that will be used by the user identity service components:

   CREATE USER uaws WITH PASSWORD '<password>';

4. Create a user database for the MapApp component:
   1. Create a database named usermpp by running the following query:

      CREATE DATABASE usermap;

   2. Grant the uaws user permissions to manage the usermap database:

      ALTER DATABASE usermap OWNER TO uaws;

   3. Switch to the created usermap database by running the following query:

      \c usermap;

   4. In the usermap database, create a table named log_events for user event logging by running the following query:

      CREATE TABLE log_events (id SERIAL PRIMARY KEY, userName VARCHAR(255) NOT NULL, domain VARCHAR(255) NOT NULL, ipAddress VARCHAR(15) NOT NULL, addTime TIMESTAMP NOT NULL, updateTime TIMESTAMP, expiryTime TIMESTAMP, status VARCHAR(16) NOT NULL, receivedTime TIMESTAMP, UNIQUE (userName, domain, ipAddress));

      Description of fields in the log_events table

      • id is the unique ID of the record.
      • userName is the user name.
      • domain is the domain.
      • ipAddress is the IPv4 address.
      • addTime is the date and time when the record was created.
      • updatedTime is the date and time when the record was updated (can be NULL).
      • expiryTime is the expiration date and time of the record (can be NULL).
      • status is the status of the session.
      • receivedTime is the date and time when the event was recorded in the database, in UTC format (can be NULL).

   5. Grant the uaws user permissions to manage the log_events table:

      ALTER DATABASE public.log_events OWNER TO uaws;

5. Create a database of LDAP user groups for the GroupApp component:
   1. Create a database named groupapp by running the following query:

      CREATE DATABASE groupapp;

   2. Grant the uaws user permissions to manage the groupapp database:

      ALTER DATABASE groupapp OWNER TO uaws;

   3. Switch to the created groupapp database by running the following query:

      \c groupapp;

   4. In the groupapp database, do the following:
      • Create a table named groups for information about LDAP user groups by running the following query:

        CREATE TABLE groups(id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, dn VARCHAR(255), status VARCHAR(15) NOT NULL, lastRequestTime TIMESTAMP NOT NULL, updatedTime TIMESTAMP, createdTime TIMESTAMP, deletedTime TIMESTAMP);

        Description of fields in the groups table

        • id is the unique ID of the record.
        • name is the group name in the <group name>@<domain> format.
        • dn is the group name in the <unique name> - CN=TestGroup6,OU=Test,DC=test,DC=ru format.
        • status is the status of the group.
        • lastRequestTime is the date and time when the information about the group was last queried.
        • updatedTime is the date and time when the record was updated (can be NULL).
        • createdTime is the date and time when the record was created (can be NULL).
        • deletedTime is the date and time when the group was deleted from the domain controller (can be NULL).
      • Create a table named users for information about LDAP users by running the following query:

        CREATE TABLE users(id SERIAL PRIMARY KEY, username VARCHAR(255) NOT NULL, samaccountname VARCHAR(255) NOT NULL, groupId INTEGER, status VARCHAR(15) NOT NULL, updatedTime TIMESTAMP, createdTime TIMESTAMP, deletedTime TIMESTAMP);

        Description of fields in the users table

        • id is the unique ID of the record.
        • username is the user name.
        • samaccountname is the name of the user account.
        • groupId is ID of the group (from groups.id) that the user is member of.
        • status is the status of the user.
        • updatedTime is the date and time when the record was updated (can be NULL).
        • createdTime is the date and time when the user was added to the group (can be NULL).
        • deletedTime is the date and time when the user was removed from the group (can be NULL).
   5. Grant the uaws user permissions to manage the groups table:

      ALTER TABLE public.groups OWNER TO uaws;

   6. Grant the uaws user permissions to manage the users table:

      ALTER TABLE public.users OWNER TO uaws;

6. Create a 'replicator' user for replicating databases among nodes:

   CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD <password>;

7. Exit PostgreSQL on the primary node:

   \q

8. Switch to the backup node.
9. Duplicate the databases created on the primary node to the backup node by running the following commands:

   sudo rm -rf /var/lib/pgsql/tmp/PGSQL.lock

   sudo -u postgres sh -c "rm -rf /var/lib/postgresql/*/main/*"

   sudo -u postgres pg_basebackup -h <IP address of the primary node> -D /var/lib/postgresql/15/main -P

10. Switch to the primary node and stop PostgreSQL by running the following command:

    sudo systemctl stop postgresql

The databases are prepared and can be used by the components of the user identity service.

Page top