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:

   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:

   sudo systemctl restart postgresql

4. Connect to the PostgreSQL console:

   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.

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

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

8. Quit PostgreSQL:

   \q

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