-
Notifications
You must be signed in to change notification settings - Fork 52
/
default-permissions.sql
105 lines (79 loc) · 4.28 KB
/
default-permissions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
/******************************************************************************
* Default permissions
*
* Created by Aquameta Labs, an open source company in Portland Oregon, USA.
* Company: http://aquameta.com/
* Project: http://blog.aquameta.com/
******************************************************************************/
begin;
set search_path=meta;
/*
Tables with RLS enabled
-- These tables need to have a policy defined for the user group that wants to use them, unless the user group is set to bypass RLS
- endpoint.resource
*/
alter table endpoint.resource enable row level security;
------------------------
-- Anonymous permissions
------------------------
-- schema privileges
grant usage on schema widget to anonymous;
grant usage on schema endpoint to anonymous;
grant usage on schema meta to anonymous;
grant usage on schema bundle to anonymous;
grant usage on schema filesystem to anonymous; -- TODO: insecure
-- table privileges
insert into meta.table_privilege (schema_name, relation_name, role_name, "type")
values ('endpoint', 'mimetype', 'anonymous', 'select'),
('endpoint', 'session', 'anonymous', 'select'),
('endpoint', 'resource', 'anonymous', 'select'),
('endpoint', 'mimetype_extension', 'anonymous', 'select'),
('endpoint', 'resource_file', 'anonymous', 'select'),
('endpoint', 'resource_binary', 'anonymous', 'select'),
('endpoint', 'resource_directory', 'anonymous', 'select'),
('endpoint', 'column_mimetype', 'anonymous', 'select'),
('endpoint', 'current_user', 'anonymous', 'select'),
('bundle', 'bundle', 'anonymous', 'select'),
('filesystem', 'file', 'anonymous', 'select'), -- TODO: insecure?
('filesystem', 'directory', 'anonymous', 'select'), -- TODO: insecure?
('widget', 'dependency_js', 'anonymous', 'select'),
('widget', 'widget', 'anonymous', 'select'),
('widget', 'widget_dependency_js', 'anonymous', 'select'),
('widget', 'widget_view', 'anonymous', 'select'),
('widget', 'input', 'anonymous', 'select'),
('meta', 'column', 'anonymous', 'select'),
('meta', 'function', 'anonymous', 'select');
-- function privileges
grant execute on function endpoint.register(text, text, boolean) to anonymous;
grant execute on function endpoint.register_confirm(text, text) to anonymous;
grant execute on function endpoint.login(text, text) to anonymous;
-- row level security permissions
-- endpoint.resource - RLS
insert into meta.policy (name, schema_name, relation_name, command, "using")
values ( 'resource_anonymous', 'endpoint', 'resource', 'select', 'path in (''/'', ''/login'', ''/register'', ''/confirm'') or path like ''%.js''');
insert into meta.policy_role (policy_name, schema_name, relation_name, role_name) values ('resource_anonymous', 'endpoint', 'resource', 'anonymous');
---------------------------
-- Generic user permissions
---------------------------
-- schema privileges
grant usage on schema endpoint to "user";
grant usage on schema filesystem to "user";
grant usage on schema widget to "user";
grant usage on schema meta to "user";
grant usage on schema semantics to "user";
grant usage on schema bundle to "user";
-- table privileges
grant select on all tables in schema endpoint to "user";
grant select on all tables in schema filesystem to "user";
grant select on all tables in schema widget to "user";
grant select on all tables in schema meta to "user";
grant select on all tables in schema semantics to "user";
grant select on all tables in schema bundle to "user";
grant delete on endpoint.session to "user"; -- TODO: insecure
grant execute on all functions in schema widget to "user";
-- row level security permissions
-- endpoint.resource - RLS
insert into meta.policy (name, schema_name, relation_name, command, "using")
values ( 'resource_user', 'endpoint', 'resource', 'select', 'true');
insert into meta.policy_role (policy_name, schema_name, relation_name, role_name) values ('resource_user', 'endpoint', 'resource', 'user');
end;