postgres=# CREATE ROLE shibboleth WITH LOGIN;
postgres=# \password shibboleth
postgres=# CREATE DATABASE shibboleth WITH OWNER shibboleth ENCODING 'UTF8' TEMPLATE template0;
postgres=# \c shibboleth
shibboleth=# SET ROLE shibboleth;
shibboleth=# CREATE TABLE shibpid (
        localEntity VARCHAR(1024) NOT NULL,
        peerEntity VARCHAR(1024) NOT NULL,
        principalName VARCHAR(255) NOT NULL,
        localId VARCHAR(255) NOT NULL,
        persistentId VARCHAR(36) NOT NULL,
        peerProvidedId VARCHAR(255) NULL,
        creationDate TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP,
        deactivationDate TIMESTAMP NULL DEFAULT NULL,
        PRIMARY KEY (localEntity, peerEntity, persistentId)
        );
shibboleth=# CREATE INDEX shibpid_getbysourcevalue_index ON shibpid(localEntity, peerEntity, localId, deactivationDate);
shibboleth=# CREATE TABLE storagerecords (
        context VARCHAR(255) NOT NULL,
        id VARCHAR(255) NOT NULL,
        expires BIGINT DEFAULT NULL,
        value TEXT NOT NULL,
        version BIGINT NOT NULL,
        PRIMARY KEY (context, id)
        );
shibboleth=# CREATE INDEX storagerecords_expires_index ON storagerecords(expires);