Creating database objects for PostgreSQL

Database prerequisites

• The database cluster must already exist.

• This procedure must be performed by a DBA user.

Let's start

• Copy the DOIM_HOME/pkg-repo/pkg-base/db/ghibli-distro-db-<version>.tar.gz to the database host or to a client that can connect to the database.

• Extract the package ghibli-distro-db-<version>.tar.gz.

• Run the steps described in the following sections.

Please notice that some of the SQL scripts require parameters whose value must be the same as the value specified in the Domain Master Configuration Reference (DMCFG). Here is a quick cross-reference between the two sets of parameters:

SQL script parameter	DMCFG parameter
ADMIN_USER	NONE. This is an arbitrary user that will be created by the scripts and will be granted full administrative rights on both CEMAN Core schema (ceman_db_schema in DMCFG) and CEMAN IAM schema (KEYCLOAK_DB_SCHEMA in DMCFG).
APP_USER	CEMAN_DB_USR
APP_PASS	CEMAN_DB_PSW
KEYCLOAK_DB_SCHEMA	KEYCLOAK_DB_SCHEMA
CEMAN_DB_SCHEMA	ceman_db_schema

1. Create the CEMAN tablespaces

(01_CEMAN_PREPARE_TBLS.sql)

psql -h <HOST> -p <PORT> -U <DBA_USER> \ 
-v tbls_d1_ge_data=d1_ge_data \  
-v tbls_d1_ge_data_path="'/opt/dataone/data/tbls/d1_ge_data'" \
-v tbls_d1_ge_indexes=d1_ge_indexes \
-v tbls_d1_ge_indexes_path="'/opt/dataone/data/tbls/d1_ge_indexes'" \ 
-v tbls_d1_ru_data=d1_ru_data \
-v tbls_d1_ru_data_path="'/opt/dataone/data/tbls/d1_ru_data'" \  
-v tbls_d1_ru_indexes=d1_ru_indexes \
-v tbls_d1_ru_indexes_path="'/opt/dataone/data/tbls/d1_ru_indexes'" \
-v tbls_d1_hi_data=d1_hi_data \
-v tbls_d1_hi_data_path="'/opt/dataone/data/tbls/d1_hi_data'" \
-v tbls_d1_hi_indexes=d1_hi_indexes \
-v tbls_d1_hi_indexes_path="'/opt/dataone/data/tbls/d1_hi_indexes'" \
-f 01_CEMAN_PREPARE_TBLS.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <DBA_USER> with the username of a DBA user

This is the list of the required space-separated script parameters:

• tbls_d1_ge_data: tablespace name for generic data

• tbls_d1_ge_data_path: absolute datafile path for generic data tablespace

• tbls_d1_ge_indexes: tablespace name for generic indexes

• tbls_d1_ge_indexes_path: absolute datafile path for generic indexes tablespace

• tbls_d1_ru_data: tablespace name for runtime data

• tbls_d1_ru_data_path: absolute datafile path for runtime data tablespace

• tbls_d1_ru_indexes: tablespace name for runtime indexes

• tbls_d1_ru_indexes_path: absolute datafile path for runtime indexes tablespace

• tbls_d1_hi_data: tablespace name for historic data

• tbls_d1_hi_data_path: absolute datafile path for historic data tablespace

• tbls_d1_hi_indexes: tablespace name for historic indexes

• tbls_d1_hi_indexes_path: absolute datafile path for historic indexes tablespace

Amend all parameters according to your specific PostgreSQL installation.

If the output shows a message like "WARNING: there is no transaction in progress", it could be related to auto commit configuration.

2. Create the CEMAN users

(02_CEMAN_PREPARE_USERS.sql)

