pgsql

-- 创建用户 saidi01 并设置密码
CREATE ROLE saidi01 WITH LOGIN PASSWORD '123456';

-- 授予 saidi01 对 tb_data 数据库的 CONNECT 权限
GRANT CONNECT ON DATABASE tb_data TO saidi01;

-- 创建 my_view01 视图
CREATE VIEW my_view01 AS
SELECT * FROM tb_production WHERE device_type='雾炮';

-- 授予 saidi01 对 my_view01 视图的 SELECT 权限
GRANT SELECT ON my_view01 TO saidi01;

-- 禁止 saidi01 查看 tb_data 数据库中除 my_view01 外的其他表
REVOKE ALL ON DATABASE tb_data FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO saidi01;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO saidi01;

-- 禁止 saidi01 查看 tb_base 数据库
REVOKE CONNECT ON DATABASE tb_base FROM saidi01;

-- 禁止 saidi01 查看 tb_data 数据库中除 my_view01 外的其他表
REVOKE ALL ON DATABASE tb_data FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO saidi01;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM public;
GRANT SELECT ON my_view01 TO saidi01;

-- 禁止 saidi01 查看 tb_base 数据库
REVOKE CONNECT ON DATABASE tb_base FROM saidi01;