Give roles inside an organisation

How to do it

One person can own one organisation and be a viewer in another, which a role column on the user cannot say. Store user, organisation and role, unique on the pair. Read the role for the organisation named in the request's address, never the one the user saw last. Staff roles live apart, as use separate staff accounts says.

Example

CREATE TABLE memberships (
  user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  organisation_id uuid NOT NULL REFERENCES organisations (id) ON DELETE CASCADE,
  role text NOT NULL CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
  PRIMARY KEY (organisation_id, user_id)
);

How to check

Make a test user owner of organisation A and viewer in B. An owner action in B answers 403, and the same action in A succeeds.