Database Structure

View product v1.8.2

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:

PowerAuth Server Database Structure - tables prefixed by pa_ (except for pa_cloud_).
PowerAuth Push Server Database Structure - tables prefixed by push_.

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

Schema

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.

Schema

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.

Schema

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

Last updated on Mar 18, 2024 (06:52) View product