pg_user_mappings
A system catalogue view listing the available user mapping|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 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