create user cspassword ‘123123';

–all TABLES
ALTER DEFAULT PRIVILEGES for user csREVOKE ALL ON TABLES FROM PUBLIC;

CREATE DATABASE wan ENCODING ‘UTF8' TEMPLATE template0;

select datname from sys_database;
select ‘REVOKE ALL on DATABASE ' || datname || ' FROM PUBLIC;' from sys_database where datname not in(‘test',‘template0',‘template1');
select ‘REVOKE ALL on DATABASE ' || datname || ' FROM cs;' from sys_database where datname not in(‘test',‘template0',‘template1');

GRANT ALL ON DATABASE wan TO cs;

GRANT SELECT,UPDATE,INSERT,DELETE,TRUNCATE,REFERENCES,TRIGGER ON ALL TABLES in SCHEMA public to cs;

ALTER DEFAULT PRIVILEGES IN SCHEMA sch_kcm grant SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER
on tables to cs;

CREATE USER readonly WITH ENCRYPTED PASSWORD ‘readonly';

alter user readonly set default_transaction_read_only=on;

GRANT CONNECT ON DATABASE mytest to readonly;

\c mytest system

GRANT USAGE ON SCHEMA public to readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

revoke USAGE ON SCHEMA public from readonly;

revoke SELECT ON ALL TABLES IN SCHEMA public from readonly;

revoke SELECT ON ALL SEQUENCES IN SCHEMA public from readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public revoke SELECT ON TABLES from readonly;

revoke CONNECT ON DATABASE foo from readonly;

alter user readonly set default_transaction_read_only=off;

\ddp

drop user readonly;