Database Structure

The following documentation contains description of database behavior and table structure.

Connection Pooling

All our applications use HikariCP (default for Spring Boot). As a result, we manage connections efficiently. By default, we create 10 connections per application. Since the Docker image contains 4 applications, each container will allocate 40 connections to the underlying PostgreSQL database.

In case you change configuration of the image to has lower count of idle connection than maximum number of connections, some of the connections may be released. If the application becomes inactive and connection are idle, HikariCP will keep these connections open for approximately 10 minutes (600 000 milliseconds, with maximum variation of 30s - HikariCP checks for idle state every 30s).

Open-Source Component Tables

Since the PowerAuth Cloud is a component consisting of several individual applications, you can learn about the database structure in the documentation of the components:

Tables

es_operation_template Enrollment Server Operations

Stores definitions of operations presented via API towards the mobile token app.

create table es_operation_template (
  id bigint not null constraint es_operation_template_pkey primary key,
  placeholder varchar(255) not null,
  language varchar(8) not null,
  title varchar(255) not null,
  message text not null,
  attributes text,
  ui text
);

create unique index es_operation_template_placeholder on es_operation_template (placeholder, language);
Name Type Default Not Null Key Description
id bigint   Y Primary Primary ID of the record
placeholder varchar(255)   Y   Localization placeholder
language varchar(8)   Y   Language (ISO 639-1)
title varchar(255)   Y   Operation title
message text   Y   Operation message
attributes text   N   Operation attributes
ui text   N   Approval UI extension JSON
Name Primary References Description
es_operation_template_pkey Y id Primary key for table records
Name Unique Columns Description
es_operation_template_placeholder Y placeholder, language Index for faster localization placeholder lookup

pa_cloud_user API User Credentials

Stores credentials for the integration API users (systems calling the services to initiate a mobile token application).

create table pa_cloud_user (
  id bigint not null constraint pa_cloud_user_pkey primary key,
  username varchar(255) not null,
  userpassword varchar(255) not null,
  enabled boolean not null
);

create unique index pa_cloud_user_username_idx on pa_cloud_user (username);
Name Type Default Not Null Key Description
id bigint   Y Primary Primary ID of the record
username varchar(255)   Y   API user username
userpassword varchar(255)   Y   API user password (SHA-256, or bcrypt)
enabled boolean   Y   Boolean indicating if the particular API user is enabled.
Name Primary References Description
pa_cloud_user_pkey Y id Primary key for table records
Name Unique Columns Description
pa_cloud_user_username_idx Y username Index for faster API user lookup by the username

pa_cloud_user_authority API User Authority

Stores the authority (role) of the integration API users.

create table pa_cloud_user_authority (
  id bigint not null constraint pa_cloud_user_authority_pkey primary key,
  user_id bigint not null constraint fk_pa_cloud_user_authorities references pa_cloud_user,
  authority varchar(255) not null
);

create unique index pa_cloud_user_authority_idx on pa_cloud_user_authority (user_id, authority);
Name Type Default Not Null Key Description
id bigint   Y Primary Primary ID of the record
user_id bigint   Y   ID of the user with the role
authority varchar(255)   Y   Authority name (i.e., ROLE_USER or ROLE_ADMIN)
Name Primary References Description
pa_cloud_user_authority_pkey Y id Primary key for table records
Name Unique Columns Description
pa_cloud_user_authority_idx Y user_id, authority Index for faster lookup by user and authority, to allow quick hasAuthority checks.

pa_cloud_user_application API User To Application Assignment

Table for relation between the integration API users and mobile application (PowerAuth application IDs).

create table pa_cloud_user_application (
  id bigint not null constraint pa_cloud_user_application_pkey primary key,
  user_id bigint not null,
  app_id bigint not null
);
Name Type Default Not Null Key Description
id bigint   Y Primary Primary ID of the record
user_id biging   Y   User ID of the API user
app_id bigint   Y   ID of mobile app
Name Primary References Description
pa_cloud_user_application_pkey Y id Primary key for table records
Name Unique Columns Description
pa_cloud_user_application_idx Y username Index for faster lookup by username, to allow fast lookup of related application.

pa_cloud_localization Operation Localization

Stores localization strings for the operation summary.

create table pa_cloud_localization (
  id bigint not null constraint pa_cloud_localization_pkey primary key,
  placeholder varchar(255) not null,
  language varchar(8) not null,
  title varchar(255) not null,
  summary text not null
);

create unique index pa_cloud_loc_placehoder_idx on pa_cloud_localization (placeholder, language);
Name Type Default Not Null Key Description
id bigint   Y Primary Primary ID of the record
placeholder varchar(255)   Y   ID of mobile app
language varchar(8)   Y   Language (ISO 639-1)
title varchar(255)   Y   Operation title
summary text   Y   Operation summary
Name Primary References Description
pa_cloud_localization_pkey Y id Primary key for table records
Name Unique Columns Description
pa_cloud_loc_placehoder_idx Y placeholder, language Index for faster lookup of a localization placeholder.

pa_cloud_property Configuration Properties

Stores various configuration properties of the system.

create table pa_cloud_property (
    name  varchar(255) not null constraint pa_cloud_property_pkey primary key,
    value text
);
Name Type Default Not Null Key Description
name varchar(255)   Y Primary Property key
value text   Y   Property value
Name Primary References Description
pa_cloud_property_pkey Y id Primary key for table records

No additional indexes

Sequences

pa_cloud_user_seq Cloud User ID Sequence

Sequence for counting the API user IDs.

CREATE SEQUENCE pa_cloud_user_seq
    MINVALUE 1 MAXVALUE 9223372036854775807
    INCREMENT BY 1 START WITH 1 CACHE 20;

pa_cloud_user_app_seq Cloud Users App Relation ID Sequence

Sequence for counting relations between users and apps.

CREATE SEQUENCE pa_cloud_user_app_seq
    MINVALUE 1 MAXVALUE 9223372036854775807
    INCREMENT BY 1 START WITH 1 CACHE 20;

pa_cloud_localization_seq Localization Element ID Sequence

Sequence for counting localization objects.

CREATE SEQUENCE pa_cloud_localization_seq
    MINVALUE 1 MAXVALUE 9223372036854775807
    INCREMENT BY 1 START WITH 1 CACHE 20;
Last updated on Mar 18, 2024 (07:52) View product
Search

develop

PowerAuth Cloud