Postgrest rbac

gistfile1.txt

CREATE TABLE user_roles (
    uid UUID PRIMARY KEY,
    role TEXT
);

-- this helper function returns the current claims set by the db_pre_request function
CREATE OR REPLACE FUNCTION req()
    RETURNS "jsonb"
    LANGUAGE "sql"
    STABLE
AS $$
  select coalesce(current_setting('request.claims', true), '{}')::JSONB
$$;

CREATE TABLE "public"."user_roles"(
    "uid" "uuid" NOT NULL,
    "role" "text" NOT NULL
);
ALTER TABLE "public"."user_roles" OWNER TO "postgres";
ALTER TABLE ONLY "public"."user_roles"
    ADD CONSTRAINT "user_roles_pkey" PRIMARY KEY ("uid");
ALTER TABLE ONLY "public"."user_roles"
    ADD CONSTRAINT "user_roles_uid_fkey" FOREIGN KEY ("uid") REFERENCES "auth"."users"("id") ON DELETE CASCADE;
    
    CREATE OR REPLACE FUNCTION db_pre_request()
    RETURNS jsonb
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path = public
AS $$
DECLARE
    claims jsonb;
    user_role text;
    headers jsonb;
BEGIN
    -- get the claims from the JWT
    SELECT coalesce(current_setting('request.jwt.claims', TRUE), '{}') INTO claims;
    SELECT coalesce(current_setting('request.headers', TRUE), '{}') INTO headers;
    claims := claims || jsonb_build_object('headers', headers);
    -- ******************************** CUSTOM CLAIMS ********************************
    -- get user_role from the user_roles table and add it to the claims
    SELECT role FROM user_roles
    WHERE uid =((claims::jsonb) ->> 'sub')::uuid 
    INTO user_role;
    claims := claims || jsonb_build_object('user_role', user_role);
    -- *******************************************************************************
    PERFORM set_config('request.claims'::text, claims::text, FALSE /* is_local */);
    RETURN claims;
END;
$$;


ALTER ROLE authenticator SET pgrst.db_pre_request TO 'public.db_pre_request';
NOTIFY pgrst,
'reload config';