pg_user_mappings
pg_user_mappings
is a system catalogue view listing the available user mappings.
pg_user_mappings
was added in PostgreSQL 8.4.
Usage
In contrast to the underlying superuser-only pg_user_mapping
system catalogue table, it is available to normal users but hides information the user has no rights to view, specifically the contents of the options
field.
Definition by PostgreSQL version
pg_user_mappings (PostgreSQL 16)
View "pg_catalog.pg_user_mappings" Column | Type | Collation | Nullable | Default -----------+--------+-----------+----------+--------- umid | oid | | | srvid | oid | | | srvname | name | | | umuser | oid | | | usename | name | | | umoptions | text[] | C | |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 15)
View "pg_catalog.pg_user_mappings" Column | Type | Collation | Nullable | Default -----------+--------+-----------+----------+--------- umid | oid | | | srvid | oid | | | srvname | name | | | umuser | oid | | | usename | name | | | umoptions | text[] | C | |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 14)
View "pg_catalog.pg_user_mappings" Column | Type | Collation | Nullable | Default -----------+--------+-----------+----------+--------- umid | oid | | | srvid | oid | | | srvname | name | | | umuser | oid | | | usename | name | | | umoptions | text[] | C | |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 13)
View "pg_catalog.pg_user_mappings" Column | Type | Collation | Nullable | Default -----------+--------+-----------+----------+--------- umid | oid | | | srvid | oid | | | srvname | name | | | umuser | oid | | | usename | name | | | umoptions | text[] | C | |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 12)
View "pg_catalog.pg_user_mappings" Column | Type | Collation | Nullable | Default -----------+--------+-----------+----------+--------- umid | oid | | | srvid | oid | | | srvname | name | | | umuser | oid | | | usename | name | | | umoptions | text[] | C | |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 11)
View "pg_catalog.pg_user_mappings" Column | Type | Collation | Nullable | Default -----------+--------+-----------+----------+--------- umid | oid | | | srvid | oid | | | srvname | name | | | umuser | oid | | | usename | name | | | umoptions | text[] | | |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 10)
View "pg_catalog.pg_user_mappings" Column | Type | Collation | Nullable | Default -----------+--------+-----------+----------+--------- umid | oid | | | srvid | oid | | | srvname | name | | | umuser | oid | | | usename | name | | | umoptions | text[] | | |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 9.6)
View "pg_catalog.pg_user_mappings" Column | Type | Modifiers -----------+--------+----------- umid | oid | srvid | oid | srvname | name | umuser | oid | usename | name | umoptions | text[] |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 9.5)
View "pg_catalog.pg_user_mappings" Column | Type | Modifiers -----------+--------+----------- umid | oid | srvid | oid | srvname | name | umuser | oid | usename | name | umoptions | text[] |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 9.4)
View "pg_catalog.pg_user_mappings" Column | Type | Modifiers -----------+--------+----------- umid | oid | srvid | oid | srvname | name | umuser | oid | usename | name | umoptions | text[] |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 9.3)
View "pg_catalog.pg_user_mappings" Column | Type | Modifiers -----------+--------+----------- umid | oid | srvid | oid | srvname | name | umuser | oid | usename | name | umoptions | text[] |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 9.2)
View "pg_catalog.pg_user_mappings" Column | Type | Modifiers -----------+--------+----------- umid | oid | srvid | oid | srvname | name | umuser | oid | usename | name | umoptions | text[] |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 9.1)
View "pg_catalog.pg_user_mappings" Column | Type | Modifiers -----------+--------+----------- umid | oid | srvid | oid | srvname | name | umuser | oid | usename | name | umoptions | text[] |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 9.0)
View "pg_catalog.pg_user_mappings" Column | Type | Modifiers -----------+--------+----------- umid | oid | srvid | oid | srvname | name | umuser | oid | usename | name | umoptions | text[] |
Documentation: pg_user_mappings
pg_user_mappings (PostgreSQL 8.4)
View "pg_catalog.pg_user_mappings" Column | Type | Modifiers -----------+--------+----------- umid | oid | srvid | oid | srvname | name | umuser | oid | usename | name | umoptions | text[] | View definition: SELECT u.oid AS umid, s.oid AS srvid, s.srvname, u.umuser, CASE WHEN u.umuser = 0::oid THEN 'public'::name ELSE a.rolname END AS usename, CASE WHEN pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text) THEN u.umoptions ELSE NULL::text[] END AS umoptions FROM pg_user_mapping u LEFT JOIN pg_authid a ON a.oid = u.umuser JOIN pg_foreign_server s ON u.umserver = s.oid;
Documentation: pg_user_mappings
Change history
This view has not been modified since it was first added.
- PostgreSQL 8.4
- added (commit cae565e5)
Examples
postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER fdw_test OPTIONS (user 'foo', password 'bar'); CREATE USER MAPPING postgres=# CREATE USER MAPPING FOR foo SERVER fdw_test OPTIONS (user 'foo', password 'bar'); CREATE USER MAPPING postgres=# SELECT * FROM pg_user_mapping; umid | srvid | srvname | umuser | usename | umoptions -------+-------+----------+--------+----------+------------------------- 75560 | 75559 | fdw_test | 10 | postgres | {user=foo,password=bar} 75561 | 75559 | fdw_test | 16384 | foo | {user=foo,password=bar} (2 rows) postgres=# \c - foo You are now connected to database "postgres" as user "foo". postgres=> SELECT * FROM pg_user_mapping; ERROR: permission denied for table pg_user_mapping postgres=> SELECT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+----------+--------+----------+----------- 75560 | 75559 | fdw_test | 10 | postgres | 75561 | 75559 | fdw_test | 16384 | foo | (2 rows)
References
- PostgreSQL documentation: pg_user_mappings