1.建立数据库a、b、b，用户a_system拥有三个数据库所有权限，用户a拥有数据库a的读写权限
--回收public权限
revoke connect on database a from public;
revoke connect on database b from public;
revoke connect on database b from public;

grant connect on database a to a;
\c a system 
GRANT USAGE ON SCHEMA public TO a;
GRANT USAGE ON SCHEMA a TO a;
--赋予模式所有权限给a用户
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO a;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA a TO a;
--赋予将来表的权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO a;
ALTER DEFAULT PRIVILEGES IN SCHEMA a GRANT ALL ON TABLES TO a;

2.只读用户的创建和删除
--创建只读账号
CREATE USER ehr_esb WITH PASSWORD 'ehr_esb@kdb@2025';
alter user readonly set default_transaction_read_only=on;
GRANT CONNECT ON DATABASE ehr TO ehr_esb;
GRANT USAGE ON SCHEMA vwork TO ehr_esb;
GRANT SELECT ON ALL TABLES IN SCHEMA vwork TO ehr_esb;
ALTER DEFAULT PRIVILEGES IN SCHEMA vwork GRANT SELECT ON TABLES TO ehr_esb;
--赋权整个模式的表
授予模式的使用权限
GRANT USAGE ON SCHEMA schema_name TO zh_kbs;
授予模式中所有表的SELECT权限
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO zh_kbs;
授予未来创建的表的权限
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO zh_kbs;

--删除用户
-- 撤销public模式上的权限
REVOKE ALL PRIVILEGES ON SCHEMA public FROM readonly;
--撤销查询权限
revoke SELECT ON ALL TABLES IN SCHEMA wjjk from readonly;
-- 撤销默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE system IN SCHEMA public REVOKE ALL ON TABLES FROM readonly;
-- 现在可以删除角色了
DROP USER readonly;
--强制删除
-- 在数据库中执行
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON DATABASE dat1 FROM readonly;

-- 再次尝试删除

DROP OWNED BY readonly CASCADE;

drop user readonly ;