rls_policy_setup.sql

CREATE
  TABLE
    users(
      id SERIAL PRIMARY KEY
    );

CREATE
  TABLE
    orgs(
      id SERIAL PRIMARY KEY
    );

CREATE
  TABLE
    org_members(
      user INTEGER REFERENCES users NOT NULL,
      org INTEGER REFERENCES orgs NOT NULL
    );

-- ** RLS setup **
ALTER TABLE
  orgs ENABLE ROW LEVEL SECURITY;

-- Create a function, current_app_user(),
-- that returns the user to authorize against.
CREATE
  FUNCTION current_app_user() RETURNS INTEGER AS $$ SELECT
    NULLIF(
      current_setting(
        'app.current_app_user',
        TRUE
      ),
      ''
    )::INTEGER $$ LANGUAGE SQL SECURITY DEFINER;

CREATE
  POLICY org_member_policy ON
  orgs
    USING(
    EXISTS(
      SELECT
        1
      FROM
        org_members
      WHERE
        user = current_app_user()
        AND org = id
    )
  );

-- Create the db user that'll be used in your application.
CREATE
  USER app_user;

GRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA public TO app_user;

GRANT ALL PRIVILEGES ON
ALL SEQUENCES IN SCHEMA public TO app_user;