psql -h <HOST> -p <PORT> -U <DBA_USER> \
-v admin_user=<ADMIN_USER> \
-v admin_pass="'<ADMIN_PWD>'" \
-v app_user=<APP_USER> \
-v app_pass="'<APP_PASS>'" \
-v tbls_d1_ge_data=d1_ge_data \
-v tbls_d1_ge_indexes=d1_ge_indexes \ 
-v tbls_d1_ru_data=d1_ru_data \ 
-v tbls_d1_ru_indexes=d1_ru_indexes \
-v tbls_d1_hi_data=d1_hi_data \ 
-v tbls_d1_hi_indexes=d1_hi_indexes \ 
-f 02_CEMAN_PREPARE_USERS.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <DBA_USER> with the username of a DBA user

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <ADMIN_PWD> with the password for the owner of the objects

• <APP_USER> with the username used by the application to connect to the database

• <APP_PASS> with the password for the application user

Amend all parameters according to your specific PostegreSQL installation.

3. Create the CEMAN database

(03_CEMAN_CREATE_DB.sql)

psql -h <HOST> -p <PORT> -U <DBA_USER> \
-v database_name=<DB_NAME> \
-v admin_user=<ADMIN_USER> \
-v tbls_d1_ge_data=d1_ge_data \
-f 03_CEMAN_CREATE_DB.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <DBA_USER> with the username of a DBA user

• <DB_NAME> with the name of the database

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

Amend all parameters according to your specific PostegreSQL installation.

4. Create the CEMAN schema

