ALTER USER MAPPING

Description

ALTER USER MAPPING changes the definition of the mapping from a user to a foreign server. The owner of the foreign server can change the user mapping of the server for any user. In addition, if the USAGE permission on the server has been granted to a user, the user can change the user mapping of its own username.

Precautions

If the password option is displayed, ensure that the usermapping.key.cipher and usermapping.key.rand files exist in the $GAUSSHOME/bin directory of each node in GaussDB. If the two files do not exist, use the gs\_guc tool to generate them and use the gs\_ssh tool to release them to the $GAUSSHOME/bin directory on each node. For details, see the description in OPTIONS.
When multi-layer quotation marks are used for sensitive columns (such as password) in OPTIONS, the semantics is different from that in the scenario where quotation marks are not used. Therefore, sensitive columns are not identified for anonymization.

Syntax

ALTER USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] );

In OPTIONS, ADD, SET, and DROP are operations to be performed. If these operations are not specified, ADD operations will be performed by default. option and value are the parameters and values of the corresponding operation.

Parameters

user_name

Specifies username of the mapping.

CURRENT_USER and USER match the name of the current user. PUBLIC is used to match all current and future usernames in the system.
server_name

Specifies name of the server to which the user is mapped.
OPTIONS

Changes an option for the user mapping. The new option overwrites any previously specified option. ADD, SET, and DROP are operations to be performed. If the operation is not set explicitly, ADD is used. The option name must be unique and will be validated with the foreign data wrapper of the server.
- User passwords are encrypted and stored in the system catalog PG_USER_MAPPING. During the encryption, usermapping.key.cipher and usermapping.key.rand are used as the encryption password file and encryption factor. Before using the tool for the first time, create the two files, save the files to the $GAUSSHOME/bin directory on each node, and ensure that you have the read permission on the files. gs_ssh helps you quickly place files in the specified directory of each node.
```
gs_ssh -c "gs_guc generate -o usermapping -S default -D $GAUSSHOME/bin"
```
- If the -S parameter is set to default, a password is randomly generated. You can also specify a password for the -S parameter to ensure the security and uniqueness of the generated password file. You do not need to save or memorize the password. For details about other parameters, see the description of the gs_guc tool in the Tool Reference.

Examples

-- Create a role.
gaussdb=# CREATE ROLE bob PASSWORD '********';

-- Create a foreign server.
gaussdb=# CREATE SERVER my_server FOREIGN DATA WRAPPER log_fdw;

-- Create a user mapping.
gaussdb=# CREATE USER MAPPING FOR bob SERVER my_server OPTIONS (user 'bob', password '********');

-- Modify the user mapping.
gaussdb=# ALTER USER MAPPING FOR bob SERVER my_server OPTIONS (SET password '********');

-- Delete the user mapping.
gaussdb=# DROP USER MAPPING FOR bob SERVER my_server;

-- Delete the foreign server.
gaussdb=# DROP SERVER my_server;

-- Delete the role.
gaussdb=# DROP ROLE bob;