pg_user_mapping
A system table containing mapping between local and remote FDW users
pg_user_mapping is a system catalogue table containing mapping between local and remote foreign data wrapper (FDW) users, as defined by the CREATE USER MAPPING command. Access to pg_user_mapping is restricted to superusers.
pg_user_mapping was added in PostgreSQL 8.4.
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
\deulists available user mappings\deu+lists available user mappings together with the parameters provided with theOPTIONSclause
Definition by PostgreSQL version
pg_user_mapping (PostgreSQL 16)
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" PRIMARY KEY, btree (oid)
"pg_user_mapping_user_server_index" UNIQUE CONSTRAINT, btree (umuser, umserver)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 15)
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" PRIMARY KEY, btree (oid)
"pg_user_mapping_user_server_index" UNIQUE CONSTRAINT, btree (umuser, umserver)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 14)
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" PRIMARY KEY, btree (oid)
"pg_user_mapping_user_server_index" UNIQUE CONSTRAINT, btree (umuser, umserver)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 13)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 12)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 11)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 10)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 9.6)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 9.5)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 9.4)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 9.3)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 9.2)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 9.1)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 9.0)
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)
Documentation: pg_user_mapping
pg_user_mapping (PostgreSQL 8.4)
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)
Documentation: pg_user_mapping
Change history
This table has not changed since it was added in PostgreSQL 8.4.
- PostgreSQL 8.4
- added (commit cae565e5)
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
- PostgreSQL documentation: pg_user_mapping
