PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Row level security policy not working when checking the same table

I have two users: alice and bob.
I have one organization: contoso.
Alice is member of contoso.

What I want is for alice to be able to add bob as member of contoso since alice is member of contoso. I wrote the policies below and the USING and WITH CHECK conditions work for the locations table, but not for the memberships table.

Any idea what could be the proper fix here? Thank you.

DROP SCHEMA IF EXISTS public CASCADE;

CREATE SCHEMA public;

CREATE TABLE public.users (
  "id" uuid DEFAULT gen_random_uuid () PRIMARY KEY NOT NULL,
  "username" text NOT NULL,
    "created_at" timestamptz DEFAULT now() NOT NULL
);
CREATE TABLE public.organizations (
    "id" uuid DEFAULT gen_random_uuid () PRIMARY KEY NOT NULL,
    "name" text NOT NULL,
    "created_at" timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE public.memberships (
    "id" uuid DEFAULT gen_random_uuid () PRIMARY KEY NOT NULL,
    "organization_id" uuid REFERENCES public.organizations ON DELETE CASCADE NOT NULL,
    "user_id" uuid REFERENCES public.users ON DELETE CASCADE NOT NULL,
    "created_at" timestamptz DEFAULT now() NOT NULL,
    UNIQUE ("organization_id", "user_id")
);

CREATE TABLE public.locations (
    "id" uuid DEFAULT gen_random_uuid () PRIMARY KEY NOT NULL,
    "organization_id" uuid REFERENCES public.organizations ON DELETE CASCADE NOT NULL,
    "name" text NOT NULL
);

DROP ROLE IF EXISTS user_role;

CREATE ROLE user_role NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN NOREPLICATION NOBYPASSRLS;
GRANT USAGE ON SCHEMA public TO user_role;

GRANT SELECT ("id", "username", "created_at") ON public.users TO user_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.organizations TO user_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.memberships TO user_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.locations TO user_role;

ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.memberships ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.locations ENABLE ROW LEVEL SECURITY;

CREATE POLICY users_select ON public.users FOR SELECT TO user_role
USING (true);

CREATE POLICY organizations_select ON public.organizations FOR SELECT TO user_role
USING (true);

CREATE POLICY organizations_insert ON public.organizations FOR INSERT TO user_role
WITH CHECK (true);

CREATE POLICY organizations_update ON public.organizations FOR UPDATE TO user_role
USING (
  EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.organization_id = organizations.id
    AND memberships.user_id = current_setting('app.sub')::uuid
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.organization_id = organizations.id
    AND memberships.user_id = current_setting('app.sub')::uuid
  )
);

CREATE POLICY memberships_select ON public.memberships FOR SELECT TO user_role
USING (
  "user_id" = current_setting('app.sub')::uuid
);

CREATE POLICY memberships_insert ON public.memberships FOR INSERT TO user_role
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.organization_id = organization_id
    AND memberships.user_id = current_setting('app.sub')::uuid
  )
);

CREATE POLICY locations_select ON public.locations FOR SELECT TO guest_role, user_role
USING (true);

CREATE POLICY locations_insert ON public.locations FOR INSERT TO user_role
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.organization_id = organization_id
    AND memberships.user_id = current_setting('app.sub')::uuid
  )
);

CREATE POLICY locations_update ON public.locations FOR UPDATE TO user_role
USING (
  EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.organization_id = organization_id
    AND memberships.user_id = current_setting('app.sub')::uuid
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.organization_id = organization_id
    AND memberships.user_id = current_setting('app.sub')::uuid
  )
);

CREATE POLICY locations_delete ON public.locations FOR DELETE TO user_role
USING (
  EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.organization_id = organization_id
    AND memberships.user_id = current_setting('app.sub')::uuid
  )
);

-- i have two users: alice and bob
INSERT INTO public.users ("username") VALUES ('alice');
INSERT INTO public.users ("username") VALUES ('bob');

-- i have one organization: contoso
INSERT INTO public.organizations ("name") VALUES ('contoso');

