Skip to content

IVAAP Database Administration



IVAAP Deployment Operations Guide
Database Administration




IVAAP 2025.1

Introduction

This document serves as a guide on initializing and managing the PostgreSQL database required by IVAAP. Basic relational database knowledge is assumed.

A chapter is dedicated to each of the various options INT is familiar with, but there may be other options that are still compatible with IVAAP.

IVAAP PostgreSQL Requirements

IVAAP 2025.1 supports the latest Postgres version 15.x. There are several extensions used by IVAAP. These extensions are required, or else IVAAP may not function properly.

  1. CITEXT
  2. FUZZYSTRMATCH
  3. PG_BUFFERCACHE
  4. PG_STAT_STATEMENTS
  5. PLPGSQL
  6. POSTGIS
  7. POSTGIS_TIGER_GEOCODER
  8. POSTGIS_TOPOLOGY
  9. UUID-OSSP

Later sections of this document will outline how to enable these extensions, if applicable.

An admin user by the name of ivaapserver is required in PostgreSQL in order to connect to IVAAP. If creating a cloud managed Postgres resource, it is best to pre-configure this admin user during the creation process. If this database is going into an existing Postgres resource, refer to the documentation for the respective resource type as well as PostgreSQL documentation for information on how to create and configure an admin user with the name ivaapserver.

In all deployment scenarios outlined in this document, a compatible command line PostgreSQL client with access to the Postgres database will be required. Access to the pg_dump utility is also recommended if the need to manually create a database dump arises. All references in this document for the PostgreSQL client will be CLI commands from a Linux server. Equivalent commands for your client will need to be used if your client is not Linux.

If the INT provided Postgres container image is used for deployment, all of these requirements will all already be available inside the image.

Azure Managed Postgres

Azure Resource Setup

When creating a resource for the PostgreSQL database, ensure to select Azure Database for PostgreSQL Flexible Server as the resource type. Follow Azure documentation for configuration steps to meet your own requirements. There are only three main configuration requirements for IVAAP:

  1. PostgreSQL 15 version
  2. Admin username must be ivaapserver
  3. Authentication method must be PostgreSQL authentication only

In the case of Azure managed Postgres, it is easiest to go ahead and set the Admin username to ivaapserver. If your deployment needs require a different admin username, or if you are deploying the database to an existing Azure managed Postgres service - refer to Azure and PostgreSQL documentation for configuring an admin user named ivaapserver in your resource.

If your deployment needs require Entra ID for additional authentication method, refer to Azure documentation for the additional requirements to meet this need. Additional steps will be required for access to the database using Entra ID that will not be outlined in this document.

Configuring Extensions inside Azure

With Azure’s managed postgres service, extensions used by postgres aren’t enabled out of the box. The required IVAAP extensions will need to be enabled in Azure Portal before loading the schema.

In Azure Portal, select your Azure managed Postgres service, then go to Settings > Server Parameters. In the filter search bar, filter for azure.extensions. Next to azure.extensions will be a Values dropbox. In this dropbox, select the following extensions:

  1. CITEXT
  2. PG_BUFFERCACHE
  3. PG_STAT_STATEMENTS
  4. PLPGSQL
  5. POSTGIS
  6. POSTGIS_TIGER_GEOCODER
  7. POSTGIS_TOPOLOGY
  8. UUID-OSSP
  9. FUZZYSTRMATCH

Ensure to save the changes once complete.

Create Database for IVAAP in Azure

For this step, a PostgreSQL client with access to the Azure managed Postgres service is required. From the client, run the following command to access Postgres, and enter your admin user password when prompted:

# Plug in actual values for items in red
psql -h database-hostname -U ivaapserver -d postgres

# Example:
user@linux:~$ psql -h postgres15.postgres.database.azure.com -U ivaapserver -d postgres
Password for user ivaapserver: 
psql (15.9 (Debian 15.9-0+deb12u1), server 15.8)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=>

From here, you are now inside the postgres managed service in the default database named postgres. Next, the database to be used by IVAAP will need to be created. The schema for IVAAP has references to the database being named int, however it is not required here. In this case, this database name will only be used as reference for connection.

Run the following command inside postgres to create the new database:

CREATE DATABASE dbname;

