PostgreSQL USER MAPPING

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_name

    OPTIONS ( [ 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_name

DROP 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;