pg_shadow 是一个 系统目录 视图，显示 pg_authid 中标记为 rolcanlogin 的所有角色的属性。
pg_shadow 是为了在 PostgreSQL 8.1 中实现向后兼容而创建的，当时旧的 pg_shadow 表被 pg_authid 替换。
用法
pg_shadow 显示用户的密码（pg_authid 的 rolpassword 字段的内容），因此只能由超级用户和被授予对其 SELECT 权限的用户访问。
视图 pg_user 默认可供所有用户访问，并将用户密码显示为固定长度的星号字符串。
按 PostgreSQL 版本定义
pg_shadow (PostgreSQL 19)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | C | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档：pg_shadow
pg_shadow (PostgreSQL 18)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | C | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档：pg_shadow
pg_shadow (PostgreSQL 17)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | C | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档：pg_shadow
pg_shadow (PostgreSQL 16)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | C | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档：pg_shadow
pg_shadow (PostgreSQL 15)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | C | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档：pg_shadow
pg_shadow (PostgreSQL 14)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | C | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档：pg_shadow
pg_shadow (PostgreSQL 13)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | C | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档：pg_shadow
pg_shadow (PostgreSQL 12)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | C | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档：pg_shadow
pg_shadow (PostgreSQL 11)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | abstime | | |
useconfig | text[] | | |
文档：pg_shadow
pg_shadow (PostgreSQL 10)
View "pg_catalog.pg_shadow"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | abstime | | |
useconfig | text[] | | |
文档：pg_shadow
pg_shadow (PostgreSQL 9.6)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
--------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
userepl | boolean |
usebypassrls | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档：pg_shadow
pg_shadow (PostgreSQL 9.5)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
--------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
userepl | boolean |
usebypassrls | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档：pg_shadow
pg_shadow (PostgreSQL 9.4)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
userepl | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档：pg_shadow
pg_shadow (PostgreSQL 9.3)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
userepl | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档：pg_shadow
pg_shadow (PostgreSQL 9.2)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
userepl | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档：pg_shadow
pg_shadow (PostgreSQL 9.1)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
userepl | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档：pg_shadow
pg_shadow (PostgreSQL 9.0)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档：pg_shadow
pg_shadow (PostgreSQL 8.4)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
View definition:
SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, pg_authid.rolvaliduntil::abstime AS valuntil, pg_authid.rolconfig AS useconfig
FROM pg_authid
WHERE pg_authid.rolcanlogin;
文档：pg_shadow
pg_shadow (PostgreSQL 8.3)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
View definition:
SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, pg_authid.rolvaliduntil::abstime AS valuntil, pg_authid.rolconfig AS useconfig
FROM pg_authid
WHERE pg_authid.rolcanlogin;
文档：pg_shadow
pg_shadow (PostgreSQL 8.2)
View "pg_catalog.pg_shadow"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
View definition:
SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, pg_authid.rolvaliduntil::abstime AS valuntil, pg_authid.rolconfig AS useconfig
FROM pg_authid
WHERE pg_authid.rolcanlogin;
文档：pg_shadow
变更历史
- PostgreSQL 9.5
- PostgreSQL 9.1
- 添加了列
userepl(提交 9b8aff8c)
- 添加了列
- PostgreSQL 8.1
- 添加（提交 7762619e）
示例
新初始化集群上的 pg_shadow 内容示例
postgres=# SELECT * FROM pg_shadow\gx -[ RECORD 1 ]+--------- usename | postgres usesysid | 10 usecreatedb | t usesuper | t userepl | t usebypassrls | t passwd | valuntil | useconfig |
参考资料
- PostgreSQL 文档: pg_shadow