Data Export By a User Without Required Permissions

gs_dump and gs_dumpall use -U to specify the user that performs the export. If the specified user does not have the required permissions, data cannot be exported. In this case, you can set -role in the gs_dump or gs_dumpall command to the role that has the permissions. Then, gs_dump or gs_dumpall uses the specified role to export data.

Procedure

1. Log in as the OS user omm to the primary node of the database.

2. Use gs_dump to export data of the human_resource database.

   User jack does not have the permissions to export data of the human_resource database and the role role1 has this permission. To export data of the human_resource database, you can set -role to role1 in the gs_dump command. The exported files are in .tar format.

   gs_dump -U jack -W Bigdata@234 -f /home/omm/backup/MPPDB_backup.tar -p 8000 human_resource --role role1 --rolepassword  abc@1234 -F t

   Table 1 Common parameters

   Parameter	Description	Example Value
   -U	Username for database connection.	-U jack
   -W	User password for database connection. - This parameter is not required for database administrators if the trust policy is used for authentication. - If you connect to the database without specifying this parameter and you are not a database administrator, you will be prompted to enter the password.	-W Bigdata@123
   -f	Folder to store exported files. If this parameter is not specified, the exported files are stored in the standard output.	-f /home/omm/backup/MPPDB_backup.tar
   -p	TCP port or local Unix-domain socket file extension on which the server is listening for connections.	-p 8000
   dbname	Name of the database to export.	human_resource
   -role	Role name for the export operation. After this parameter is set, the SET ROLE command will be issued after gs_dump or gs_dumpall connects to the database. It is useful when the user specified by -U does not have the permissions required by gs_dump or gs_dumpall. This parameter allows you to switch to a role with the required permissions.	-r role1
   -rolepassword	Role password.	-rolepassword abc@1234
   -F	Select the format of file to export. The values of -F are as follows: - p: plaintext - c: custom - d: directory - t: .tar	-F t

   For details about other parameters, see "Server Tools > gs_dump" or "Server Tools > gs_dumpall" in the Tool Reference.

Examples

Example 1: User jack does not have the permissions required to export data of the human_resource database using gs_dump and the role role1 has the permissions. To export data of the human_resource database, you can set -role to role1 in the gs_dump command. The exported files are in .tar format.

human_resource=# CREATE USER jack IDENTIFIED BY "1234@abc";
CREATE ROLE

gs_dump -U jack -W 1234@abc -f /home/omm/backup/MPPDB_backup11.tar -p 8000 human_resource --role role1 --rolepassword abc@1234 -F t
gs_dump[port='8000'][human_resource][2017-07-21 16:21:10]: dump database human_resource successfully
gs_dump[port='8000'][human_resource][2017-07-21 16:21:10]: total time: 4239  ms

Example 2: User jack does not have the permissions required to export the public schema using gs_dump and the role role1 has the permissions. To export the public schema, you can set -role to role1 in the gs_dump command. The exported files are in .tar format.

human_resource=# CREATE USER jack IDENTIFIED BY "1234@abc";
CREATE ROLE

gs_dump -U jack -W 1234@abc -f /home/omm/backup/MPPDB_backup12.tar -p 8000 human_resource -n public --role role1 --rolepassword abc@1234 -F t
gs_dump[port='8000'][human_resource][2017-07-21 16:21:10]: dump database human_resource successfully
gs_dump[port='8000'][human_resource][2017-07-21 16:21:10]: total time: 3278  ms

Example 3: User jack does not have the permissions required to export all databases in a cluster using gs_dumpall and the role role1 (cluster administrator) has the permissions. To export all the databases, you can set -role to role1 in the gs_dumpall command. The exported files are in text format.

human_resource=# CREATE USER jack IDENTIFIED BY "1234@abc";
CREATE ROLE

gs_dumpall -U jack -W 1234@abc -f /home/omm/backup/MPPDB_backup.sql -p 8000 --role role1 --rolepassword abc@1234
gs_dumpall[port='8000'][human_resource][2018-11-14 17:26:18]: dumpall operation successful
gs_dumpall[port='8000'][human_resource][2018-11-14 17:26:18]: total time: 6437  ms