pg_rorole_v2_minimal_test.sql

DROP TABLE IF EXISTS rorole_minimal_test_table CASCADE;

DROP USER IF EXISTS rorole_minimal_test_user;
DROP USER IF EXISTS no_rorole_minimal_test_user;

CREATE TABLE rorole_minimal_test_table (id integer);
INSERT INTO rorole_minimal_test_table VALUES (1), (2);

CREATE USER rorole_minimal_test_user;
CREATE USER no_rorole_minimal_test_user;

GRANT pg_read_all_data TO rorole_minimal_test_user;
GRANT ALL ON SCHEMA public TO no_rorole_minimal_test_user;
REVOKE pg_read_all_data FROM no_rorole_minimal_test_user;

SET SESSION AUTHORIZATION rorole_minimal_test_user;
SELECT session_user, current_user;
SELECT * FROM rorole_minimal_test_table; -- ok
INSERT INTO rorole_minimal_test_table VALUES (3); -- fail

SET SESSION AUTHORIZATION no_rorole_minimal_test_user;
SELECT session_user, current_user;
SELECT * FROM rorole_minimal_test_table; -- fail
INSERT INTO rorole_minimal_test_table VALUES (3); -- fail