-- alice is member of contoso
INSERT INTO public.memberships ("organization_id", "user_id")
VALUES (
  (SELECT "id" FROM public.organizations WHERE "name" = 'contoso'),
  (SELECT "id" FROM public.users WHERE "username" = 'alice')
);

-- alice must be able to add location. this works.
BEGIN;
  SET LOCAL ROLE user_role;
  SELECT set_config('app.sub', (SELECT "id" FROM public.users WHERE "username" = 'alice')::text, true);
  INSERT INTO public.locations ("organization_id", "name")
  VALUES (
    (SELECT "id" FROM public.organizations WHERE "name" = 'contoso'),
    'alice-location'
  ) RETURNING *;
COMMIT;

-- bob must not be able to add location. this throws as expected.
BEGIN;
  SET LOCAL ROLE user_role;
  SELECT set_config('app.sub', (SELECT "id" FROM public.users WHERE "username" = 'bob')::text, true);
  -- INSERT INTO public.locations ("organization_id", "name")
  -- VALUES (
  --   (SELECT "id" FROM public.organizations WHERE "name" = 'contoso'),
  --   'bob-location'
  -- ) RETURNING *;
COMMIT;

-- i want alice to be able to add bob to contoso. this throws unexpectedly.
BEGIN;
  SET LOCAL ROLE user_role;
  SELECT set_config('app.sub', (SELECT "id" FROM public.users WHERE "username" = 'alice')::text, true);
  INSERT INTO public.memberships ("organization_id", "user_id")
  VALUES (
    (SELECT "id" FROM public.organizations WHERE "name" = 'contoso'),
    (SELECT "id" FROM public.users WHERE "username" = 'bob')
  ) RETURNING *;
COMMIT;

-- SELECT * FROM public.users;
-- SELECT * FROM public.organizations;
-- SELECT * FROM public.memberships;

I tried using different variations of the policies but they don’t seem to work on the same table.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    problem is at memberships_select

    fix was to put the following block at memberships_select policy so users can access their own memberships, but 1.) organization_id is ambigous here, and 2.) this results in a recursive check since it invokes security checks.

    EXISTS (
  SELECT 1 FROM public.memberships
  WHERE public.memberships.organization_id = organization_id
  AND public.memberships.user_id = current_setting('app.sub')::uuid
)

    solution was to use 1. a function that uses a non-ambigous parameter oid in place of organization_id, and 2.) a function that uses explicit security definer instead of the implicit security invoker in order to avoid the recursive security checks.

    
CREATE OR REPLACE FUNCTION is_organization_member (oid uuid)
RETURNS boolean LANGUAGE plpgsql SECURITY DEFINER
AS $$
  BEGIN
    RETURN EXISTS (
      SELECT 1 FROM public.memberships
      WHERE public.memberships.organization_id = oid
      AND public.memberships.user_id = current_setting('app.sub')::uuid
    );
  END;
$$;


CREATE POLICY memberships_select ON public.memberships FOR SELECT TO user_role
USING (is_organization_member(organization_id));

CREATE POLICY memberships_insert ON public.memberships FOR INSERT TO user_role
WITH CHECK (is_organization_member(organization_id));

CREATE POLICY memberships_delete ON public.memberships FOR DELETE TO user_role
USING (is_organization_member(organization_id));


CREATE POLICY locations_select ON public.locations FOR SELECT TO guest_role, user_role
USING (true);

CREATE POLICY locations_insert ON public.locations FOR INSERT TO user_role
WITH CHECK (is_organization_member(organization_id));

CREATE POLICY locations_update ON public.locations FOR UPDATE TO user_role
USING (is_organization_member(organization_id))
WITH CHECK (is_organization_member(organization_id));

CREATE POLICY locations_delete ON public.locations FOR DELETE TO user_role
USING (is_organization_member(organization_id));

    That's pretty much it.

    i wanna thank me for answering my own question.


  2. 0

    You have to correct memberships_insert in this way:

    CREATE POLICY memberships_insert ON public.memberships FOR INSERT TO user_role
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.memberships
    WHERE memberships.organization_id = NEW.organization_id
    AND memberships.user_id = current_setting('app.sub')::uuid
  )
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search