I have postgres (13.2) based API with RLS enabled (I use postgraphile) and it's extremely slow. User sends JWT from Google OAuth. Access to tables are based on roles (there are 2: person, admin) + RLS. I have 2 tables for users auth: person, person_groups
CREATE TABLE IF NOT EXISTS myschema.person_groups (
id serial PRIMARY KEY,
person_id citext NOT NULL REFERENCES myschema.person (id),
google_id text NOT NULL REFERENCES myschema_private.person_account (google_id),
group_id serial NOT NULL REFERENCES myschema.groups (id),
updated_at timestamp DEFAULT now(),
CONSTRAINT unq_person_id_group_id UNIQUE (person_id, group_id)
);
CREATE INDEX persongroups_google_group_idx ON myschema.person_groups (google_id, group_id);
For RLS to check I have function specified as:
CREATE OR REPLACE FUNCTION myschema.is_in_group (group_id int[])
RETURNS boolean
AS $$
SELECT
CASE WHEN current_setting('role', FALSE) = 'admin' THEN
TRUE
WHEN EXISTS (
SELECT
1
FROM
myschema.person_groups
WHERE
person_groups.group_id = ANY ($1) AND person_groups.google_id = current_setting('user.sub', TRUE)) THEN
TRUE
ELSE
FALSE
END
$$
LANGUAGE SQL
STABLE
STRICT
SECURITY DEFINER;
I have table: "gate_enterlogs", which user wants to access. RLS for this table is:
CREATE POLICY select_gate_enterlog ON myschema.gate_enterlog
FOR SELECT TO person
USING (myschema.is_in_group (ARRAY[6, 1]));
If I use such code:
BEGIN;
SET local ROLE person;
SET local "user.sub" TO 'yyy';
EXPLAIN ANALYZE VERBOSE
SELECT COUNT(id) FROM myschema.gate_enterlog;
COMMIT;
I end up with:
Aggregate (cost=23369.00..23369.01 rows=1 width=8) (actual time=2897.487..2897.487 rows=1 loops=1)
Output: count(id)
-> Seq Scan on myschema.gate_enterlog (cost=0.00..23297.08 rows=28769 width=4) (actual time=2897.484..2897.484 rows=0 loops=1)
Output: id, person_id, checkpoint_time, direction, place
Filter: is_in_group('{6,1}'::integer[])
Rows Removed by Filter: 86308
Planning Time: 0.626 ms
Execution Time: 2897.567 ms
If I disable RLS policy:
CREATE POLICY select_gate_enterlog ON myschema.gate_enterlog FOR SELECT TO person USING (TRUE);
Aggregate (cost=1935.85..1935.86 rows=1 width=8) (actual time=17.671..17.672 rows=1 loops=1)
Output: count(id)
-> Seq Scan on myschema.gate_enterlog (cost=0.00..1720.08 rows=86308 width=4) (actual time=0.008..7.364 rows=86308 loops=1)
Output: id, person_id, checkpoint_time, direction, place
Planning Time: 0.594 ms
Execution Time: 17.737 ms
Do you have any thoughts how can I optimize RLS so postgres would "remember" that user has privileges to access table. My only idea is to end up with USING (TRUE) for select and grant access once before calling query, but before going that way I hope that somebody can give me a hint what I did wrong
is_in_group('{6,1}'::integer[])
is a constant expression that evaluates toFALSE
, then skips scanning the table… – TelluricGRANT SELECT …
) to the table, and deny it for normal users altogether. – Telluricis_in_group
With this approach admin user can add groups, can add user to groups via api. With role approach as far as I can understand admin can't create 'roles' (as it's not normal table) via api. Anyway, I'm very curious what I'm doing wrong that after first run ofis_in_group('{6,1}'::integer[])
value it's not cached against other rows. – Juniuser_id
to the database, it also gets therole
from the JWT and uses that for the session. I don't know how you configure this with Google, but adding a user to a group would then need to be done via that (Google) API that issues the tokens. I don't see how adding new groups is relevant here, since the membership check is hardcoded anyway in your case. But yeah, this approach gets a bit more complicated if you also need dynamic groups – Telluric