Help Center> Data Warehouse Service> Developer Guide> Data Export> Using gs_dump and gs_dumpall to Export Data> Data Export By a User Without Required Permissions
View PDF

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 permission, 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 permission. Then, gs_dump or gs_dumpall uses the specified role to export data.

Procedure

  1. Connect to the default database postgres as a database administrator through the database client tool provided by GaussDB(DWS).

    For example, use the gsql client to connect to the database by running the following command:

    1
    		 
    gsql -d postgres -h 192.168.2.30 -U dbadmin -p 8000 -r

    Enter your password as prompted.

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

    User jack does not have the permission 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/dbadmin/backup/MPPDB_backup.tar -p 8000 human_resource --role role1 --rolepassword  abc@1234 -F t
    Table 1 Common parameters

    Parameter

    Description

    Example Value

    -U

    User name 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/dbadmin/backup/MPPDB_backup.tar

    -p

    TCP port or the local Unix-domain socket file extension on which the server is listening for connections.

    -p 8000

    dbname

    Name of the database to be exported.

    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

    Format of exported files. The values of -F are as follows:

    • p: plain text
    • c: custom
    • d: directory
    • t: .tar

    -F t

    For details about other parameters, see gs_dump or gs_dumpall.

Examples

Example 1: User jack does not have the permission to export data of the human_resource database using gs_dump 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.

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

gs_dump -U jack -W 1234@abc -f /home/dbadmin/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 permission to export the public schema using gs_dump and the role role1 has this permission. 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 USER

gs_dump -U jack -W 1234@abc -f /home/dbadmin/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 permission to export all databases in a cluster using gs_dumpall and the role role1 (cluster administrator) has this permission. To 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 USER

gs_dumpall -U jack -W 1234@abc -f /home/dbadmin/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