PgJSON session backend

This backend is the recommended one for production installations of LemonLDAP::NG.

Prerequisites

First, make sure you have installed the DBD::Pg perl module.

On Debian-based distributions

apt install libdbd-pg-perl

On Fedora-based distributions

yum install 'perl(DBD::Pg)'

The minimum required version of PostgreSQL is 9.3 with support for JSON column types

Make sure you are using at least version 1.2.9 of Apache::Session::Browseable, this might require installing it from Debian Backports or CPAN.

Create database schema

Create the following tables. You may skip the session types you are not going to use, but you need at least sessions and psessions

CREATE TABLE sessions (
    id varchar(64) not null primary key,
    a_session jsonb
);

CREATE INDEX i_s__whatToTrace     ON sessions ((a_session ->> '_whatToTrace'));
CREATE INDEX i_s__session_kind    ON sessions ((a_session ->> '_session_kind'));
CREATE INDEX i_s__utime           ON sessions ((cast (a_session ->> '_utime' as bigint)));
CREATE INDEX i_s_ipAddr           ON sessions ((a_session ->> 'ipAddr'));
CREATE INDEX i_s__httpSessionType ON sessions ((a_session ->> '_httpSessionType'));
CREATE INDEX i_s_user             ON sessions ((a_session ->> 'user'));


CREATE TABLE psessions (
    id varchar(64) not null primary key,
    a_session jsonb
);
CREATE INDEX i_p__session_kind    ON psessions ((a_session ->> '_session_kind'));
CREATE INDEX i_p__httpSessionType ON psessions ((a_session ->> '_httpSessionType'));
CREATE INDEX i_p__session_uid     ON psessions ((a_session ->> '_session_uid'));
CREATE INDEX i_p_ipAddr           ON psessions ((a_session ->> 'ipAddr'));
CREATE INDEX i_p__whatToTrace     ON psessions ((a_session ->> '_whatToTrace'));


CREATE TABLE samlsessions (
    id varchar(64) not null primary key,
    a_session jsonb
);
CREATE INDEX i_a__session_kind ON samlsessions ((a_session ->> '_session_kind'));
CREATE INDEX i_a__utime        ON samlsessions ((cast(a_session ->> '_utime' as bigint)));
CREATE INDEX i_a_ProxyID       ON samlsessions ((a_session ->> 'ProxyID'));
CREATE INDEX i_a__nameID       ON samlsessions ((a_session ->> '_nameID'));
CREATE INDEX i_a__assert_id    ON samlsessions ((a_session ->> '_assert_id'));
CREATE INDEX i_a__art_id       ON samlsessions ((a_session ->> '_art_id'));
CREATE INDEX i_a__saml_id      ON samlsessions ((a_session ->> '_saml_id'));

CREATE TABLE oidcsessions (
    id varchar(64) not null primary key,
    a_session jsonb
);
CREATE INDEX i_o__session_kind ON oidcsessions ((a_session ->> '_session_kind'));
CREATE INDEX i_o__utime        ON oidcsessions ((cast(a_session ->> '_utime' as bigint )));

CREATE TABLE cassessions (
    id varchar(64) not null primary key,
    a_session jsonb
);
CREATE INDEX i_c__session_kind ON cassessions ((a_session ->> '_session_kind'));
CREATE INDEX i_c__utime        ON cassessions ((cast(a_session ->> '_utime' as bigint)));
CREATE INDEX i_c__cas_id       ON cassessions ((a_session ->> '_cas_id'));
CREATE INDEX i_c_pgtIou        ON cassessions ((a_session ->> 'pgtIou'));

LemonLDAP::NG configuration

Go in the Manager and set the session module to Apache::Session::Browseable::PgJSON for each session type you intend to use:

• General parameters » Sessions » Session storage » Apache::Session module
• General parameters » Sessions » Persistent sessions » Apache::Session module
• CAS Service » CAS sessions module name
• OpenID Connect Service » Sessions » Sessions module name
• SAML2 Service » Advanced » SAML sessions module name

Then, set the following module options:

Required parameters
Name	Comment	Example
DataSource	The DBI string	dbi:Pg:database=lemonldap-ng
UserName	The database username	lemonldapng
Password	The database password	mysuperpassword
TableName	Table name (optional)	sessions
Commit	1	This setting is mandatory for PostgreSQL to work

Tip

Unlike other browseable modules, Pg::JSON does not require an Index parameter