schema.lit
1@code_type sql .sql
2@comment_type -- %s
3@compiler lit -t schema.lit && bash -c 'sql-formatter -l sqlite --fix **/*.sql' && bash -c 'for f in **/*.sql; do echo "GENERATED - DO NOT MODIFY. See schema.lit" >> $f; done'
4
5@title Attribute-based Access Control
6
7This is an ABAC module implemented in Go and SQL (using sqlite3). It is meant to be embedded in a `users` database
8and called and exposed through higher-level interfaces to the rest of the program. It provides low-level functions
9for assigning and checking permissions, and persisting/querying them in a database. Much of the logic is implemented
10in SQL to ensure the correct permissions under various conflicting scenarios using relational constraints. Literate
11programming is used to explain the design and generate the SQL files that are embedded in the Go library.
12
13@s Schema
14
15The basic schema consists of three tables and a materialized view of the permissions set. Goose is used to handle
16migrations.
17
18The `permissions` table uses a zanzibar-style system. There should be one answer to the question: Can `{actor}` do `{action}`
19to `{resource}`. Permissions are simplified to a bit-field integer (see permissions.go). Resources have a `type` and
20an `id` (a int64 snowflake primary key). For example, in a blog system the type might be `blog` corresponding to a
21path of `blog/{article_id}`. Resource type must always be provided, but the id may be NULL. Nulls are interpreted as
22matching any id. So, a row that contains a permission for `type=blog id=NULL` is the default permission for that actor unless
23for any blog resource type unless a more specific permission is given to a resource by id.
24
25The actor can be one of two types: either an id for a specific actor (user id) or a reference to an attribute that defines
26a group of one or more users. In this way, permissions can be granted based on the specifics of the user or a persona
27created by an attribute.
28
29--- schema/001_base.sql
30-- +goose Up
31-- +goose StatementBegin
32CREATE TABLE IF NOT EXISTS permissions (
33 resource_type TEXT NOT NULL,
34 resource_id INTEGER,
35 user_id INTEGER,
36 attribute_id INTEGER,
37 permissions INTEGER NOT NULL
38);
39
40---
41
42Attributes are unique key, value pairs of any type.
43
44--- schema/001_base.sql +=
45CREATE TABLE IF NOT EXISTS attributes (key TEXT NOT NULL, value BLOB NOT NULL);
46
47---
48
49To assign one or more users to an attribute, a join table connects the unique key,value attribute with a user ID.
50
51--- schema/001_base.sql +=
52CREATE TABLE IF NOT EXISTS attribute_user (
53 attribute_id INTEGER NOT NULL,
54 user_id INTEGER NOT NULL
55);
56
57---
58
59A materialized view creates a table `abac` that consists of permissions rules associated either with a specific
60`user_id` or with NULL fields that represent default permissions for that `resource_type` or `resource_id`. The table
61is joined with the attributes table to create a permissions row for each user and resource. For example, if permissions
62are given to the attribute 'role: users' and you add give that attribute to three users, the materialized view creates
63three rows in the table, one for each `resource->user->permission` that is derived from the attribute permission.
64
65--- schema/001_base.sql +=
66CREATE VIEW IF NOT EXISTS abac (resource_type, resource_id, user_id, permissions) AS
67SELECT
68 permissions.resource_type,
69 permissions.resource_id,
70 COALESCE(permissions.user_id, attribute_user.user_id) AS user_id,
71 permissions.permissions
72FROM
73 permissions
74 LEFT JOIN attribute_user USING (attribute_id);
75
76---
77
78Unique indexes are used to ensure that there is a single unambigious row for each resource and user combination.
79
80--- schema/001_base.sql +=
81CREATE UNIQUE INDEX IF NOT EXISTS permission_idx_user ON permissions (resource_type, resource_id, user_id)
82WHERE
83 attribute_id IS NULL;
84
85CREATE UNIQUE INDEX IF NOT EXISTS permission_idx_att ON permissions (resource_type, resource_id, attribute_id)
86WHERE
87 user_id IS NULL;
88
89CREATE UNIQUE INDEX IF NOT EXISTS permission_idx_def_res ON permissions (resource_type, resource_id)
90WHERE
91 user_id IS NULL
92 AND attribute_id IS NULL;
93
94CREATE UNIQUE INDEX IF NOT EXISTS permission_idx_def_type ON permissions (resource_type)
95WHERE
96 resource_id IS NULL
97 AND user_id IS NULL
98 AND attribute_id IS NULL;
99
100CREATE UNIQUE INDEX IF NOT EXISTS attribute_user_idx ON attribute_user (attribute_id, user_id);
101
102CREATE UNIQUE INDEX IF NOT EXISTS attribute_idx ON attributes (key, value);
103-- +goose StatementEnd
104
105---
106
107And a corresponding rollback is provided for migrations.
108
109--- schema/001_base.sql +=
110-- +goose Down
111-- +goose StatementBegin
112DROP TABLE permissions;
113DROP TABLE attributes;
114DROP TABLE attribute_user;
115DROP INDEX permission_idx_user;
116DROP INDEX permission_idx_att;
117DROP INDEX permission_idx_def_res;
118DROP INDEX permission_idx_def_type;
119DROP INDEX attribute_user_idx;
120DROP INDEX attribute_idx;
121-- +goose StatementEnd
122---
123
124<h1>Adding and querying permissions</h1>
125
126@s Setting the default for a type
127
128ABAC provides a system for a fallback "default" permission to be set for a particular resource type or a specific
129resource identified by ID. This is a namespaced system where every resource is defined as `type:id` or can be mapped to
130URLs as `type/id`. For example, a blog may be identified by `blog/{id}`. To define default permissions for a type,
131fields `resource_id`, `user_id`, and `attribute_id` are `NULL`.
132
133--- set_default_for_type.sql
134INSERT INTO permissions (resource_type, permissions, resource_id, user_id)
135 VALUES (?, ?, NULL, NULL)
136ON CONFLICT
137 DO UPDATE SET permissions=excluded.permissions;
138---
139
140@s Setting the default for a resource
141
142Similarly, a particular resource identified by `type/id` can have a default permission set that applies to all users
143unless a more specific rule is associated with an actor.
144
145--- set_default_for_resource.sql
146INSERT INTO permissions (resource_type, resource_id, permissions, user_id)
147 VALUES (?, ?, ?, NULL)
148ON CONFLICT
149 DO UPDATE SET permissions=excluded.permissions;
150---
151
152@s Setting a permission for a user
153
154For a specific actor, permissions can apply to a particular resource identified by `resource_id` or a class of resources
155identified by `resource_type`.
156
157--- set_permission_for_user.sql
158INSERT INTO permissions (resource_type, resource_id, user_id, permissions)
159 VALUES (?, ?, ?, ?)
160ON CONFLICT
161 DO UPDATE SET permissions=excluded.permissions;
162---
163
164A user can also have a default permission for a type. This is often be used for blanket rules (e.g., banning an actor from accessing a class of resources or controlling administrative routes.)
165
166--- set_default_for_user.sql
167INSERT INTO permissions (resource_type, resource_id, user_id, permissions)
168 VALUES (?, NULL, ?, ?)
169ON CONFLICT
170 DO UPDATE SET permissions=excluded.permissions;
171---
172
173@s Setting a permission for an attribute
174
175Similarly, permissions can be set for an attribute. In the database, creation the attribute and assigning the permission
176happens in a transaction and uses conflict rules to ensure uniqueness. This provides a simplified interface for consumers
177who don't need to care about the database schema and can think instead of key/value attributes and permissions.
178
179--- set_permission_for_attribute.sql
180BEGIN TRANSACTION;
181INSERT INTO attributes (key, value) VALUES (?, ?) ON CONFLICT DO NOTHING;
182INSERT INTO permissions (resource_type, resource_id, attribute_id, permissions)
183 VALUES (
184 ?,
185 ?,
186 (SELECT rowid FROM attributes WHERE key=? AND value=?),
187 ?
188 )
189ON CONFLICT
190 DO UPDATE SET permissions=excluded.permissions;
191COMMIT;
192---
193
194An attribute can also be used to set the default permissions for a type.
195
196--- set_default_for_attribute.sql
197BEGIN TRANSACTION;
198INSERT INTO attributes (key, value) VALUES (?, ?) ON CONFLICT DO NOTHING;
199INSERT INTO permissions (resource_type, resource_id, attribute_id, permissions)
200 VALUES (
201 ?,
202 NULL,
203 (SELECT rowid FROM attributes WHERE key=? AND value=?),
204 ?
205 )
206ON CONFLICT
207 DO UPDATE SET permissions=excluded.permissions;
208COMMIT;
209---
210
211Attributes are set on a user and created in the database if needed.
212
213--- set_attribute_for_user.sql
214BEGIN TRANSACTION;
215INSERT INTO attributes (key, value) VALUES (?, ?) ON CONFLICT DO NOTHING;
216INSERT INTO attribute_user (attribute_id, user_id)
217 VALUES (
218 (SELECT rowid FROM attributes WHERE key=? AND value=?)
219 , ?)
220ON CONFLICT
221 DO NOTHING;
222COMMIT;
223---
224
225@s Querying permissions for a user
226
227Querying makes use of the database schema and ordering behavior to return a single permission that reflects
228this precedence:
229
230* (1) exact match of `type/id` with a rule that resolves to the `user_id` directly or from an attribute (highest permission wins)</li>
231* (2) default for the `resource_id`</li>
232* (3) default for the `resource_type`</li>
233
234--- get_permission.sql
235SELECT permissions FROM abac
236WHERE
237 resource_type=?
238AND
239 (resource_id=? OR resource_id IS NULL)
240AND
241 (user_id=? OR user_id IS NULL)
242ORDER BY
243 user_id NULLS LAST,
244 resource_id NULLS LAST,
245 permissions DESC
246LIMIT 1;
247---
