CREATE USER MAPPING
Description
CREATE USER MAPPING defines a new mapping from a user to an external server. A user mapping usually contains connection information. The foreign data wrapper uses the connection information and the information contained in the external server to access an external data source, the owner of an external server can create a user mapping for the server for any user. If a user is granted the USAGE privilege on the server, the user can create a user mapping for 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 in GaussDB. For details, see the description in OPTIONS ( { option_name 'value' } [, ...] ).
Syntax
CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC } SERVER server_name [ OPTIONS ( option 'value' [ , ... ] ) ];
Parameters
-
Specifies the name of an existing user to map to a foreign server.
CURRENT_USER and USER match the name of the current user. When PUBLIC is specified, a public mapping is created and used when no mapping for a particular user is available.
-
Specifies the name of the existing server for a created user mapping.
-
OPTIONS ( { option_name ' value ' } [, ...] )
Specifies options for user mapping. These options typically define the actual username and password of this mapping. The option name must be unique. The allowed option names and values are related to 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".
- 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.
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;
Helpful Links
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot