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.

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