(04_CEMAN_CREATE_SCHEMA.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \ 
-v database_name=<DB_NAME> \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER> \ 
-v schema=<CEMAN_DB_SCHEMA> \ 
-v tbls_d1_ge_data=d1_ge_data \ 
-v tbls_d1_ge_indexes=d1_ge_indexes \ 
-v tbls_d1_ru_data=d1_ru_data \ 
-v tbls_d1_ru_indexes=d1_ru_indexes \ 
-v tbls_d1_hi_data=d1_hi_data \ 
-v tbls_d1_hi_indexes=d1_hi_indexes \ 
-f 04_CEMAN_CREATE_SCHEMA.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <DB_NAME> with the name of the database

• <APP_USER> with the username used by the application to connect to the database

• <CEMAN_DB_SCHEMA> with the name of the schema

Amend all parameters according to your specific PostgreSQL installation.

5. Create CEMAN workflow objects

(05_CEMAN_CREATE_SCHEMA_WORKFLOW.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v database_name=<DB_NAME> \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER> \ 
-v schema=<CEMAN_DB_SCHEMA> \ 
-v tbls_d1_ge_data=d1_ge_data \ 
-v tbls_d1_ge_indexes=d1_ge_indexes \ 
-v tbls_d1_ru_data=d1_ru_data \ 
-v tbls_d1_ru_indexes=d1_ru_indexes \ 
-v tbls_d1_hi_data=d1_hi_data \ 
-v tbls_d1_hi_indexes=d1_hi_indexes \ 
-f 05_CEMAN_CREATE_SCHEMA_WORKFLOW.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <DB_NAME> with the name of the database

• <APP_USER> with the username used by the application to connect to the database

• <CEMAN_DB_SCHEMA> with the name of the schema

Amend all parameters according to your specific PostgreSQL installation.

6. Load CEMAN initial data

(06_CEMAN_INITIAL_DML.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v schema=<CEMAN_DB_SCHEMA> \ 
-f 06_CEMAN_INITIAL_DML.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <DB_NAME> with the name of the database

• <CEMAN_DB_SCHEMA> with the name of the schema

Amend all parameters according to your specific PostgreSQL installation.

7. Assign CEMAN permissions to the application user

(07_CEMAN_GRANT_USER.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v database_name=<DB_NAME> \ 
-v schema=<CEMAN_DB_SCHEMA> \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER> \ 
-f 07_CEMAN_GRANT_USER.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <DB_NAME> with the name of the database

• <CEMAN_DB_SCHEMA> with the name of the schema

• <APP_USER> with the username used by the application to connect to the database

Amend all parameters according to your specific PostgreSQL installation.

8. Create the KEYCLOAK tablespaces

(08_IAM_PREPARE_TBLS.sql)

psql -h <HOST> -p <PORT> -U <DBA_USER> \
-v tbls_kc_ge_data=kc_ge_data \ 
-v tbls_kc_ge_data_path="'/opt/dataone/data/tbls/d1_kc_data'" \ 
-f 08_IAM_PREPARE_TBLS.sql \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER> 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <DBA_USER> with the username of a DBA user

This is the list of the required space-separated script parameters:

• tbls_kc_ge_data: tablespace name for keycloak data

• tbls_kc_ge_data_path: absolute datafile path for keycloak data tablespace

Amend all parameters according to your specific PostgreSQL installation.

If the output shows a message like "WARNING: there is no transaction in progress", it could be related to auto commit configuration.

9. Create the KEYCLOAK schema

(09_IAM_CREATE_SCHEMA.sql)

 psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v tbls_kc_ge_data=kc_ge_data \ 
-v admin_user=<ADMIN_USER> \ 
-v app_user=<APP_USER> \ 
-v schema=<KEYCLOAK_DB_SCHEMA> \ 
-f 09_IAM_CREATE_SCHEMA.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <DB_NAME> with the name of the database

• <APP_USER> with the username used by the application to connect to the database

• <KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema

Amend all parameters according to your specific PostgreSQL installation.

10. Create the KEYCLOAK schema authorization

(10_IAM_CREATE_SCHEMA_AUTHORIZATION.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v keycloak_schema=<KEYCLOAK_DB_SCHEMA> \ 
-v dataone_schema=<CEMAN_DB_SCHEMA> \ 
-v app_user=<APP_USER> \ 
-f 10_IAM_CREATE_SCHEMA_AUTHORIZATION.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <DB_NAME> with the name of the database

• <APP_USER> with the username used by the application to connect to the database

• <KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema

• <CEMAN_DB_SCHEMA> with the name of the schema

Amend all parameters according to your specific PostgreSQL installation.

11. Assign KEYCLOAK permissions to the application user

(11_IAM_GRANT_USER.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v database_name=<DB_NAME> \ 
-v schema=<KEYCLOAK_DB_SCHEMA> \ 
-v app_user=<APP_USER> \ 
-f 11_IAM_GRANT_USER.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <DB_NAME> with the name of the database

• <KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema

• <APP_USER> with the username used by the application to connect to the database

Amend all parameters according to your specific PostgreSQL installation.

12. Create the KEYCLOAK Triggers

(12_IAM_ADD_TRIGGERS.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v keycloak_schema=<KEYCLOAK_DB_SCHEMA> \ 
-v dataone_schema=<CEMAN_DB_SCHEMA> \ 
-v app_user=<APP_USER> \ 
-v tbls_kc_ge_data=kc_ge_data \ 
-f 12_IAM_ADD_TRIGGERS.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <DB_NAME> with the name of the database

• <KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema

• <CEMAN_DB_SCHEMA> with the name of the schema

• <APP_USER> with the username used by the application to connect to the database

Amend all parameters according to your specific PostgreSQL installation.

13. Load KEYCLOAK initial data

(13_IAM_INITIAL_DML.sql)

psql -h <HOST> -p <PORT> -U <ADMIN_USER> -d <DB_NAME> -e \
-v keycloak_schema=<KEYCLOAK_DB_SCHEMA> \ 
-f 13_IAM_INITIAL_DML.sql 

Replace:

• <HOST> with the PostgreSQL hostname or IP

• <PORT> with the PostgreSQL port

• <ADMIN_USER> with the username of the owner of the objects (tables, views, …)

• <DB_NAME> with the name of the database

• <KEYCLOAK_DB_SCHEMA> with the name of the keycloak schema

Amend all parameters according to your specific PostgreSQL installation.