001_base.sql
1-- schema/001_base.sql
2-- +goose Up
3-- +goose StatementBegin
4CREATE TABLE IF NOT EXISTS permissions (
5 resource_type TEXT NOT NULL,
6 resource_id INTEGER,
7 user_id INTEGER,
8 attribute_id INTEGER,
9 permissions INTEGER NOT NULL
10);
11
12CREATE TABLE IF NOT EXISTS attributes (key TEXT NOT NULL, value BLOB NOT NULL);
13
14CREATE TABLE IF NOT EXISTS attribute_user (
15 attribute_id INTEGER NOT NULL,
16 user_id INTEGER NOT NULL
17);
18
19CREATE VIEW IF NOT EXISTS abac (resource_type, resource_id, user_id, permissions) AS
20SELECT
21 permissions.resource_type,
22 permissions.resource_id,
23 COALESCE(permissions.user_id, attribute_user.user_id) AS user_id,
24 permissions.permissions
25FROM
26 permissions
27 LEFT JOIN attribute_user USING (attribute_id);
28
29CREATE UNIQUE INDEX IF NOT EXISTS permission_idx_user ON permissions (resource_type, resource_id, user_id)
30WHERE
31 attribute_id IS NULL;
32
33CREATE UNIQUE INDEX IF NOT EXISTS permission_idx_att ON permissions (resource_type, resource_id, attribute_id)
34WHERE
35 user_id IS NULL;
36
37CREATE UNIQUE INDEX IF NOT EXISTS permission_idx_def_res ON permissions (resource_type, resource_id)
38WHERE
39 user_id IS NULL
40 AND attribute_id IS NULL;
41
42CREATE UNIQUE INDEX IF NOT EXISTS permission_idx_def_type ON permissions (resource_type)
43WHERE
44 resource_id IS NULL
45 AND user_id IS NULL
46 AND attribute_id IS NULL;
47
48CREATE UNIQUE INDEX IF NOT EXISTS attribute_user_idx ON attribute_user (attribute_id, user_id);
49
50CREATE UNIQUE INDEX IF NOT EXISTS attribute_idx ON attributes (key, value);
51-- +goose StatementEnd
52
53-- +goose Down
54-- +goose StatementBegin
55DROP TABLE permissions;
56DROP TABLE attributes;
57DROP TABLE attribute_user;
58DROP INDEX permission_idx_user;
59DROP INDEX permission_idx_att;
60DROP INDEX permission_idx_def_res;
61DROP INDEX permission_idx_def_type;
62DROP INDEX attribute_user_idx;
63DROP INDEX attribute_idx;
64-- +goose StatementEnd
65
