CREATE USER MAPPING

Function

CREATE USER MAPPING defines a new mapping from a user to a foreign server.

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 MogDB. 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 MogDB.

Syntax

CreateUserMapping ::= CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [ ,' ... '] ) ];

Parameter Description

• user_name

  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.

• server_name

  Specifies the name of the existing server for which a user mapping will be created.

• OPTIONS ( { option_name ' value ' } [, …] )

  Specifies options for user mapping. These options typically define the actual user name and password for this mapping. The option name must be unique. The allowed option names and values are related to the foreign data wrapper of the server.

  NOTE:

  • 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, run the following command to 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".

  • Options supported by oracle_fdw are as follows:

    • user

      Oracle server user name.

    • password

      Password of the Oracle user.

  • Options supported by mysql_fdw are as follows:

    • username

      User name of the MySQL server or MariaDB.

    • password

      User password of the MySQL server or MariaDB.

  • Options supported by postgres_fdw are as follows:

    • user

      User name of the remote MogDB database.

    • password

      User password of the remote MogDB database.

      NOTE: The password entered by the user is encrypted in the MogDB background to ensure security. The key file required for encryption must be generated using the gs_guc tool and released to the $GAUSSHOME/bin directory of each node in MogDB using the gs_ssh tool. The password cannot contain the prefix 'encryptOpt'. Otherwise, it is considered as encrypted ciphertext.

Helpful Links

ALTER USER MAPPING，DROP USER MAPPING