Contents

pg_auth_members

A system table containing information about database groups

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

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)

Categories

Cluster-wide table, System catalogue, User management

See also

pg_authid