-- Remove changes TO allow re-running the example
DROP TABLE IF EXISTS colors_shapes;
DROP ROLE alpha; 
DROP ROLE beta;
DROP ROLE gamma;
DROP ROLE blue_role;
DROP ROLE purple_role;
DROP ROLE triangle_role;
DROP ROLE heart_role;
DROP ROLE circle_role;
DROP ROLE square_role;

-- Create real user roles
CREATE ROLE alpha;
CREATE ROLE beta;
CREATE ROLE gamma;

-- Populate the sample table
CREATE table colors_shapes (id integer, color varchar(20), shape varchar(20));
INSERT INTO colors_shapes values (1,'blue_role','triangle_role');
INSERT INTO colors_shapes values (2, 'red_role', 'heart_role');
INSERT INTO colors_shapes values (3, 'purple_role', 'circle_role');

-- Create permissions granting roles and grant a subset of them to the users
CREATE ROLE blue_role;
CREATE ROLE red_role;
CREATE ROLE purple_role;
CREATE ROLE triangle_role;
CREATE ROLE heart_role;
CREATE ROLE circle_role;
CREATE ROLE square_role;

GRANT blue_role TO alpha;
GRANT purple_role TO alpha;
GRANT purple_role TO beta;
GRANT triangle_role TO alpha;
GRANT circle_role TO alpha;
GRANT square_role TO alpha;
GRANT red_role TO gamma;
GRANT heart_role TO gamma;

-- Only allow viewing a row if the user has a matching <color>_role and <shape>_role entry
ALTER TABLE colors_shapes
  SET ROW SECURITY FOR ALL TO (
  color IN (
    SELECT permission_role.rolname FROM pg_roles active_role, pg_auth_members, pg_roles permission_role WHERE
      active_role.rolname = current_user AND
      active_role.oid = pg_auth_members.member AND
      permission_role.oid = pg_auth_members.roleid
    )
  AND shape IN (
    SELECT permission_role.rolname FROM pg_roles active_role, pg_auth_members, pg_roles permission_role WHERE
      active_role.rolname = current_user AND
      active_role.oid = pg_auth_members.member AND
      permission_role.oid = pg_auth_members.roleid
    )
);
GRANT ALL ON colors_shapes TO alpha;
GRANT ALL ON colors_shapes TO beta;
GRANT ALL ON colors_shapes TO gamma;
SELECT relname,relhasrowsecurity FROM pg_class WHERE relhasrowsecurity;

-- Show the full working data set as superuser
SELECT * FROM colors_shapes;

-- Demonstrate the three views 
SET SESSION AUTHORIZATION alpha;
SELECT current_user, * FROM colors_shapes;
SET SESSION AUTHORIZATION beta;
SELECT current_user, * FROM colors_shapes;
SET SESSION AUTHORIZATION gamma;
SELECT current_user, * FROM colors_shapes;