# Example:
postgres=> CREATE DATABASE ivaapdb;
CREATE DATABASE

# Check that the new database exists
postgres=> \l

Below are two examples of how to connect to this new database:

# Connect to database while already inside Postgres:
\c dbname

# Example:
postgres=> \c ivaapdb
psql (15.9 (Debian 15.9-0+deb12u1), server 15.8)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "ivaapdb" as user "ivaapserver".
ivaapdb=> 

# Connect to new database from client
psql -h database-hostname -U ivaapserver -d dbname

# Example:
user@linux:~$ psql -h postgres15.postgres.database.azure.com -U ivaapserver -d ivaapdb
Password for user ivaapserver: 
psql (15.9 (Debian 15.9-0+deb12u1), server 15.8)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

ivaapdb=>

The database is now ready for IVAAP schema.

Create Schema and Extensions for IVAAP in Azure

A few postgres commands will need to be run before the IVAAP schema can be loaded. Run the following commands inside the postgres database for IVAAP:

CREATE SCHEMA int;
CREATE SCHEMA tiger;
CREATE SCHEMA tiger_data;
CREATE SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder WITH SCHEMA tiger;
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;

Expected output:

ivaapdb=> CREATE SCHEMA int;
CREATE SCHEMA tiger;
CREATE SCHEMA tiger_data;
CREATE SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder WITH SCHEMA tiger;
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION

Loading IVAAP Schema in Azure

INT will provide a starting database dump for fresh deployment. This will contain the schema for IVAAP. Upload this dump sql file to your client with access to the postgres database. The following command can be used to load the schema:

psql -f path/to/dump/file.sql -h database-host -U ivaapserver -d dbname

# Example:
user@linux:~$ psql -f ivaap-postgres-vanilla-pre-migration-2024-11-13.sql -h postgres15.postgres.database.azure.com -U ivaapserver -d ivaapdb

Once the command is executed, you will see the schema being loaded into postgres. It is important to observe this command output and ensure that there are no errors. You may see some “schema already exists” errors. This is okay, as these schemas were created in earlier steps.

Below is an example of these errors which are okay to ignore:

psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:23: ERROR:  schema "int" already exists
ALTER SCHEMA
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:32: ERROR:  schema "tiger" already exists
ALTER SCHEMA
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:41: ERROR:  schema "tiger_data" already exists
ALTER SCHEMA
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:50: ERROR:  schema "topology" already exists
ALTER SCHEMA

If any other errors appear, however, please refer to the Troubleshooting section at the bottom of this document. If those steps do not help with the specific errors, consult with INT support for further assistance.

Amazon Relational Database System (RDS)

AWS Resource Setup

When creating an RDS resource for the PostgreSQL database, ensure to select PostgreSQL as the engine. Follow AWS documentation for configuration steps to meet your own requirements. There are only three main configuration requirements for IVAAP:

  1. PostgreSQL 15.x version
  2. Master username must be ivaapserver
  3. Authentication method must be PostgreSQL authentication only

In the case of RDS, it is easiest to go ahead and set the Master username to ivaapserver. If your deployment needs require a different master username, or if you are deploying the database to an existing RDS instance - refer to AWS and PostgreSQL documentation for configuring an admin user named ivaapserver in your RDS instance.

Additionally, this section will assume database authentication type Password authentication. If IAM or Kerberos are required to meet your needs, refer to AWS and PostgreSQL documentation. These additions will need to be reflected in commands used to access the database.

Create Database for IVAAP in RDS

For this step, a PostgreSQL client with access to the RDS resource is required. From the client, run the following command to access Postgres, and enter your admin user password when prompted:

# Plug in actual values for items in red
psql -h database-hostname -U ivaapserver -d postgres

# Example:
user@linux:~$ psql -h postgres15.cday5nzud2oc.us-east-2.rds.amazonaws.com -U ivaapserver -d postgres
Password for user ivaapserver: 
psql (15.9 (Debian 15.9-0+deb12u1), server 15.8)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=>

From here, you are now in the default database named postgres inside the RDS host. Next, the database to be used by IVAAP will need to be created. The schema for IVAAP has references to the database being named int, however it is not required here. In this case, this database name will only be used as reference for connection.

