pg_user_mapping
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
\deu
lists available user mappings\deu+
lists available user mappings together with the parameters provided with theOPTIONS
clause
Definition by PostgreSQL version
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