pg_user 是一个提供 pg_shadow 公共访问版本的系统目录视图，除了 passwd 字段外，其他字段与 pg_shadow 完全相同，passwd 字段显示为一系列星号（无论用户是否设置了密码）。
pg_user 添加于 PostgreSQL 6.3，取代了之前的 系统目录表 pg_user，该表又被 pg_shadow 取代。
按 PostgreSQL 版本定义
pg_user (PostgreSQL 17)
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档: pg_user
pg_user (PostgreSQL 16)
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档: pg_user
pg_user (PostgreSQL 15)
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档: pg_user
pg_user (PostgreSQL 14)
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档: pg_user
pg_user (PostgreSQL 13)
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档: pg_user
pg_user (PostgreSQL 12)
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | timestamp with time zone | | |
useconfig | text[] | C | |
文档: pg_user
pg_user (PostgreSQL 11)
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | abstime | | |
useconfig | text[] | | |
文档: pg_user
pg_user (PostgreSQL 10)
View "pg_catalog.pg_user"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
usename | name | | |
usesysid | oid | | |
usecreatedb | boolean | | |
usesuper | boolean | | |
userepl | boolean | | |
usebypassrls | boolean | | |
passwd | text | | |
valuntil | abstime | | |
useconfig | text[] | | |
文档: pg_user
pg_user (PostgreSQL 9.6)
View "pg_catalog.pg_user"
Column | Type | Modifiers
--------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
userepl | boolean |
usebypassrls | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档: pg_user
pg_user (PostgreSQL 9.5)
View "pg_catalog.pg_user"
Column | Type | Modifiers
--------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
userepl | boolean |
usebypassrls | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档: pg_user
pg_user (PostgreSQL 9.4)
View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
userepl | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档: pg_user
pg_user (PostgreSQL 9.3)
View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
userepl | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档: pg_user
pg_user (PostgreSQL 9.2)
View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
userepl | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档: pg_user
pg_user (PostgreSQL 9.1)
View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
userepl | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档: pg_user
pg_user (PostgreSQL 9.0)
View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
文档: pg_user
pg_user (PostgreSQL 8.4)
View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
View definition:
SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig
FROM pg_shadow;
文档: pg_user
pg_user (PostgreSQL 8.3)
View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
View definition:
SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig
FROM pg_shadow;
文档: pg_user
pg_user (PostgreSQL 8.2)
View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
View definition:
SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil, pg_shadow.useconfig
FROM pg_shadow;
文档: pg_user
变更历史
- PostgreSQL 9.5
- PostgreSQL 9.1
- 添加
userepl列 (提交 9b8aff8c)
- 添加
- PostgreSQL 7.3
- PostgreSQL 6.3
示例
pg_user 的示例行
postgres=# SELECT * FROM pg_user WHERE usename='postgres'\x\g\x Expanded display is on. -[ RECORD 1 ]+--------------------------- usename | postgres usesysid | 10 usecreatedb | t usesuper | t userepl | t usebypassrls | t passwd | ******** valuntil | useconfig | {password_encryption=true}
参考文献
- PostgreSQL 文档： pg_user