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
Log in as the OS user omm to the primary node of the database.
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 -f /home/omm/backup/MPPDB_backup.tar -p 8000 human_resource --role role1 --rolepassword abc@1234 -F t Password:
Table 1 Common parameters
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 -f /home/omm/backup/MPPDB_backup11.tar -p 8000 human_resource --role role1 --rolepassword abc@1234 -F t
Password:
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 -f /home/omm/backup/MPPDB_backup12.tar -p 8000 human_resource -n public --role role1 --rolepassword abc@1234 -F t
Password:
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 -f /home/omm/backup/MPPDB_backup.sql -p 8000 --role role1 --rolepassword abc@1234
Password:
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