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.

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)

Categories

Foreign Data Wrapper (FDW), System catalogue, User management

See also

pg_user_mapping, CREATE USER MAPPING