acl.sql

text/plain

Download

-- make -C /pgtop/contrib/isn install; make -C /pgtop/contrib/postgres_fdw install
DROP EXTENSION isn CASCADE;
DROP EXTENSION postgres_fdw CASCADE;
DROP SERVER pgtest;
DROP FOREIGN DATA WRAPPER pgtest2;
DROP USER ro;
CREATE USER ro SUPERUSER;
DROP USER test_ui;
CREATE USER test_ui SUPERUSER;
\c test ro
CREATE TABLE test (x INT);
CREATE EXTENSION isn;
CREATE EXTENSION postgres_fdw;
CREATE SERVER pgtest TYPE 'postgres_fdw' FOREIGN DATA WRAPPER postgres_fdw;
CREATE FOREIGN DATA WRAPPER pgtest2;
\c test postgres
GRANT SELECT ON TABLE test TO ro;
GRANT USAGE ON TYPE isbn TO ro;
GRANT USAGE ON FOREIGN SERVER pgtest TO ro;
GRANT USAGE ON FOREIGN DATA WRAPPER pgtest2 TO ro;
--
SELECT (SELECT rolname FROM pg_roles WHERE oid = relowner), relacl FROM pg_class WHERE oid = 'test'::regclass;
SELECT (SELECT rolname FROM pg_roles WHERE oid = typowner), typacl FROM pg_type WHERE oid = 'isbn'::regtype;
SELECT (SELECT rolname FROM pg_roles WHERE oid = srvowner), srvacl FROM pg_foreign_server WHERE srvname = 'pgtest';
SELECT (SELECT rolname FROM pg_roles WHERE oid = fdwowner), fdwacl FROM pg_foreign_data_wrapper WHERE fdwname = 'pgtest2';
REASSIGN OWNED BY ro TO test_ui;
SELECT (SELECT rolname FROM pg_roles WHERE oid = relowner), relacl FROM pg_class WHERE oid = 'test'::regclass;
SELECT (SELECT rolname FROM pg_roles WHERE oid = typowner), typacl FROM pg_type WHERE oid = 'isbn'::regtype;
SELECT (SELECT rolname FROM pg_roles WHERE oid = srvowner), srvacl FROM pg_foreign_server WHERE srvname = 'pgtest';
SELECT (SELECT rolname FROM pg_roles WHERE oid = fdwowner), fdwacl FROM pg_foreign_data_wrapper WHERE fdwname = 'pgtest2';
ALTER TABLE test OWNER TO ro;
ALTER TYPE isbn OWNER TO ro;
ALTER SERVER pgtest OWNER TO ro;
ALTER FOREIGN DATA WRAPPER pgtest2 OWNER TO ro;
SELECT (SELECT rolname FROM pg_roles WHERE oid = relowner), relacl FROM pg_class WHERE oid = 'test'::regclass;
SELECT (SELECT rolname FROM pg_roles WHERE oid = typowner), typacl FROM pg_type WHERE oid = 'isbn'::regtype;
SELECT (SELECT rolname FROM pg_roles WHERE oid = srvowner), srvacl FROM pg_foreign_server WHERE srvname = 'pgtest';
SELECT (SELECT rolname FROM pg_roles WHERE oid = fdwowner), fdwacl FROM pg_foreign_data_wrapper WHERE fdwname = 'pgtest2';