Files
qqq-all/infra/postgres/02-auth-and-roles.sql

91 lines
3.0 KiB
SQL

-- C4's OAuth2 session tables and qbit-user-role-permissions tables.
-- Names follow QInstanceEnricher's camelCase-to-snake_case backend mapping.
CREATE TABLE IF NOT EXISTS user_session (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMPTZ,
modify_date TIMESTAMPTZ,
uuid VARCHAR(40) UNIQUE,
access_token TEXT,
user_id VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS redirect_state (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
state VARCHAR(45) UNIQUE,
redirect_uri VARCHAR(4096),
create_date TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS "user" (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMPTZ,
modify_date TIMESTAMPTZ,
email VARCHAR(250),
full_name VARCHAR(250)
);
CREATE TABLE IF NOT EXISTS role (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMPTZ,
modify_date TIMESTAMPTZ,
name VARCHAR(100) NOT NULL UNIQUE,
description VARCHAR(500)
);
CREATE TABLE IF NOT EXISTS permission (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMPTZ,
modify_date TIMESTAMPTZ,
name VARCHAR(250) NOT NULL UNIQUE,
description VARCHAR(500),
object_type VARCHAR(50),
object_label VARCHAR(250)
);
CREATE TABLE IF NOT EXISTS role_permission_int (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMPTZ,
modify_date TIMESTAMPTZ,
role_id INTEGER NOT NULL REFERENCES role(id),
permission_id INTEGER NOT NULL REFERENCES permission(id),
UNIQUE (role_id, permission_id)
);
CREATE TABLE IF NOT EXISTS user_role_int (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMPTZ,
modify_date TIMESTAMPTZ,
user_id INTEGER NOT NULL REFERENCES "user"(id),
role_id INTEGER NOT NULL REFERENCES role(id),
UNIQUE (user_id, role_id)
);
CREATE TABLE IF NOT EXISTS user_permission_int (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
create_date TIMESTAMPTZ,
modify_date TIMESTAMPTZ,
user_id INTEGER NOT NULL REFERENCES "user"(id),
permission_id INTEGER NOT NULL REFERENCES permission(id),
UNIQUE (user_id, permission_id)
);
INSERT INTO role (name, description) VALUES
('admin', 'All QQQ All demo tables'),
('viewer', 'Read-only QQQ All demo tables')
ON CONFLICT (name) DO NOTHING;
INSERT INTO permission (name, object_type, object_label)
SELECT table_name || '.' || action, 'table', table_name
FROM (VALUES
('customer'), ('order'), ('orderLine'), ('product'),
('warehouseCustomer'), ('supplierOrder'), ('shipment'), ('document'), ('importFile')
) AS tables(table_name)
CROSS JOIN (VALUES ('read'), ('insert'), ('edit'), ('delete')) AS actions(action)
ON CONFLICT (name) DO NOTHING;
INSERT INTO role_permission_int (role_id, permission_id)
SELECT role.id, permission.id
FROM role CROSS JOIN permission
WHERE role.name = 'admin' OR (role.name = 'viewer' AND permission.name LIKE '%.read')
ON CONFLICT (role_id, permission_id) DO NOTHING;