pg_user_mappings
A system catalogue view listing the available user mappings
pg_user_mappings is a system catalogue view listing the available user mappings. 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.
pg_user_mappings was added in PostgreSQL 8.4 (commit cae565e5).
Definition by PostgreSQL version
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