Storage block

storage:
  driver: "postgres"
  postgres:
    url: "postgres://${PG_USER}:${PG_PASSWORD}@localhost:5432/postgres?sslmode=disable&search_path=cerbos"
    connPool:
      maxLifeTime: 5m
      maxIdleTime: 3m
      maxOpen: 10
      maxIdle: 5

CREATE SCHEMA IF NOT EXISTS cerbos;

SET search_path TO cerbos;

CREATE TABLE IF NOT EXISTS policy (
    id bigint NOT NULL PRIMARY KEY, 
    kind VARCHAR(128) NOT NULL,
    name VARCHAR(1024) NOT NULL,
    version VARCHAR(128) NOT NULL,
    scope VARCHAR(512),
    description TEXT,
    disabled BOOLEAN default false,
    definition BYTEA
);

CREATE TABLE IF NOT EXISTS policy_dependency (
    policy_id BIGINT,
    dependency_id BIGINT,
    PRIMARY KEY (policy_id, dependency_id),
    FOREIGN KEY (policy_id) REFERENCES cerbos.policy(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS policy_ancestor (
    policy_id BIGINT,
    ancestor_id BIGINT,
    PRIMARY KEY (policy_id, ancestor_id),
    FOREIGN KEY (policy_id) REFERENCES cerbos.policy(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS policy_revision (
    revision_id SERIAL PRIMARY KEY,
    action VARCHAR(64),
    id BIGINT,
    kind VARCHAR(128),
    name VARCHAR(1024),
    version VARCHAR(128),
    scope VARCHAR(512),
    description TEXT,
    disabled BOOLEAN, 
    definition BYTEA,
    update_timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS attr_schema_defs (
    id VARCHAR(255) PRIMARY KEY,
    definition JSON
);

CREATE OR REPLACE FUNCTION process_policy_audit() RETURNS TRIGGER AS $policy_audit$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO policy_revision(action, id, kind, name, version, scope, description, disabled, definition)
            VALUES('DELETE', OLD.id, OLD.kind, OLD.name, OLD.version, OLD.scope, OLD.description, OLD.disabled, OLD.definition);
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO policy_revision(action, id, kind, name, version, scope, description, disabled, definition)
            VALUES('UPDATE', NEW.id, NEW.kind, NEW.name, NEW.version, NEW.scope, NEW.description, NEW.disabled, NEW.definition);
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO policy_revision(action, id, kind, name, version, scope, description, disabled, definition)
            VALUES('INSERT', NEW.id, NEW.kind, NEW.name, NEW.version, NEW.scope, NEW.description, NEW.disabled, NEW.definition);
        END IF;
        RETURN NULL; 
    END;
$policy_audit$ LANGUAGE plpgsql;

CREATE TRIGGER policy_audit
AFTER INSERT OR UPDATE OR DELETE ON policy 
FOR EACH ROW EXECUTE PROCEDURE process_policy_audit();

CREATE USER cerbos_user WITH PASSWORD 'changeme';
GRANT CONNECT ON DATABASE postgres TO cerbos_user;
GRANT USAGE ON SCHEMA cerbos TO cerbos_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON cerbos.policy, cerbos.policy_dependency, cerbos.policy_ancestor, cerbos.attr_schema_defs TO cerbos_user; 
GRANT SELECT,INSERT ON cerbos.policy_revision TO cerbos_user;
GRANT USAGE,SELECT ON cerbos.policy_revision_revision_id_seq TO cerbos_user;

storage:
  driver: "mysql"
  mysql:
    dsn: "${MYSQL_USER}:${MYSQL_PASSWORD}@tcp(localhost:3306)/cerbos?tls=mysecuretls"
    tls:
      mysecuretls:
        caCert: /path/to/ca_certificate.crt
        cert: /path/to/certificate.crt
        key: /path/to/private.key

storage:
  driver: "mysql"
  mysql:
    dsn: "${MYSQL_USER}:${MYSQL_PASSWORD}@tcp(localhost:3306)/cerbos?serverPubKey=mypubkey"
    serverPubKey:
      mypubkey: /path/to/server_public_key.pem

storage:
  driver: "mysql"
  mysql:
    dsn: "${MYSQL_USER}:${MYSQL_PASSWORD}@tcp(localhost:3306)/cerbos"
    connPool:
      maxLifeTime: 5m
      maxIdleTime: 3m
      maxOpen: 10
      maxIdle: 5

CREATE DATABASE IF NOT EXISTS cerbos CHARACTER SET utf8mb4;

USE cerbos;

CREATE TABLE IF NOT EXISTS policy (
    id BIGINT PRIMARY KEY, 
    kind VARCHAR(128) NOT NULL,
    name VARCHAR(1024) NOT NULL,
    version VARCHAR(128) NOT NULL,
    scope VARCHAR(512),
    description TEXT,
    disabled BOOLEAN default false,
    definition BLOB);

CREATE TABLE IF NOT EXISTS policy_dependency (
    policy_id BIGINT NOT NULL,
    dependency_id BIGINT NOT NULL,
    PRIMARY KEY (policy_id, dependency_id),
    FOREIGN KEY (policy_id) REFERENCES policy(id) ON DELETE CASCADE);

CREATE TABLE IF NOT EXISTS policy_ancestor (
    policy_id BIGINT NOT NULL,
    ancestor_id BIGINT NOT NULL,
    PRIMARY KEY (policy_id, ancestor_id),
    FOREIGN KEY (policy_id) REFERENCES policy(id) ON DELETE CASCADE);

CREATE TABLE IF NOT EXISTS policy_revision (
    revision_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    action ENUM('INSERT', 'UPDATE', 'DELETE'),
    id BIGINT NOT NULL,
    kind VARCHAR(128),
    name VARCHAR(1024),
    version VARCHAR(128),
    scope VARCHAR(512),
    description TEXT,
    disabled BOOLEAN, 
    definition BLOB,
    update_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS attr_schema_defs (
    id VARCHAR(255) PRIMARY KEY,
    definition JSON);

DROP TRIGGER IF EXISTS policy_on_insert;

CREATE TRIGGER policy_on_insert AFTER INSERT ON policy 
FOR EACH ROW
INSERT INTO policy_revision(action, id, kind, name, version, scope, description, disabled, definition)
VALUES('INSERT', NEW.id, NEW.kind, NEW.name, NEW.version, NEW.scope, NEW.description, NEW.disabled, NEW.definition);

DROP TRIGGER IF EXISTS policy_on_update;

CREATE TRIGGER policy_on_update AFTER UPDATE ON policy 
FOR EACH ROW
INSERT INTO policy_revision(action, id, kind, name, version, scope, description, disabled, definition)
VALUES('UPDATE', NEW.id, NEW.kind, NEW.name, NEW.version, NEW.scope, NEW.description, NEW.disabled, NEW.definition);

DROP TRIGGER IF EXISTS policy_on_delete;

CREATE TRIGGER policy_on_delete AFTER DELETE ON policy 
FOR EACH ROW
INSERT INTO policy_revision(action, id, kind, name, version, scope, description, disabled, definition)
VALUES('DELETE', OLD.id, OLD.kind, OLD.name, OLD.version, OLD.scope, OLD.description, OLD.disabled, OLD.definition);

CREATE USER IF NOT EXISTS cerbos_user IDENTIFIED WITH mysql_native_password BY 'changeme';
GRANT SELECT,INSERT,UPDATE,DELETE ON cerbos.policy TO cerbos_user; 
GRANT SELECT,INSERT,UPDATE,DELETE ON cerbos.attr_schema_defs TO cerbos_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON cerbos.policy_dependency TO cerbos_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON cerbos.policy_ancestor TO cerbos_user;
GRANT SELECT,INSERT ON cerbos.policy_revision TO cerbos_user; 

storage:
  driver: "sqlserver"
  sqlserver:
    url: "sqlserver://${SQL_SERVER_USERNAME}:${SQL_SERVER_PASSWORD}@host/instance?database=cerbos&param1=value&param2=value"
    connPool:
      maxLifeTime: 5m
      maxIdleTime: 3m
      maxOpen: 10
      maxIdle: 5

IF SUSER_ID('cerbos_user') IS NULL
CREATE LOGIN cerbos_user WITH PASSWORD = 'ChangeMe(1!!)';

GO

IF NOT EXISTS (
    SELECT [name]
        FROM sys.databases
        WHERE [name] = N'cerbos'
)
CREATE DATABASE cerbos;
GO

USE cerbos;

IF OBJECT_ID('[dbo].[policy]', 'U') IS NULL
CREATE TABLE [dbo].[policy] (
    id BIGINT PRIMARY KEY,
    kind VARCHAR(128) NOT NULL,
    name VARCHAR(1024) NOT NULL,
    version VARCHAR(128) NOT NULL,
    scope VARCHAR(512),
    description NVARCHAR(MAX),
    disabled BIT default 'FALSE',
    definition VARBINARY(MAX));

IF OBJECT_ID('[dbo].[policy_dependency]', 'U') IS NULL
CREATE TABLE [dbo].[policy_dependency] (
    policy_id BIGINT NOT NULL,
    dependency_id BIGINT  NOT NULL,
    PRIMARY KEY (policy_id, dependency_id),
    FOREIGN KEY (policy_id) REFERENCES [policy](id) ON DELETE CASCADE);

IF OBJECT_ID('[dbo].[policy_ancestor]', 'U') IS NULL
CREATE TABLE [dbo].[policy_ancestor] (
    policy_id BIGINT NOT NULL,
    ancestor_id BIGINT  NOT NULL,
    PRIMARY KEY (policy_id, ancestor_id),
    FOREIGN KEY (policy_id) REFERENCES [policy](id) ON DELETE CASCADE);

IF OBJECT_ID('[dbo].[policy_revision]', 'U') IS NULL
CREATE TABLE [dbo].[policy_revision] (
    revision_id INT NOT NULL IDENTITY PRIMARY KEY,
    action VARCHAR(255) NOT NULL CHECK ([action] IN('INSERT', 'UPDATE', 'DELETE')),
    id BIGINT NOT NULL,
    kind VARCHAR(128),
    name VARCHAR(1024),
    version VARCHAR(128),
    scope VARCHAR(512),
    description NVARCHAR(MAX),
    disabled BIT,
    definition VARBINARY(MAX),
    update_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP);

IF OBJECT_ID('[dbo].[attr_schema_defs]', 'U') IS NULL
CREATE TABLE [dbo].[attr_schema_defs] (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    definition VARBINARY(MAX));

DROP TRIGGER IF EXISTS dbo.policy_on_insert;
DROP TRIGGER IF EXISTS dbo.policy_on_update;
DROP TRIGGER IF EXISTS dbo.policy_on_delete;

GO

USE cerbos;

IF USER_ID('cerbos_user') IS NULL
CREATE USER cerbos_user for LOGIN cerbos_user;

GRANT SELECT,INSERT,UPDATE,DELETE ON [dbo].[policy] TO cerbos_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.attr_schema_defs TO cerbos_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.policy_dependency TO cerbos_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.policy_ancestor TO cerbos_user;
GRANT SELECT,INSERT ON dbo.policy_revision TO cerbos_user;

GO

CREATE TRIGGER dbo.policy_on_insert ON dbo.[policy] AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.policy_revision(action, id, kind, name, version, scope, description, disabled, definition)
    SELECT
        'INSERT', i.id, i.kind, i.name, i.version, i.scope, i.description, i.disabled, i.definition
    FROM inserted i
END;

GO

CREATE TRIGGER dbo.policy_on_update ON dbo.[policy] AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.policy_revision(action, id, kind, name, version, scope, description, disabled, definition)
    SELECT
        'UPDATE', i.id, i.kind, i.name, i.version, i.scope, i.description, i.disabled, i.definition
    FROM inserted i
END;

GO

CREATE TRIGGER dbo.policy_on_delete ON dbo.[policy] AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.policy_revision(action, id, kind, name, version, scope, description, disabled, definition)
    SELECT
        'DELETE', d.id, d.kind, d.name, d.version, d.scope, d.description, d.disabled, d.definition
    FROM deleted d
END;