Database Structure

The following documentation contains description of database structure.

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

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

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,
  password 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
`password`	`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 Jun 23, 2022 (00:07) View product