-- SETUP --

-- login as admin
sudo su - postgres
psql

-- create roles
create role red login password 'red';
create role blue login password 'blue';
create role green login password 'green';

-- create database
create database allcols;

-- open terminals for each user and log in
psql -h localhost -d allcols -U red    

-- 1 -- USER VS ROLE

-- create role without login permission
create role yellow;

-- grant login permission
alter role yellow login password 'yellow';

-- 2 -- GRANT SELECT

-- red:
create table redtable (i int);
insert into redtable values (1), (2), (3);
select * from redtable;

-- blue: 
select * from redtable;

-- red:
grant select on redtable to blue;

-- blue:
select * from redtable;

-- 2 -- AUTHORIZAION GRAPH

-- postgres
\c allcols
create table r (i int);
insert into r values (10), (20), (30);
\z
grant select on r to blue with grant option;
grant SELECT on r to green with grant option;
\z

-- blue
grant select on r to red;
grant select on r to yellow;

-- green
grant select on r to yellow;

-- postgres
revoke select on r from blue;
revoke select on r from blue cascade;

