pg_roles 是一个列出可用角色的系统目录视图
pg_roles 在 PostgreSQL 8.1 中添加。
用法
pg_roles 提供了底层 pg_authid 表的公开可读视图，但密码列包含星号。请注意，无论角色是否设置了密码，都会显示固定数量的星号。
按 PostgreSQL 版本定义
pg_roles (PostgreSQL 17)
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | C | |
oid | oid | | |
文档: pg_roles
pg_roles (PostgreSQL 16)
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | C | |
oid | oid | | |
文档: pg_roles
pg_roles (PostgreSQL 15)
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | C | |
oid | oid | | |
文档: pg_roles
pg_roles (PostgreSQL 14)
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | C | |
oid | oid | | |
文档: pg_roles
pg_roles (PostgreSQL 13)
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | C | |
oid | oid | | |
文档: pg_roles
pg_roles (PostgreSQL 12)
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | C | |
oid | oid | | |
文档: pg_roles
pg_roles (PostgreSQL 11)
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | | |
oid | oid | | |
文档: pg_roles
pg_roles (PostgreSQL 10)
View "pg_catalog.pg_roles"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
rolname | name | | |
rolsuper | boolean | | |
rolinherit | boolean | | |
rolcreaterole | boolean | | |
rolcreatedb | boolean | | |
rolcanlogin | boolean | | |
rolreplication | boolean | | |
rolconnlimit | integer | | |
rolpassword | text | | |
rolvaliduntil | timestamp with time zone | | |
rolbypassrls | boolean | | |
rolconfig | text[] | | |
oid | oid | | |
文档: pg_roles
pg_roles (PostgreSQL 9.6)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
----------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcanlogin | boolean |
rolreplication | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolbypassrls | boolean |
rolconfig | text[] |
oid | oid |
文档: pg_roles
pg_roles (PostgreSQL 9.5)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
----------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcanlogin | boolean |
rolreplication | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolbypassrls | boolean |
rolconfig | text[] |
oid | oid |
文档: pg_roles
pg_roles (PostgreSQL 9.4)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
----------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcatupdate | boolean |
rolcanlogin | boolean |
rolreplication | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolconfig | text[] |
oid | oid |
文档: pg_roles
pg_roles (PostgreSQL 9.3)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
----------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcatupdate | boolean |
rolcanlogin | boolean |
rolreplication | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolconfig | text[] |
oid | oid |
文档: pg_roles
pg_roles (PostgreSQL 9.2)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
----------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcatupdate | boolean |
rolcanlogin | boolean |
rolreplication | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolconfig | text[] |
oid | oid |
文档: pg_roles
pg_roles (PostgreSQL 9.1)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
----------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcatupdate | boolean |
rolcanlogin | boolean |
rolreplication | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolconfig | text[] |
oid | oid |
文档: pg_roles
pg_roles (PostgreSQL 9.0)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
---------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcatupdate | boolean |
rolcanlogin | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolconfig | text[] |
oid | oid |
文档: pg_roles
pg_roles (PostgreSQL 8.4)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
---------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcatupdate | boolean |
rolcanlogin | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolconfig | text[] |
oid | oid |
View definition:
SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid
FROM pg_authid;
文档: pg_roles
pg_roles (PostgreSQL 8.3)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
---------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcatupdate | boolean |
rolcanlogin | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolconfig | text[] |
oid | oid |
View definition:
SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid
FROM pg_authid;
文档: pg_roles
pg_roles (PostgreSQL 8.2)
View "pg_catalog.pg_roles"
Column | Type | Modifiers
---------------+--------------------------+-----------
rolname | name |
rolsuper | boolean |
rolinherit | boolean |
rolcreaterole | boolean |
rolcreatedb | boolean |
rolcatupdate | boolean |
rolcanlogin | boolean |
rolconnlimit | integer |
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolconfig | text[] |
oid | oid |
View definition:
SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit, pg_authid.rolcreaterole, pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit, '********'::text AS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid
FROM pg_authid;
文档: pg_roles
更改历史
- PostgreSQL 9.5
- PostgreSQL 9.1
- 添加了
rolreplication列 (提交 9b8aff8c)
- 添加了
- PostgreSQL 8.1
- 添加 (提交 7762619e)
示例
来自 pg_roles 的示例行
postgres=# SELECT * FROM pg_roles WHERE rolname = 'postgres'\gx -[ RECORD 1 ]--+--------- rolname | postgres rolsuper | t rolinherit | t rolcreaterole | t rolcreatedb | t rolcanlogin | t rolreplication | t rolconnlimit | -1 rolpassword | ******** rolvaliduntil | rolbypassrls | t rolconfig | oid | 10
显示的密码始终设置为由八个星号组成的固定字符串
postgres=# ALTER ROLE postgres PASSWORD 'foo'; ALTER ROLE postgres=# SELECT rolpassword FROM pg_roles WHERE rolname = 'postgres'\gx -[ RECORD 1 ]--------- rolpassword | ******** postgres=# ALTER ROLE postgres PASSWORD NULL; ALTER ROLE postgres=# SELECT rolpassword FROM pg_roles WHERE rolname = 'postgres'\gx -[ RECORD 1 ]--------- rolpassword | ********
参考文献
- PostgreSQL 文档： pg_roles