Run the following command inside postgres to create the new database:

CREATE DATABASE dbname;

# Example:
postgres=> CREATE DATABASE ivaapdb;
CREATE DATABASE

# Check that the new database exists
postgres=> \l

Below are two examples of how to connect to this new database:

# Connect to database while already inside Postgres:
\c dbname

# Example:
postgres=> \c ivaapdb
psql (15.9 (Debian 15.9-0+deb12u1), server 15.8)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "ivaapdb" as user "ivaapserver".
ivaapdb=> 

# Connect to new database from client
psql -h database-hostname -U ivaapserver -d dbname

# Example:
user@linux:~$ psql -h postgres15.cday5nzud2oc.us-east-2.rds.amazonaws.com -U ivaapserver -d ivaapdb
Password for user ivaapserver: 
psql (15.9 (Debian 15.9-0+deb12u1), server 15.8)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

ivaapdb=>

The database is now ready for IVAAP schema.

Create Schema and Extensions for IVAAP in RDS

A few postgres commands will need to be run before the IVAAP schema can be loaded. Run the following commands inside the postgres database for IVAAP:

CREATE SCHEMA int;
CREATE SCHEMA tiger;
CREATE SCHEMA tiger_data;
CREATE SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder WITH SCHEMA tiger;
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;

Expected output:

ivaapdb=> CREATE SCHEMA int;
CREATE SCHEMA tiger;
CREATE SCHEMA tiger_data;
CREATE SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder WITH SCHEMA tiger;
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION

Loading IVAAP Schema in RDS

INT will provide a starting database dump for fresh deployment. This will contain the schema for IVAAP. Upload this dump sql file to your client with access to the postgres database. The following command can be used to load the schema:

psql -f path/to/dump/file.sql -h database-host -U ivaapserver -d dbname

# Example:
user@linux:~$ psql -f ivaap-postgres-vanilla-pre-migration-2024-11-13.sql -h postgres15.cday5nzud2oc.us-east-2.rds.amazonaws.com -U ivaapserver -d ivaapdb

Once the command is executed, you will see the schema being loaded into postgres. It is important to observe this command output and ensure that there are no errors. You may see some “schema already exists” errors. This is okay, as these schemas were created in earlier steps. Below is an example of these errors which are okay to ignore:

psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:23: ERROR:  schema "int" already exists
ALTER SCHEMA
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:32: ERROR:  schema "tiger" already exists
ALTER SCHEMA
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:41: ERROR:  schema "tiger_data" already exists
ALTER SCHEMA
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:50: ERROR:  schema "topology" already exists
ALTER SCHEMA

If any other errors appear, however, please refer to the Troubleshooting section at the bottom of this document. If those steps do not help with the specific errors, consult with INT support for further assistance.

Local Native Postgres

In K3s deployments, running postgres directly on the host server is an option. This requires two dependencies to be installed from either apt or yum, depending on your distribution: PostgreSQL and PostGIS. Refer to PostgreSQL: Downloads and Getting Started | PostGIS official documentation for installation steps for your environment.

User Setup

Once PostgreSQL and PostGIS is installed on the host, connect to the PostgreSQL instance to setup the ivaapserver user with sudo -i -u postgres psql. 

user@linux:~$ sudo -i -u postgres psql
psql (15.10 (Ubuntu 15.10-0ubuntu0.24.04.1))
Type "help" for help.

postgres=#
Once inside, run the following to create the ivaapserver user. 
DO
$do$
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'ivaapserver')
    THEN
        RAISE NOTICE 'User "ivaapserver" already exists. Skipping.';
    ELSE
        CREATE USER ivaapserver WITH ENCRYPTED PASSWORD '<your-password-here>';
    END IF;
END
$do$;

Then alter the ivaapserver user's roles. 

ALTER ROLE ivaapserver WITH SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS;

Exit the database now with \q.

Create Database and Extensions

Re-authenticate with the database again, but this time with the ivaapserver user. Then, we will create the database for IVAAP, switch to that database, and create the necessary extensions. 

