complex_rls_policy.sql

CREATE
  TABLE
    users(
      id SERIAL PRIMARY KEY,
      is_admin BOOLEAN
    );

ALTER TABLE
  users ENABLE ROW LEVEL SECURITY;

-- Users can do anything to themselves.
CREATE
  POLICY self_policy ON
  users
    USING(
    id = current_app_user()
  );

CREATE
  FUNCTION is_user_admin(
    _user_id INTEGER
  ) RETURNS bool AS $$ SELECT
    EXISTS(
      SELECT
        1
      FROM
        users
      WHERE
        id = _user_id
        AND is_admin = TRUE
    ) $$ LANGUAGE SQL SECURITY DEFINER;

CREATE
  FUNCTION do_users_share_org(
    _user_id_1 INTEGER,
    _user_id_2 INTEGER
  ) RETURNS bool AS $$ SELECT
    EXISTS(
      SELECT
        1
      FROM
        org_members om1,
        org_members om2
      WHERE
        om1.user != om2.user
        AND om1.org = om2.org
        AND om1.user = _user_id_1
        AND om2.user = _user_id_2
    ) $$ LANGUAGE SQL SECURITY INVOKER;

-- Non-admins can only read users in their orgs.
CREATE
  POLICY read_in_shared_orgs_policy ON
  users FOR SELECT
      USING(
      do_users_share_org(
        current_app_user(),
        id
      )
    );

CREATE
  POLICY admin_policy ON
  users
    USING(
    do_users_share_org(
      current_app_user(),
      id
    )
    AND is_user_admin(
      current_app_user()
    )
  );