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:

   sudo apt install postgresql -y

3. Stop PostgreSQL to make configuration changes:

   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:

   sudo systemctl start postgresql

2. Connect to the PostgreSQL console:

   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.

   Commands for creating the MapApp database

   CREATE DATABASE usermap;

   ALTER DATABASE usermap OWNER TO uaws;

   \c usermap;

   CREATE SEQUENCE public.changeseq

   as bigint

   START WITH 1

   INCREMENT BY 1

   NO MINVALUE

   NO MAXVALUE

   CACHE 1;

   ALTER TABLE public.changeseq OWNER TO uaws;

   CREATE TABLE public.log_events (

   id bigint NOT NULL,

   username character varying(255) NOT NULL,

   domain character varying(255) NOT NULL,

   ipaddress character varying(15) NOT NULL,

   addtime timestamp without time zone NOT NULL,

   updatetime timestamp without time zone,

   expirytime timestamp without time zone,

   status character varying(16) NOT NULL,

   receivedtime timestamp without time zone,

   changeseq bigint NOT NULL

   )

   WITH (fillfactor='90', autovacuum_vacuum_scale_factor='0.005', autovacuum_vacuum_threshold='1000', autovacuum_analyze_scale_factor='0.005', autovacuum_analyze_threshold='1000', autovacuum_vacuum_cost_limit='8000', autovacuum_vacuum_cost_delay='1');

   ALTER TABLE public.log_events OWNER TO uaws;

   CREATE SEQUENCE public.log_events_changeseq_seq

   as bigint

   START WITH 1

   INCREMENT BY 1

   NO MINVALUE

   NO MAXVALUE

   CACHE 1;

   ALTER TABLE public.log_events_changeseq_seq OWNER TO uaws;

   ALTER SEQUENCE public.log_events_changeseq_seq OWNED BY public.log_events.changeseq;

   CREATE SEQUENCE public.log_events_id_seq

   as bigint

   START WITH 1

   INCREMENT BY 1

   NO MINVALUE

   NO MAXVALUE

   CACHE 1;

   ALTER TABLE public.log_events_id_seq OWNER TO uaws;

   ALTER SEQUENCE public.log_events_id_seq OWNED BY public.log_events.id;

   ALTER TABLE ONLY public.log_events ALTER COLUMN id SET DEFAULT nextval('public.log_events_id_seq'::regclass);

   ALTER TABLE ONLY public.log_events ALTER COLUMN changeseq SET DEFAULT nextval('public.log_events_changeseq_seq'::regclass);

   ALTER TABLE ONLY public.log_events

   ADD CONSTRAINT log_events_pkey PRIMARY KEY (id);

   ALTER TABLE ONLY public.log_events

   ADD CONSTRAINT log_events_uq_user_domain_ip UNIQUE (username, domain, ipaddress) WITH (fillfactor='90');

   CREATE INDEX log_events_changeseq_idx ON public.log_events USING btree (changeseq);

   CREATE INDEX log_events_ip_upd_desc_idx ON public.log_events USING btree (ipaddress, updatetime DESC) WITH (fillfactor='90');

   CREATE INDEX log_events_ipaddress_idx ON public.log_events USING btree (ipaddress);

   CREATE INDEX log_events_receivedtime_idx ON public.log_events USING btree (receivedtime) WITH (fillfactor='90');

   CREATE INDEX log_events_status_idx ON public.log_events USING btree (status);

