PostgreSQLSessionStoreServlet

The PostgreSQLSessionStoreServlet facilitates the remote storage of session information into a Postgres database, within the context of the Dynamic Session Management Engine.

You can only use the PostgreSQLSessionStoreServlet in combination with the SessionManagementFilter. The SessionManagementFilter defines the method of session binding, for example, by means of cookies. The filter also creates and handles the session. The PostgreSQLSessionStoreServlet loads and stores the session data in the remote session store, that is, into the remote Postgres database. In particular, the PostgreSQLSessionStoreServlet stores the data that is required to identify and control the user sessions in the Postgres data table.

Compared to the LocalSessionStoreServlet, the PostgreSQLSessionStoreServlet preserves existing sessions when restarting the proxy. It also allows sharing sessions among multiple proxy instances. However, using the PostgreSQLSessionStoreServlet implies separating the proxy from its session store, which will extend the communication time between them. This can also impact the overall performance negatively, compared to using the LocalSessionStoreServlet.

For more information on how to share one or multiple remote session stores, see the chapter Sharing remote session stores.

Classname

ch::nevis::nevisproxy::servlet::cache::postgresql::PostgreSQLSessionStoreServlet

Library

libPostgreSQLSessionStoreServlets.so.1

Configuration

ConnectString

Type: string
Syntax: postgresql://[userspec@][hostspec][/dbname][?paramspec]
Usage Constraints: required

Sets the Postgres database connect string. The userspec is user[:password] and hostspec is [host][:port][,...] and paramspec is name=value[&...].
See also https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

note

When using SSL, the private key file (configured with sslkey param in the connection string) must have permissions u=rw (0600) or less if owned by the proxy user, or permissions u=rw,g=r (0640) or less if owned by root.

SessionsTableName

Type: string
Default: session

Sets the name of the table that holds the proxy sessions.

AttributesTableName

Type: string
Default: attribute

Sets the name of the table that holds the proxy attributes.

KeyToIdMapTableName

Type: string
Default: key_id_map

Sets the name of the table that holds the proxy key-ID map.

ConfigurationsTableName

Type: string
Default: conf

Sets the name of the table that holds the proxy configuration.

MaxConnections

Type: integer
Default: 100
Range: min: 10

Sets the maximum number of open connections.

MaxConnections.WaitTimeout

Type: integer
Unit: milliseconds
Default: 5000
Range: min: 10

Sets the timeout to wait until a connection can be reused again if all the available connections are in used. If no connection can be reused within this time, an error will occur.

EnableMetrics

Type: boolean
Usage Constraints: optional
Default: true

Controls the OpenTelemetry metrics generation of the servlet. If disabled, the servlet won't forward the session statistics as metrics.

Database Configuration

You need to create the following tables in your database:

-- Database setup for session management engine.

CREATE TABLE session (
    SESSION_ID VARCHAR(255) PRIMARY KEY,
    CREATION_TIME INTEGER NOT NULL,
    LAST_ACCESS_TIME INTEGER NOT NULL,
    INACTIVE_TIMEOUT INTEGER NOT NULL,
    FINAL_TIMEOUT INTEGER NOT NULL,
    OWNER_ID BIGINT NOT NULL DEFAULT 0
);

CREATE INDEX owner_id ON session (OWNER_ID);

CREATE TABLE key_id_map (
    KEY_ID VARCHAR(255) PRIMARY KEY,
    SESSION_ID VARCHAR(255) NOT NULL,
    FOREIGN KEY(SESSION_ID) REFERENCES session(SESSION_ID) ON DELETE CASCADE
);

CREATE TABLE attribute (
    SESSION_ID VARCHAR(255) NOT NULL,
    NAME VARCHAR(200) NOT NULL,
    VALUE BYTEA,
    VALUE_SHA BYTEA,
    FOREIGN KEY(SESSION_ID) REFERENCES session(SESSION_ID) ON DELETE CASCADE
);
alter table attribute add constraint uc_id_name unique (SESSION_ID, NAME);

CREATE TABLE conf (
    CACHENAME VARCHAR(100) not null,
    PARAMETER VARCHAR(100) not null,
    VALUE VARCHAR(255) not null
);

insert into conf (CACHENAME, PARAMETER, VALUE) values('session', 'VERSION', '5.7.0.0');
insert into conf (CACHENAME, PARAMETER, VALUE) values('session', 'REAPER', '0');
insert into conf (CACHENAME, PARAMETER, VALUE) values('session', 'INACTIVE_TIMEOUT', '300');
insert into conf (CACHENAME, PARAMETER, VALUE) values('session', 'FINAL_TIMEOUT', '43100');

You need to grant all privileges to the use configured in the ConnectString. For ex.:

GRANT ALL PRIVILEGES ON TABLE session TO <user>;
GRANT ALL PRIVILEGES ON TABLE key_id_map TO <user>;
GRANT ALL PRIVILEGES ON TABLE conf TO <user>;
GRANT ALL PRIVILEGES ON TABLE attribute TO <user>;