pg_auth_members
pg_auth_members
is a system catalogue table containing information about database groups.
It replaces the previous system catalogue table pg_group, which is now a view.
This table is shared across all databases in a cluster.
psql commands
\du
provides a list of database users and their role memberships\du+
provides the same information as\du
and also the user's description
System functions
The system function pg_has_role()
can be used to determine whether a particular user is member of a particular role.
Definition by PostgreSQL version
pg_auth_members (PostgreSQL 15)
Table "pg_catalog.pg_auth_members" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+--------- roleid | oid | | not null | member | oid | | not null | grantor | oid | | not null | admin_option | boolean | | not null | Indexes: "pg_auth_members_role_member_index" PRIMARY KEY, btree (roleid, member), tablespace "pg_global" "pg_auth_members_member_role_index" UNIQUE CONSTRAINT, btree (member, roleid), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 14)
Table "pg_catalog.pg_auth_members" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+--------- roleid | oid | | not null | member | oid | | not null | grantor | oid | | not null | admin_option | boolean | | not null | Indexes: "pg_auth_members_role_member_index" PRIMARY KEY, btree (roleid, member), tablespace "pg_global" "pg_auth_members_member_role_index" UNIQUE CONSTRAINT, btree (member, roleid), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 13)
Table "pg_catalog.pg_auth_members" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+--------- roleid | oid | | not null | member | oid | | not null | grantor | oid | | not null | admin_option | boolean | | not null | Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 12)
Table "pg_catalog.pg_auth_members" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+--------- roleid | oid | | not null | member | oid | | not null | grantor | oid | | not null | admin_option | boolean | | not null | Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 11)
Table "pg_catalog.pg_auth_members" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+--------- roleid | oid | | not null | member | oid | | not null | grantor | oid | | not null | admin_option | boolean | | not null | Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 10)
Table "pg_catalog.pg_auth_members" Column | Type | Collation | Nullable | Default --------------+---------+-----------+----------+--------- roleid | oid | | not null | member | oid | | not null | grantor | oid | | not null | admin_option | boolean | | not null | Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 9.6)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 9.5)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 9.4)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 9.3)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 9.2)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 9.1)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 9.0)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 8.4)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Triggers: pg_sync_pg_auth_members AFTER INSERT OR DELETE OR UPDATE ON pg_auth_members FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger() Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 8.3)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Triggers: pg_sync_pg_auth_members AFTER INSERT OR DELETE OR UPDATE ON pg_auth_members FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger() Tablespace: "pg_global"
Documentation: pg_auth_members
pg_auth_members (PostgreSQL 8.2)
Table "pg_catalog.pg_auth_members" Column | Type | Modifiers --------------+---------+----------- roleid | oid | not null member | oid | not null grantor | oid | not null admin_option | boolean | not null Indexes: "pg_auth_members_member_role_index" UNIQUE, btree (member, roleid), tablespace "pg_global" "pg_auth_members_role_member_index" UNIQUE, btree (roleid, member), tablespace "pg_global" Triggers: pg_sync_pg_auth_members AFTER INSERT OR DELETE OR UPDATE ON pg_auth_members FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger() Tablespace: "pg_global"
Documentation: pg_auth_members
Change history
- PostgreSQL 8.1
- added (commit 7762619e)
This table has remained unchanged since it was added in PostgreSQL 8.1.
Examples
postgres=# SELECT * FROM pg_auth_members; roleid | member | grantor | admin_option --------+--------+---------+-------------- 3374 | 3373 | 10 | f 3375 | 3373 | 10 | f 3377 | 3373 | 10 | f (3 rows)
References
- PostgreSQL documentation: pg_auth_members