5. Create a database of LDAP user groups for the GroupApp component.

   Commands for creating the GroupApp database

   CREATE DATABASE groupapp;

   ALTER DATABASE groupapp OWNER TO uaws;

   \c groupapp;

   CREATE SEQUENCE public.changeseq

   as bigint

   START WITH 1

   INCREMENT BY 1

   NO MINVALUE

   NO MAXVALUE

   CACHE 1;

   ALTER TABLE public.changeseq OWNER TO uaws;

   CREATE SEQUENCE public.groupchilds_id_seq

   as bigint

   START WITH 1

   INCREMENT BY 1

   NO MINVALUE

   NO MAXVALUE

   CACHE 1;

   ALTER TABLE public.groupchilds_id_seq OWNER TO uaws;

   CREATE TABLE public.groupchilds (

   id bigint DEFAULT nextval('public.groupchilds_id_seq'::regclass) NOT NULL,

   dn character varying(255) NOT NULL,

   whenchanged character varying(30),

   parentid bigint NOT NULL,

   domain character varying(255)

   );

   ALTER TABLE public.groupchilds OWNER TO uaws;

   CREATE TABLE public.groups (

   id bigint NOT NULL,

   name character varying(255) NOT NULL,

   dn character varying(255),

   status character varying(15) NOT NULL,

   lastrequesttime timestamp without time zone NOT NULL,

   updatedtime timestamp without time zone,

   createdtime timestamp without time zone,

   deletedtime timestamp without time zone,

   whenchanged character varying(30),

   domain character varying(255)

   )

   WITH (autovacuum_vacuum_threshold='1000', autovacuum_freeze_min_age='10000000', autovacuum_enabled='true', autovacuum_vacuum_scale_factor='0.02', autovacuum_analyze_scale_factor='0.01');

   ALTER TABLE public.groups OWNER TO uaws;

   CREATE SEQUENCE public.groups_id_seq

   as bigint

   START WITH 1

   INCREMENT BY 1

   NO MINVALUE

   NO MAXVALUE

   CACHE 1;

   ALTER TABLE public.groups_id_seq OWNER TO uaws;

   ALTER SEQUENCE public.groups_id_seq OWNED BY public.groups.id;

   CREATE TABLE public.users (

   id bigint NOT NULL,

   username character varying(255) NOT NULL,

   samaccountname character varying(255) NOT NULL,

   groupid bigint,

   status character varying(15) NOT NULL,

   updatedtime timestamp without time zone,

   createdtime timestamp without time zone,

   deletedtime timestamp without time zone,

   changeseq bigint DEFAULT nextval('public.changeseq'::regclass) NOT NULL,

   domain character varying(255),

   whenchanged character varying(30)

   )

   WITH (autovacuum_freeze_min_age='10000000', autovacuum_enabled='true', autovacuum_vacuum_scale_factor='0.01', autovacuum_analyze_scale_factor='0.005', autovacuum_vacuum_threshold='5000', autovacuum_analyze_threshold='5000', autovacuum_vacuum_cost_limit='2000');

   ALTER TABLE public.users OWNER TO uaws;

   CREATE SEQUENCE public.users_id_seq

   START WITH 1

   INCREMENT BY 1

   NO MINVALUE

   NO MAXVALUE

   CACHE 1;

   ALTER TABLE public.users_id_seq OWNER TO uaws;

   ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;

   ALTER TABLE ONLY public.groups ALTER COLUMN id SET DEFAULT nextval('public.groups_id_seq'::regclass);

   ALTER TABLE ONLY public.users ALTER COLUMN id SET DEFAULT nextval('public.users_id_seq'::regclass);

   ALTER TABLE ONLY public.groupchilds

   ADD CONSTRAINT groupchilds_pkey PRIMARY KEY (id);

   ALTER TABLE ONLY public.groups

   ADD CONSTRAINT groups_pkey PRIMARY KEY (id);

   ALTER TABLE ONLY public.users

   ADD CONSTRAINT test_constraint_1 UNIQUE (groupid, username);

   ALTER TABLE ONLY public.users

   ADD CONSTRAINT users_pkey PRIMARY KEY (id);

   CREATE UNIQUE INDEX groupchilds_dn_parent ON public.groupchilds USING btree (dn, parentid);

   CREATE INDEX idx_groups_name ON public.groups USING btree (name);

   CREATE INDEX idx_users_deletedtime_deleted ON public.users USING btree (deletedtime) WHERE ((status)::text = 'deleted'::text);

   CREATE INDEX idx_users_group_change_status ON public.users USING btree (groupid, changeseq DESC, status) INCLUDE (samaccountname, status);

   CREATE INDEX users_actual_changeseq_desc ON public.users USING btree (changeseq DESC) INCLUDE (groupid, samaccountname) WHERE ((status)::text = 'actual'::text);

   CREATE INDEX users_actual_changeseq_group_idx ON public.users USING btree (changeseq DESC, groupid) INCLUDE (samaccountname) WHERE ((status)::text = 'actual'::text);

   CREATE INDEX users_actual_gid_changeseq_desc ON public.users USING btree (groupid, changeseq DESC) INCLUDE (samaccountname) WHERE ((status)::text = 'actual'::text);

   CREATE INDEX users_domain_whenchanged_desc ON public.users USING btree (domain, whenchanged DESC);

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:

   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:

    sudo systemctl stop postgresql

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