User Authorization with Postgres Row Level Security Policy

Supabase has a storage gateway that uses RLS for authorization.

It requires a JWT that provides the role information to perform the SQL, here is an example of the JWT payload:

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

The storage bucket table is:

                        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()

You only need to set up the correct RLS policy in the database. Here is an example:

-- 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