87E2SLju

1BEGIN;
2
3CREATE SCHEMA app_public; -- tables and functions to be exposed to GraphQL
4/* CREATE SCHEMA app_hidden; -- same privileges as app_public, but simply not exposed to GraphQL */
5CREATE SCHEMA app_private; -- secrets that require elevated privileges to access
6
7-- Prelude -------------------------------------------------------
8
9ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON functions FROM PUBLIC; -- by default, all functions can be executed by anyone (public role). Remove this feature to set permissions explicitly in future using `GRANT EXECUTE ON FUNCTION function_name TO role_names`
10
11CREATE extension IF NOT EXISTS "pgcrypto";
12
13CREATE FUNCTION app_private.set_updated_at() RETURNS TRIGGER AS $$
14begin
15  new.updated_at := current_timestamp;
16  return new;
17end;
18$$ LANGUAGE plpgsql;
19
20CREATE ROLE app_anonymous;
21CREATE ROLE app_user;
22
23GRANT USAGE ON SCHEMA app_public TO app_anonymous, app_user;
24GRANT USAGE ON SCHEMA app_private TO app_user;
25
26COMMIT;
27
28
29BEGIN;
30
31-- Users table -------------------------------------------------------
32
33CREATE TABLE app_public.users (
34  id
35  serial
36  PRIMARY KEY
37, first_name
38  varchar(255)
39  NOT NULL
40, last_name
41  varchar(255)
42  NOT NULL
43, created_at
44  timestamp
45  NOT NULL
46  DEFAULT now()
47, updated_at
48  timestamp
49  NOT NULL
50  DEFAULT now()
51);
52
53ALTER TABLE app_public.users ENABLE ROW LEVEL SECURITY;
54
55CREATE TABLE app_private.user_accounts (
56  user_id
57  integer
58  NOT NULL
59  PRIMARY KEY -- PK
60  REFERENCES app_public.users(id) -- and also FK
61  ON DELETE CASCADE -- if app_public.users deleted, delete also app_private.user_accounts
62, email
63  varchar(255)
64  NOT NULL
65  UNIQUE
66  CHECK (email ~ '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
67, password_hash
68  text
69  NOT NULL
70);
71
72-- User triggers ----------------------------------------------------
73
74CREATE TRIGGER user_updated_at BEFORE UPDATE
75  ON app_public.users
76  FOR EACH ROW
77  EXECUTE PROCEDURE app_private.set_updated_at();
78
79-- User functions ----------------------------------------------------
80
81CREATE FUNCTION app_public.users_full_name(users app_public.users) RETURNS text AS $$
82  select users.first_name || ' ' || users.last_name
83$$ LANGUAGE SQL STABLE;
84
85COMMENT ON FUNCTION app_public.users_full_name(app_public.users) IS 'A user’s full name which is a concatenation of their first and last name.';
86
87CREATE FUNCTION app_public.register_user(
88  first_name text,
89  last_name  text,
90  email      text,
91  password   text
92) RETURNS app_public.users AS $$
93declare
94  users app_public.users;
95begin
96  insert into app_public.users (first_name, last_name) values
97    (first_name, last_name)
98    returning * into users;
99
100  insert into app_private.user_accounts (user_id, email, password_hash) values
101    (users.id, email, crypt(password, gen_salt('bf')));
102
103  return users;
104end;
105$$ LANGUAGE plpgsql STRICT SECURITY DEFINER;
106
107COMMENT ON FUNCTION app_public.register_user(text, text, text, text) IS 'Registers a single user and creates an account in our forum.';
108
109-- User authentication -------------------------------------------------------
110
111CREATE TYPE app_public.jwt AS (
112  ROLE text,
113  user_id integer
114);
115
116CREATE FUNCTION app_public.authenticate(
117  email text,
118  password text
119) RETURNS app_public.jwt AS $$
120declare
121  account app_private.user_accounts;
122begin
123  select a.* into account
124  from app_private.user_accounts as a
125  where a.email = $1;
126
127  if account.password_hash = crypt(password, account.password_hash) then
128    return ('app_user', account.user_id)::app_public.jwt;
129  else
130    return null;
131  end if;
132end;
133$$ LANGUAGE plpgsql
134  STRICT -- the function always returns null whenever any of its arguments are null
135  SECURITY DEFINER; -- function is to be executed with the privileges of the user that created it
136
137COMMENT ON FUNCTION app_public.authenticate(text, text) IS 'Creates a JWT token that will securely identify a users and give them certain permissions.';
138
139CREATE FUNCTION app_public.current_user() RETURNS app_public.users AS $$
140  select *
141  from app_public.users
142  where id = current_setting('jwt.claims.user_id', true)::integer
143$$ LANGUAGE SQL
144  STABLE; -- function has no side effects, can be cached within a single table
145
146COMMENT ON FUNCTION app_public.current_user() IS 'Gets the users who was identified by our JWT.';
147
148CREATE FUNCTION app_public.current_user_id() RETURNS integer AS $$
149  select id
150  from app_public.current_user()
151$$ LANGUAGE SQL STABLE;
152
153COMMENT ON FUNCTION app_public.current_user() IS 'Gets the users id who was identified by our JWT.';
154
155CREATE FUNCTION app_public.users_emails(users app_public.users) RETURNS varchar[] AS $$
156  SELECT ARRAY(
157    SELECT email
158    FROM   app_private.user_accounts
159    WHERE  user_id = users.id
160  )
161$$ LANGUAGE SQL STABLE;
162
163COMMENT ON FUNCTION app_public.users_emails(users app_public.users) IS 'List of user emails';
164
165-- User autorization ---------------------------------------------------------
166
167GRANT SELECT ON TABLE app_public.users TO app_anonymous, app_user;
168GRANT SELECT ON TABLE app_private.user_accounts TO app_user;
169
170GRANT UPDATE, DELETE ON TABLE app_public.users TO app_user;
171
172GRANT EXECUTE ON FUNCTION app_public.users_full_name(app_public.users) TO app_anonymous, app_user;
173GRANT EXECUTE ON FUNCTION app_public.users_emails(users app_public.users) TO app_anonymous, app_user;
174GRANT EXECUTE ON FUNCTION app_public.authenticate(text, text) TO app_anonymous, app_user;
175GRANT EXECUTE ON FUNCTION app_public.current_user() TO app_anonymous, app_user;
176GRANT EXECUTE ON FUNCTION app_public.current_user_id() TO app_anonymous, app_user;
177GRANT EXECUTE ON FUNCTION app_public.register_user(text, text, text, text) TO app_anonymous;
178
179-- TODO:
180-- why
181--
182-- without `CREATE policy select_users ON app_public.users FOR SELECT TO app_user USING (TRUE);`
183-- - I can select all users in ordinary query
184-- - I can't select users in query in function
185--
186-- with:
187-- - I can select all users in ordinary query
188-- - I can select users in query in function
189
190CREATE POLICY select_user_accounts ON app_private.user_accounts FOR SELECT TO app_user
191  USING (TRUE);
192
193CREATE POLICY select_users ON app_public.users FOR SELECT TO app_user
194  USING (TRUE);
195
196CREATE POLICY update_users ON app_public.users FOR UPDATE TO app_user
197  USING (id = current_setting('jwt.claims.person_id', TRUE)::integer);
198
199CREATE POLICY delete_users ON app_public.users FOR DELETE TO app_user
200  USING (id = current_setting('jwt.claims.person_id', TRUE)::integer);
201
202COMMIT;