CREATE USER MAPPING
CREATE USER MAPPING — define a new mapping of a user to a foreign server
CREATE USER MAPPING [ IF NOT EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_name[ OPTIONS (option'value' [ , ... ] ) ]
CREATE USER MAPPING defines a mapping of a user to a foreign server. A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource.
The owner of a foreign server can create user mappings for that server for any user. Also, a user can create a user mapping for their own user name if USAGE privilege on the server has been granted to the user.
Parameters
IF NOT EXISTS
Do not throw an error if a mapping of the given user to the given foreign server already exists. A notice is issued in this case. Note that there is no guarantee that the existing user mapping is anything like the one that would have been created.
user_name
The name of an existing user that is mapped to foreign server. CURRENT_USER and USER match the name of the current user. When PUBLIC is specified, a so-called public mapping is created that is used when no user-specific mapping is applicable.
server_name
The name of an existing server for which the user mapping is to be created.
OPTIONS ( option 'value' [, ... ] )
This clause specifies the options of the user mapping. The options typically define the actual user name and password of the mapping. Option names must be unique. The allowed option names and values are specific to the server’s foreign-data wrapper.
Examples
Create a user mapping for user bob, server foo:
CREATE USER MAPPING FOR bob SERVER foo OPTIONS (user 'bob', password 'secret');
ALTER USER MAPPING
ALTER USER MAPPING — change the definition of a user mapping
ALTER USER MAPPING FOR { user_name | USER | CURRENT_USER | SESSION_USER | PUBLIC } SERVER server_nameOPTIONS ( [ ADD | SET | DROP ]option['value'] [, ... ] )
ALTER USER MAPPING changes the definition of a user mapping.
The owner of a foreign server can alter user mappings for that server for any user. Also, a user can alter a user mapping for their own user name if USAGE privilege on the server has been granted to the user.
Parameters
user_name
User name of the mapping. CURRENT_USER and USER match the name of the current user. PUBLIC is used to match all present and future user names in the system.
server_name
Server name of the user mapping.
OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )
Change options for the user mapping. The new options override any previously specified options. ADD, SET, and DROP specify the action to be performed. ADD is assumed if no operation is explicitly specified. Option names must be unique; options are also validated by the server’s foreign-data wrapper.
Examples
Change the password for user mapping bob, server foo:
ALTER USER MAPPING FOR bob SERVER foo OPTIONS (SET password 'public');
DROP USER MAPPING
DROP USER MAPPING — remove a user mapping for a foreign server
DROP USER MAPPING [ IF EXISTS ] FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_nameDROP USER MAPPING removes an existing user mapping from foreign server.
The owner of a foreign server can drop user mappings for that server for any user. Also, a user can drop a user mapping for their own user name if USAGE privilege on the server has been granted to the user.
Parameters
IF EXISTS
Do not throw an error if the user mapping does not exist. A notice is issued in this case.
user_name
User name of the mapping. CURRENT_USER and USER match the name of the current user. PUBLIC is used to match all present and future user names in the system.
server_name
Server name of the user mapping.
Examples
Drop a user mapping bob, server foo if it exists:
DROP USER MAPPING IF EXISTS FOR bob SERVER foo;