
CREATE TABLE friends (
  id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  person_id INTEGER NOT NULL REFERENCES people,
  friend_id INTEGER NOT NULL REFERENCES people
);

CREATE ROLE self;
GRANT SELECT (id, email, password) ON people TO self;
GRANT SELECT ON friends TO self;
CREATE POLICY self
ON people
TO self
USING (id = CURRENT_SETTING('app.user_id')::INTEGER);

CREATE ROLE friend;
GRANT SELECT (id, email) ON people TO friend;
GRANT SELECT ON friends TO friend;
CREATE POLICY friend
ON people
TO friend
USING (
  EXISTS (
    SELECT true
    FROM friends f
    WHERE f.person_id = people.id
      AND f.friend_id = CURRENT_SETTING('app.user_id')::INTEGER
  )
)
WITH CHECK (false);

CREATE ROLE person INHERIT;
GRANT self TO person;
GRANT friend TO person;

INSERT INTO friends (person_id, friend_id) VALUES
  (1,2), (2,1);
