PostgreSQL：normal_user无法查看information_schema.views的view_definition

执行以下SQL创建用户、模式并分配权限：

CREATE USER admin_user  WITH PASSWORD 'ExtremStr0ngPassw0rd';
CREATE USER normal_user WITH PASSWORD 'Str0ngPassw0rd';

CREATE SCHEMA my_schema;
DROP SCHEMA PUBLIC;

ALTER SCHEMA my_schema OWNER TO admin_user;

GRANT ALL ON DATABASE "my-db" TO admin_user;
GRANT ALL PRIVILEGES ON DATABASE "my-db" TO admin_user;
GRANT ALL PRIVILEGES ON SCHEMA my_schema TO admin_user;

GRANT CONNECT, TEMP, TEMPORARY ON DATABASE "my-db" TO normal_user;
GRANT USAGE ON SCHEMA my_schema TO normal_user;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA my_schema TO normal_user;
ALTER DEFAULT PRIVILEGES FOR USER admin_user IN SCHEMA my_schema GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO normal_user;
ALTER DEFAULT PRIVILEGES FOR USER admin_user IN SCHEMA my_schema GRANT SELECT, USAGE ON SEQUENCES TO normal_user;
ALTER DEFAULT PRIVILEGES FOR USER admin_user IN SCHEMA my_schema GRANT EXECUTE ON FUNCTIONS TO normal_user;
ALTER DEFAULT PRIVILEGES FOR USER admin_user IN SCHEMA information_schema GRANT SELECT ON TABLES TO normal_user;

随后admin_user创建表和视图：

CREATE TABLE my_schema.test_1
(
    id BIGINT NOT NULL,
    text_value TEXT,
    CONSTRAINT test_1_pk PRIMARY KEY (id)
);

CREATE VIEW my_schema.view_1 as SELECT * FROM my_schema.test_1;

以normal_user登录后，可正常查询视图my_schema.view_1，但执行以下语句时，view_definition字段始终为null：

SELECT * FROM information_schema.views WHERE table_schema = 'my_schema'

请问缺少哪项权限配置，或该现象的原因是什么？

这是因为normal_user没有获取视图定义的权限。在PostgreSQL中，只有视图所有者、拥有视图定义涉及所有对象权限且被授予视图SELECT权限的用户，或拥有超级权限的用户，才能看到view_definition的具体内容。

当前normal_user虽能查询视图，但未被授予查看视图定义的权限，需要添加以下配置：

-- 授予normal_user查看my_schema下已存在视图定义的权限
GRANT SELECT ON ALL VIEWS IN SCHEMA my_schema TO normal_user;
-- 设置默认权限，确保admin_user后续创建的视图也能让normal_user查看定义
ALTER DEFAULT PRIVILEGES FOR USER admin_user IN SCHEMA my_schema GRANT SELECT ON VIEWS TO normal_user;

当前场景下normal_user已拥有视图依赖的test_1表的SELECT权限，添加上述视图权限后，即可正常查看view_definition内容。

内容的提问来源于stack exchange，提问作者Alex