# Authenticate with ivaapserver user
user@linux:~$ psql -h localhost -d postgres -U ivaapserver -p 5432
Password for user ivaapserver:
psql (15.10 (Ubuntu 15.10-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=# 

# Create the Database
postgres=# CREATE database ivaap;
CREATE DATABASE

# Switch to the new database with '\c'
postgres=# \c ivaap
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
You are now connected to database "ivaap" as user "ivaapserver".

# Create the schemas and extensions
ivaap=# CREATE SCHEMA int;
CREATE SCHEMA tiger;
CREATE SCHEMA tiger_data;
CREATE SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder WITH SCHEMA tiger;
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
ivaap=#

Exit out of the database again with \q.

Load IVAAP Schema

It's time to load the database schema with the following command: 

psql -f <dump-filename>.psql -h localhost -d ivaap -U ivaapserver -p 5432
The output should look something like this: 
user@linux:~$ psql -f ivaap-dump.psql -h localhost -d ivaap -U ivaapserver -p 5432
Password for user ivaapserver:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
psql:ivaap-dump.psql:25: ERROR:  schema "int" already exists
ALTER SCHEMA
psql:ivaap-dump.psql:43: ERROR:  schema "tiger" already exists
psql:ivaap-dump.psql:52: ERROR:  schema "tiger_data" already exists
psql:ivaap-dump.psql:61: ERROR:  schema "topology" already exists
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
CREATE TYPE
ALTER TYPE
CREATE TYPE
ALTER TYPE
SET
SET
CREATE TABLE
ALTER TABLE
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
...
...
...
...
...

The four errors above are okay in this case, since we have already created those schemas in the previous step. This can happen sometimes, depending on the database dump being loaded. However, it's important to make sure no other errors occur when loading the schema.

Add K3s IP Range to pg_hba.conf

Before connecting IVAAP to the database, some modification to the pg_hba.conf and the IVAAP deployment yaml is required in order for the pod to be able to connect to the local database.

K3s uses a default IP range for the pods of 10.42.0.0/16, so we need to essentially whitelist this range PostgreSQL. To do this, we need to add one line to the bottom of the file /etc/postgresql/15/main/pg_hba.conf. Note: your path may vary depending on distro and installation method. 

host    all             all             10.42.0.0/16            scram-sha-256

Now, restart the PostgreSQL service with sudo systemctl restart postgresql, then check that you are able to authenticate to postgres using the k3s host bridge network interface: 10.42.0.1 

user@linux:~$ psql -h 10.42.0.1 -d postgres -U ivaapserver -p 5432
Password for user ivaapserver:
psql (15.10 (Ubuntu 16.10-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#

With a successful connection, this IP address can now be used for IVAAP, and set for the value of IVAAP_SERVER_ADMIN_DATABASE_HOST.

Adminserver Bash Functions

Baked into the adminserver container image, we now include a ~/.bashrc file that contains useful functions for interacting with the PostgreSQL database. Since the adminserver is already served environment variables containing PostgreSQL connection details, these same variables are used to in these functions to authenticate with the database in order to run queries.

For all of the functions below, you must shell into the adminserver pod. 

user@linux:~$ kubectl get pods -n ivaap
NAME                                                  READY   STATUS    RESTARTS   AGE
adminserver-deployment-54595bf4d4-26gn7               1/1     Running   0          34m
ivaap-activemq-deployment-58677b8fff-7s47g            1/1     Running   0          2d3h
ivaap-admin-deployment-8b6f68874-7jgcr                1/1     Running   0          25h
ivaap-backend-deployment-74f46c864d-wsrjw             12/12   Running   0          169m
ivaap-dashboard-deployment-857d77f9b9-s2v5k           1/1     Running   0          4h42m
ivaap-dashboard-publish-deployment-6555459fbf-6v2mw   1/1     Running   0          4h42m
ivaap-proxy-deployment-865f458765-jhktv               1/1     Running   0          2d3h
ivaap-scheduledtasks-deployment-58fc764744-wphvc      3/3     Running   0          2d3h
user@linux:~$ kubectl exec -it adminserver-deployment-54595bf4d4-26gn7 -n ivaap -- /bin/bash
adminserver-deployment-54595bf4d4-26gn7:/opt/ivaap/adminserver$ 

# Or with ivaap-helpers: ki exec <namespace> <pod-fuzzy-search>
user@linux:~$ ki exec ivaap adminserver
adminserver-deployment-54595bf4d4-26gn7:/opt/ivaap/adminserver$

listSuperAdmins

This function lists all super admin users for IVAAP, and inculdes the following information for each of those users:

Below is an example output of the function, but condensed slightly. Simply run listSuperAdmins to run the function. 

adminserver-deployment-54595bf4d4-26gn7:/opt/ivaap/adminserver$ listSuperAdmins
                 guid                 |                domain                | fname |   lname    |                            pword                             | company | active |       email        |     username      
--------------------------------------+--------------------------------------+-------+------------+--------------------------------------------------------------+---------+---------+--------+--------------------+---------
 dee8253a-6646-41d6-96dd-f56a8f47fe1a | bcf883a1-2103-450a-81c5-26fee1a9e528 | IVAAP | Root       | $2a$10$WOBH1Hn1nsGtfVffv.vMj.hRVTFHjysK4qiNxe6SpLOgD8Y1gL4S. | INT     | t      | ivaaproot@int.com  | ivaaproot@int.com
 d6fb33a2-9d22-405d-abfe-9c8ddb4c0457 | bcf883a1-2103-450a-81c5-26fee1a9e528 | IVAAP | SuperAdmin | $2a$10$MUWNr0GTkRP2saHGAlbUdOVH2Ka/I9upwZ5NPJIYD.uUgoqmisfHW | SLB     | t      | user@slb.com       | superadmin
(2 rows)

listSuperAdminsFull

This function is similar to listSuperAdmins, but shows all columns in the table. This can be used if additional information is needed for troubleshooting purposes, but most of the time listSuperAdmins will suffice.

adminserver-deployment-54595bf4d4-26gn7:/opt/ivaap/adminserver$ listSuperAdminsFull
                 guid                 |                domain                | fname |   lname    |                            pword                             | company |   title   |   phone    |       email        | deleted | active |     username      |        creation         |        modification        | unitsystemid | istemporary | timezoneid | temporarystartdate | temporaryenddate 
--------------------------------------+--------------------------------------+-------+------------+--------------------------------------------------------------+---------+-----------+------------+--------------------+---------+--------+-------------------+-------------------------+----------------------------+--------------+-------------+------------+--------------------+------------------
 dee8253a-6646-41d6-96dd-f56a8f47fe1a | bcf883a1-2103-450a-81c5-26fee1a9e528 | IVAAP | Root       | $2a$10$WOBH1Hn1nsGtfVffv.vMj.hRVTFHjysK4qiNxe6SpLOgD8Y1gL4S. | INT     | Root user | 7777777777 | ivaaproot@int.com  | f       | t      | ivaaproot@int.com |                         | 2025-03-21 02:35:52.576318 |              | f           |            |                    | 
 d6fb33a2-9d22-405d-abfe-9c8ddb4c0457 | bcf883a1-2103-450a-81c5-26fee1a9e528 | IVAAP | SuperAdmin | $2a$10$MUWNr0GTkRP2saHGAlbUdOVH2Ka/I9upwZ5NPJIYD.uUgoqmisfHW | SLB     |           |            | user@slb.com       | f       | t      | superadmin        | 2025-07-30 16:22:21.224 | 2025-07-30 19:36:13.031767 |              | f           |            |                    | 
(2 rows)

listAllUsers

The listAllUsers function lists all users in the database. This will include read-only users, normal users, admin users, and super admin users across all domains.

adminserver-deployment-848f9647f5-dld6s:/opt/ivaap/adminserver$ listAllUsers
                 guid                 |                domain                | fname  |  lname   | deleted | active |         email          |        username        
--------------------------------------+--------------------------------------+--------+----------+---------+--------+------------------------+------------------------
 c86e6859-ea06-4819-90f4-192a367fd15c | d4983f37-c74b-4ecb-bcb7-4118bd907708 | IVAAP  | Root     | f       | t      | ivaaproot@int.com      | ivaaproot@int.com
 bb7ccb06-4c70-4053-aff5-ae586de4d0bd | 4d7a6c4e-fffd-49c8-a381-3e596d9889e4 |        |          | f       | t      | admin-main@testing.com | admin-main@testing.com
(2 rows)

resetAllSuperAdminActive

There is the possibility of being locked out of super admin access after too many incorrect password attempts. If this happens, resetAllSuperAdminActive function can be used to set all super admin users back to active status.

adminserver-deployment-848f9647f5-dld6s:/opt/ivaap/adminserver$ listSuperAdmins
                 guid                 |                domain                | fname | lname |                            pword                             | company | deleted | active |       email       |     username      
--------------------------------------+--------------------------------------+-------+-------+--------------------------------------------------------------+---------+---------+--------+-------------------+-------------------
 c86e6859-ea06-4819-90f4-192a367fd15c | d4983f37-c74b-4ecb-bcb7-4118bd907708 | IVAAP | Root  | $2y$10$BeAuSvLjAOz3w1dh9jbiR.XCrQ.yRRqhd8NstgFkuRKMeP/B1FcRK | INT     | f       | f      | ivaaproot@int.com | ivaaproot@int.com
(1 row)
In the above code block, you can see an active status of false (f). This user has been locked out. Now below will demonstrate running resetAllSuperAdminActive - you will see UPDATE 1, indicating a table is updated. listSuperAdmins then shows active status back to true (t).

adminserver-deployment-848f9647f5-dld6s:/opt/ivaap/adminserver$ resetAllSuperAdminActive
UPDATE 1
adminserver-deployment-848f9647f5-dld6s:/opt/ivaap/adminserver$ listSuperAdmins
                 guid                 |                domain                | fname | lname |                            pword                             | company | deleted | active |       email       |     username      
--------------------------------------+--------------------------------------+-------+-------+--------------------------------------------------------------+---------+---------+--------+-------------------+-------------------
 c86e6859-ea06-4819-90f4-192a367fd15c | d4983f37-c74b-4ecb-bcb7-4118bd907708 | IVAAP | Root  | $2y$10$BeAuSvLjAOz3w1dh9jbiR.XCrQ.yRRqhd8NstgFkuRKMeP/B1FcRK | INT     | f       | t      | ivaaproot@int.com | ivaaproot@int.com
(1 row)

resetIvaapRootActive

The resetIvaapRootActive function is the same as resetAllSuperAdminActive, except it only performs the action to the default ivaaproot user.

create_random_ivaaproot_pw

The create_random_ivaaproot_pw can be used to create a randomly generated password for the default ivaaproot@int.com super admin user.

adminserver-deployment-869fd7bb46-rkv2z:/opt/ivaap/adminserver$ create_random_ivaaproot_pw
Testing PostgreSQL connection and authentication...
PostgreSQL connection successful.

SUPER ADMIN CREDENTIALS:
Username: ivaaproot@int.com
PW: coupaimsrandkola

Use this password to login to the admin client https://<FQDN>/admin
NOTE: This username and password will not work on the dashboard page, and can only be used for administration

Setting new password in PostgreSQL Database...
NOTICE:  extension "pgcrypto" already exists, skipping
CREATE EXTENSION
UPDATE 1

promoteUserToSuperAdmin

A user can easily be promoted to super admin using the promoteUserToSuperAdmin. Simply run promoteUserToSuperAdmin, and the function will prompt the user to input the username of the user they wish to promote.

Warning

It is imporant to use the value from the username field for this; not email.

The below demonstration first lists all super admins, then all users. From there, promoteUserToSuperAdmin is ran in order to promote admin-main@testing.com to super admin. Once complete, listSuperAdmins shows this user is now part of the super admin list. 

adminserver-deployment-848f9647f5-dld6s:/opt/ivaap/adminserver$ listSuperAdmins
                 guid                 |                domain                | fname | lname |                            pword                             | company | deleted | active |       email       |     username      
--------------------------------------+--------------------------------------+-------+-------+--------------------------------------------------------------+---------+---------+--------+-------------------+-------------------
 c86e6859-ea06-4819-90f4-192a367fd15c | d4983f37-c74b-4ecb-bcb7-4118bd907708 | IVAAP | Root  | $2y$10$BeAuSvLjAOz3w1dh9jbiR.XCrQ.yRRqhd8NstgFkuRKMeP/B1FcRK | INT     | f       | t      | ivaaproot@int.com | ivaaproot@int.com
(1 row)

adminserver-deployment-848f9647f5-dld6s:/opt/ivaap/adminserver$ listAllUsers
                 guid                 |                domain                | fname  |  lname   | deleted | active |         email          |        username        
--------------------------------------+--------------------------------------+--------+----------+---------+--------+------------------------+------------------------
 bb7ccb06-4c70-4053-aff5-ae586de4d0bd | 4d7a6c4e-fffd-49c8-a381-3e596d9889e4 |        |          | f       | t      | admin-main@testing.com | admin-main@testing.com
 c86e6859-ea06-4819-90f4-192a367fd15c | d4983f37-c74b-4ecb-bcb7-4118bd907708 | IVAAP  | Root     | f       | t      | ivaaproot@int.com      | ivaaproot@int.com
(2 rows)

adminserver-deployment-848f9647f5-dld6s:/opt/ivaap/adminserver$ promoteUserToSuperAdmin 
Enter the username of the user to make super admin: admin-main@testing.com
DO

adminserver-deployment-848f9647f5-dld6s:/opt/ivaap/adminserver$ listSuperAdmins
                 guid                 |                domain                | fname | lname |                            pword                             | company  | deleted | active |         email          |        username        
--------------------------------------+--------------------------------------+-------+-------+--------------------------------------------------------------+----------+---------+--------+------------------------+------------------------
 c86e6859-ea06-4819-90f4-192a367fd15c | d4983f37-c74b-4ecb-bcb7-4118bd907708 | IVAAP | Root  | $2y$10$BeAuSvLjAOz3w1dh9jbiR.XCrQ.yRRqhd8NstgFkuRKMeP/B1FcRK | INT      | f       | t      | ivaaproot@int.com      | ivaaproot@int.com
 bb7ccb06-4c70-4053-aff5-ae586de4d0bd | 4d7a6c4e-fffd-49c8-a381-3e596d9889e4 |       |       | $2y$10$BeAuSvLjAOz3w1dh9jbiR.XCrQ.yRRqhd8NstgFkuRKMeP/B1FcRK | external | f       | t      | admin-main@testing.com | admin-main@testing.com
(2 rows)

Troubleshooting

Starting over from scratch

If there are issues with loading the database, it is possible to start over from scratch by dropping all schemas and extensions. The following commands must be run in the order provided below:

DROP SCHEMA int CASCADE;
DROP SCHEMA tiger_data;
DROP EXTENSION IF EXISTS postgis_tiger_geocoder;
DROP SCHEMA tiger;
DROP EXTENSION IF EXISTS postgis_topology;
DROP SCHEMA topology;
DROP EXTENSION IF EXISTS citext;
DROP EXTENSION IF EXISTS fuzzystrmatch;
DROP EXTENSION IF EXISTS postgis;
DROP EXTENSION IF EXISTS "uuid-ossp";

Expected output:

ivaapdb=> DROP SCHEMA int CASCADE;
DROP SCHEMA tiger_data;
DROP EXTENSION IF EXISTS postgis_tiger_geocoder;
DROP SCHEMA tiger;
DROP EXTENSION IF EXISTS postgis_topology;
DROP SCHEMA topology;
DROP EXTENSION IF EXISTS citext;
DROP EXTENSION IF EXISTS fuzzystrmatch;
DROP EXTENSION IF EXISTS postgis;
DROP EXTENSION IF EXISTS "uuid-ossp";
DROP SCHEMA
DROP SCHEMA
DROP EXTENSION
DROP SCHEMA
DROP EXTENSION
DROP SCHEMA
DROP EXTENSION
DROP EXTENSION
DROP EXTENSION
DROP EXTENSION

Azure Managed Postgres Extensions Error

Error creating extensions if extensions aren’t enabled in Azure managed Postgres settings:

ivaapdb=> CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder WITH SCHEMA tiger;
CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;
ERROR:  extension "citext" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL
HINT:  to learn how to allow an extension or see the list of allowed extensions, please refer to https://go.microsoft.com/fwlink/?linkid=2281561
ERROR:  extension "fuzzystrmatch" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL
HINT:  to learn how to allow an extension or see the list of allowed extensions, please refer to https://go.microsoft.com/fwlink/?linkid=2281561
ERROR:  extension "postgis" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL
HINT:  to learn how to allow an extension or see the list of allowed extensions, please refer to https://go.microsoft.com/fwlink/?linkid=2281561
ERROR:  extension "postgis_tiger_geocoder" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL
HINT:  to learn how to allow an extension or see the list of allowed extensions, please refer to https://go.microsoft.com/fwlink/?linkid=2281561
ERROR:  extension "postgis_topology" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL
HINT:  to learn how to allow an extension or see the list of allowed extensions, please refer to https://go.microsoft.com/fwlink/?linkid=2281561
ERROR:  extension "uuid-ossp" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL
HINT:  to learn how to allow an extension or see the list of allowed extensions, please refer to https://go.microsoft.com/fwlink/?linkid=2281561

To resolve this, follow the steps in the section above named Azure Managed Postgres > Configuring Extensions inside Azure.

Incorrect Admin User Configuration

Postgres requires that the admin user is ivaapserver. If the admin user for postgres is not ivaapserver, the following errors may occur when the IVAAP schema is loaded:

psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:245: ERROR:  role "ivaapserver" does not exist
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:274: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:277: ERROR:  role "ivaapserver" does not exist
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:303: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:306: ERROR:  role "ivaapserver" does not exist
psql:ivaap-postgres-vanilla-pre-migration-2024-11-13.sql:325: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.

The easiest solution to this issue would be to start over from scratch; recreate the resource and to ensure that the correct admin user is provided during configuration and setup. If that is not an option, refer to documentation for PostgreSQL and the resource type (Azure, RDS, etc) for more information on how to create an admin user inside postgres named ivaapserver.

Specific Azure Role Errors

Azure managed postgres service has special roles only used by Azure. These roles are strictly for Azure Postgres administrative uses only, and are not used by IVAAP. If migrating from an Azure Managed PostgreSQL service to some other postgres service, you may get errors when loading the Azure db dump: 

psql:ivaap_dump.psql:1445751: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445758: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445765: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445772: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445779: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445786: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445793: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445800: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445807: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445814: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445821: ERROR:  role "azure_pg_admin" does not exist
psql:ivaap_dump.psql:1445828: ERROR:  role "azuresu" does not exist
REVOKE
psql:ivaap_dump.psql:1445830: ERROR:  role "azure_pg_admin" does not exist
GRANT
psql:ivaap_dump.psql:1445838: ERROR:  role "azuresu" does not exist
REVOKE
psql:ivaap_dump.psql:1445840: ERROR:  role "azure_pg_admin" does not exist
GRANT
psql:ivaap_dump.psql:1445848: ERROR:  role "azuresu" does not exist
REVOKE
psql:ivaap_dump.psql:1445850: ERROR:  role "azure_pg_admin" does not exist
GRANT
psql:ivaap_dump.psql:1445858: ERROR:  role "azuresu" does not exist
REVOKE
psql:ivaap_dump.psql:1445860: ERROR:  role "azure_pg_admin" does not exist
GRANT
psql:ivaap_dump.psql:1445868: ERROR:  role "azuresu" does not exist
REVOKE
psql:ivaap_dump.psql:1445870: ERROR:  role "azure_pg_admin" does not exist
GRANT
psql:ivaap_dump.psql:1445878: ERROR:  role "azuresu" does not exist
REVOKE
psql:ivaap_dump.psql:1445880: ERROR:  role "azure_pg_admin" does not exist
GRANT
psql:ivaap_dump.psql:1445888: ERROR:  role "azuresu" does not exist
REVOKE
psql:ivaap_dump.psql:1445890: ERROR:  role "azure_pg_admin" does not exist
GRANT
psql:ivaap_dump.psql:1445898: ERROR:  role "azuresu" does not exist
REVOKE
psql:ivaap_dump.psql:1445900: ERROR:  role "azure_pg_admin" does not exist
GRANT
psql:ivaap_dump.psql:1445908: ERROR:  role "azuresu" does not exist
REVOKE
psql:ivaap_dump.psql:1445910: ERROR:  role "azure_pg_admin" does not exist

The reason this happens is simply because the postgres instance the dump is being loaded to is not an Azure managed service, and is missing those roles. These errors can safely be ignored, as they will not effect IVAAP in any way.