• Browse categories
• PostgreSQL releases
  • PostgreSQL 14 (dev)
  • PostgreSQL 13 (latest)
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10
  • PostgreSQL 9.6
  • PostgreSQL 9.5
  • PostgreSQL 9.4 (EOL)

Useful articles

Useful links

pg_user_mapping

pg_user_mapping is a system catalogue table containing mapping between local and remote FDW users, as defined by the CREATE USER MAPPING command. Access to pg_user_mapping is restricted to superusers.

pg_user_mapping was introduced in PostgreSQL 8.4 (commit cae565e5).

The view pg_user_mappings displays the contents of pg_user_mapping in a more readable format, in particular providing the foreign server name. It is also accessible to  non-superusers.

psql commands

• \deu lists available user mappings
• \deu+ lists available user mappings together with the parameters provided with the OPTIONS clause

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null |
 umuser    | oid    |           | not null |
 umserver  | oid    |           | not null |
 umoptions | text[] | C         |          |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default
-----------+--------+-----------+----------+---------
 oid       | oid    |           | not null |
 umuser    | oid    |           | not null |
 umserver  | oid    |           | not null |
 umoptions | text[] | C         |          |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default
-----------+--------+-----------+----------+---------
 umuser    | oid    |           | not null |
 umserver  | oid    |           | not null |
 umoptions | text[] |           |          |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

         Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Collation | Nullable | Default
-----------+--------+-----------+----------+---------
 umuser    | oid    |           | not null |
 umserver  | oid    |           | not null |
 umoptions | text[] |           |          |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

Table "pg_catalog.pg_user_mapping"
  Column   |  Type  | Modifiers
-----------+--------+-----------
 umuser    | oid    | not null
 umserver  | oid    | not null
 umoptions | text[] |
Indexes:
    "pg_user_mapping_oid_index" UNIQUE, btree (oid)
    "pg_user_mapping_user_server_index" UNIQUE, btree (umuser, umserver)
    

Change history

This table has not changed since it was added in PostgreSQL 8.4.

Examples

postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER fdw_test
postgres-#   OPTIONS(user 'postgres');
CREATE USER MAPPING

postgres=# SELECT * FROM pg_user_mapping;
 umuser | umserver |    umoptions    
--------+----------+-----------------
     10 |    67200 | {user=postgres}
(1 row)

postgres=# SELECT * FROM pg_user_mappings;
 umid  | srvid | srvname  | umuser | usename  |    umoptions    
-------+-------+----------+--------+----------+-----------------
 67201 | 67200 | fdw_test |     10 | postgres | {user=postgres}
(1 row)

References

Categories

See also