Accounting Service database - SSO

Accounting Service stores information in these database tables:

Event table ACCT_EVENT to store the incoming events
Daily Accounting table ACCT_DAILYACCOUNTING to store the daily unique users
Monthly Accounting table ACCT_MONTHLYACCOUNTING to store the calculated monthly user counts

These tables are created automatically in the startup with the embedded scripts by the means of Flyway Database migration tool (https://flywaydb.org). Flyway holds the migration data in its own table FLYWAY_SCHEMA_HISTORY.

NOTE: If the embedded PostgreSQL database table creation scripts are not optimal for your needs you can replace them by extending the Accounting Service configuration with Spring Boot Flyway settings. However, the product has been tested only with the embedded scripts and PostgreSQL database.

Event table details

Event table stores the timestamps in UTC timezone.

Most of the event attributes are stored in JSON format in the column ACCC_EVENT_DATA. Event data depends on the event type but currently only ticket granted events are handled.

Example of ticket granted event data JSON part

{
    "ticketRequestId": "54e3d543997f2d25dbf01a9414a0bd72d0fa7e3291ace8315c6dab71147acd1c",
    "authRequestOrigin": "CN=eidm2,OU=eIDM Services,CN=Ubilogin,DC=test",
    "redirectUrl": "https://localhost:7443/",
    "identityType": "UBILOGIN",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:66.0) Gecko/20100101 Firefox/66.0",
    "sessionId": "d74700161d7ea52e990399609fc1e3c016f21f3bbc39f11e256e709b2a503c38",
    "authMethodName": "saml.ap.custid",
    "remoteAddress": "ee6b208b66b8990afd3071b8e96395c3cecd00b1c08a6499e6b8ef62c67c171e",
    "authMethodType": "SAML"
}

NOTE: Event data column contains a large object and is defined as text data type in the PostgreSQL database. PostgreSQL stores in the text column an index referring to another table with the actual data. In order to query it you need to use e.g. the following kind of SQL syntax:

SELECT *, 
    convert_from(loread(
         lo_open(accc_event_data::int, x'40000'::int), x'40000'::int), 'UTF-8') 
    AS accc_event_data
FROM acct_event;

See also PostgreSQL Documentation, Large objects (https://www.postgresql.org/docs/9.6/lo-interfaces.html)