Creating user databases on a single device

Expand all | Collapse all

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

To configure PostgreSQL databases:

1. Install PostgreSQL by running the following command:

   sudo apt install postgresql -y

2. 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.
3. Restart PostgreSQL by running the following command:

   sudo systemctl restart postgresql

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

   sudo -u postgres psql

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

   CREATE USER uaws WITH PASSWORD '<password>';

6. 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;

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

8. Quit PostgreSQL:

   \q

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

Page top