User Authorization with Postgres Row Level Security Policy

{
“sub”: “authenticated”,
“iat”: 1516239022,
“role”: “f918ffd9-a611-4b2a-b4bb-df8f25d7569f”
}

Table “storage.buckets”
Column | Type | Collation | Nullable | Default
————+————————–+———–+———-+———
id | text | | not null |
name | text | | not null |
owner | uuid | | |
created_at | timestamp with time zone | | | now()
updated_at | timestamp with time zone | | | now()

— generate a UUID as the role name since it needs to match the owner type
SELECT gen_random_uuid(); — f918ffd9-a611-4b2a-b4bb-df8f25d7569f

CREATE ROLE “f918ffd9-a611-4b2a-b4bb-df8f25d7569f”;
GRANT all ON schema storage TO “f918ffd9-a611-4b2a-b4bb-df8f25d7569f”;
GRANT all on buckets TO “f918ffd9-a611-4b2a-b4bb-df8f25d7569f”;
— generate another role
CREATE ROLE “11b795e0-a566-491b-9ee7-62c025175dd8”;
GRANT all ON schema storage TO “11b795e0-a566-491b-9ee7-62c025175dd8”;
GRANT all on buckets TO “11b795e0-a566-491b-9ee7-62c025175dd8”;

CREATE OR REPLACE FUNCTION user_record_count(uuid) RETURNS integer AS $$
DECLARE
count integer;
BEGIN
SELECT COUNT(*) INTO count
FROM buckets
WHERE owner = $1;

RETURN count;
END;
$$ LANGUAGE plpgsql;

CREATE POLICY limit_user_crud ON buckets
USING (owner = current_user::uuid)
WITH CHECK (
(SELECT user_record_count(current_user::uuid) < 3)
);

SET ROLE “f918ffd9-a611-4b2a-b4bb-df8f25d7569f”;

INSERT INTO buckets (id, name, owner) VALUES (‘1’, ‘one’, current_user::uuid);
INSERT INTO buckets (id, name, owner) VALUES (‘2’, ‘two’, current_user::uuid);
INSERT INTO buckets (id, name, owner) VALUES (‘3’, ‘three’, current_user::uuid);
— check before the insertion
INSERT INTO buckets (id, name, owner) VALUES (‘4’, ‘four’, current_user::uuid); — ERROR: new row violates row-level security policy for table “buckets”
SELECT * FROM buckets; — this returns 3 rows

SET role “11b795e0-a566-491b-9ee7-62c025175dd8”;
SELECT * FROM buckets; — this returns nothing
INSERT INTO buckets (id, name, owner) VALUES (‘4’, ‘four’, current_user::uuid); — success
DELETE FROM buckets where id = ‘4’; — success
DELETE FROM buckets where id = ‘1’; — delete 0