/*s*/DROP USER peon1;
/*s*/DROP USER peon2;
/*s*/DROP USER peon3;
/*s*/DROP ROLE r1;
/*s*/DROP ROLE r2;
/*s*/DROP ROLE r3;
/*s*/DROP ROLE r12;
DROP TABLE t1 IF exists;
DROP TABLE t2 IF exists;
DROP TABLE t3 IF exists;
DROP TABLE t4 IF exists;
CREATE TABLE t1(i int);
CREATE TABLE t2(i int);
CREATE TABLE t3(i int);
CREATE TABLE t4(i int);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2);
INSERT INTO t3 VALUES(3);
INSERT INTO t4 VALUES(4);
COMMIT;
CREATE USER peon1 PASSWORD password;
CREATE USER peon2 PASSWORD password;
CREATE USER peon3 PASSWORD password;

/*u0*/CREATE ROLE r1;
/*u0*/CREATE ROLE r2;
/*u0*/CREATE ROLE r3;
/*u0*/CREATE ROLE r12;
/*u0*/GRANT ALL ON t1 TO r1;
/*u0*/GRANT ALL ON t1 TO r12;
/*u0*/GRANT ALL ON t2 TO r2;
/*u0*/GRANT ALL ON t2 TO r12;
/*u0*/GRANT ALL ON t3 TO r3;

-- Can't mix right-grants and role-grants
/*e*/GRANT r1, SELECT ON t1 TO peon1;
/*e*/GRANT SELECT ON t1, r1 TO peon1;

/*u0*/GRANT SELECT, INSERT ON t1 TO peon1;
/*u0*/GRANT r2, r3 TO peon2;
/*u0*/GRANT r12 TO peon3;

CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*e*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon2 PASSWORD password;
/*e*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*c1*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon3 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER sa PASSWORD '';
/*u0*/GRANT r2 TO peon1;
/*u0*/GRANT r3 TO r12;  -- r12 held by peon3.  Nest r3 into it too.
/*u0*/GRANT SELECT ON t1 TO peon2;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon2 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*c1*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon3 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*c1*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;

-- Access to t3 has been granted only through r3, either directly or
-- indirectly (the latter through nesting t3 in another role).
CONNECT USER sa PASSWORD '';
DROP ROLE r3;
CONNECT USER peon1 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon2 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
CONNECT USER peon3 PASSWORD password;
/*c1*/SELECT * FROM t1;
/*c1*/SELECT * FROM t2;
/*e*/SELECT * FROM t3;
/*e*/SELECT * FROM t4;
