Cloning schema as a non-super user v17

Suggest edits

You can now clone the schema as a non-super user. This two functions are created while creating the extension:

  • GRANT_CLONE_SCHEMA_PRIVILEGES - Grants the privileges to a non-super user to clone the schema.
  • REVOKE_CLONE_SCHEMA_PRIVILEGES - Revokes the privileges from a non-super user for cloning the schema.

GRANT_CLONE_SCHEMA_PRIVILEGES

You can grant the clone schema privileges to a non-super user using this function.

Syntax:

GRANT_CLONE_SCHEMA_PRIVILEGES(<user_name> TEXT, [<allow_remote_schema_clone> BOOLEAN], [<print_commands> BOOLEAN])

Where,

user_name

Name of the user to whom privileges are to be granted to do local cloning.

allow_remote_schema_clone

Optionally provide a boolean value to this parameter to control the remote cloning by the user. By default the value is set to False. The true value grants user the privileges to do remote cloning.

print_commands

Optionally provide a boolean value to this parameter to control printing of the executed commands. By default the value is set to false. The true value prints the executed commands on the terminal.

This example shows how to grant a non-super user ec2-user the privileges for local and remote cloning:

SELECT edb_util.grant_clone_schema(user_name => 'ec2-user',
                                   allow_remote_schema => true,
                                   print_commands => true);
Output
INFO:  Executed command: GRANT USAGE ON SCHEMA edb_util TO "ec2-user"
INFO:  Executed command: GRANT pg_read_all_settings TO "ec2-user"
INFO:  Executed command: GRANT EXECUTE ON PACKAGE SYS.UTL_FILE TO "ec2-user"
INFO:  Executed command: GRANT EXECUTE ON FUNCTION pg_catalog.pg_stat_file(text) TO "ec2-user"
INFO:  Executed command: GRANT EXECUTE ON FUNCTION pg_catalog.pg_read_file(text, bigint, bigint) TO "ec2-user"
INFO:  Executed command: GRANT SELECT ON pg_authid TO "ec2-user"
INFO:  Executed command: GRANT SELECT ON pg_user_mapping TO "ec2-user"
INFO:  Executed command: GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO "ec2-user"
INFO:  Executed command: GRANT EXECUTE ON FUNCTION dblink(text, text) TO "ec2-user"
INFO:  Executed command: GRANT EXECUTE ON FUNCTION pg_catalog.pg_file_write(text, text, boolean) TO "ec2-user"
INFO:  Executed command: GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO "ec2-user"
┌───────────────────────────────┐
│ grant_clone_schema_privileges │
├───────────────────────────────┤
│ t                             │
└───────────────────────────────┘
(1 row)

REVOKE_CLONE_SCHEMA_PRIVILEGES

You can revoke the clone schema privileges from a non-super user using this function.

Syntax:

revoke_clone_schema_privileges(<user_name> TEXT[, <revoke_remote_schema> BOOLEAN][,<print_commands> BOOLEAN])

Where,

user_name

Name of the user from whom we want to revoke the cloning privileges.

revoke_remote_schema_clone

Optionally provide a boolean value to this parameter to control the remote cloning by the user. By default the value is set to False. The true value revokes the remote cloning privileges from the user.

print_commands

Optionally provide a boolean value to this parameter to control printing of the executed commands. By default the value is set to false. The true value prints the executed commands on the terminal.

This example shows how to revoke cloning privileges from the ec2-user user.

SELECT edb_util.revoke_clone_schema_privileges(user_name => 'ec2-user',
                                              revoke_remote_schema_clone => true,
                                              print_commands => true);
Output
INFO:  Revoked USAGE on schema edb_util from ec2-user.
INFO:  Revoked pg_read_all_settings from ec2-user.
INFO:  Revoked EXECUTE on package SYS.UTL_FILE from ec2-user.
INFO:  Revoked EXECUTE on function pg_catalog.pg_stat_file(text) from ec2-user.
INFO:  Revoked EXECUTE on function pg_catalog.pg_read_file(text, bigint, bigint) from ec2-user.
INFO:  Revoked SELECT on pg_authid from ec2-user.
INFO:  Revoked SELECT on pg_user_mapping from ec2-user.
INFO:  Revoked EXECUTE on function dblink_connect_u(text, text) from ec2-user.
INFO:  Revoked EXECUTE on function dblink(text, text) from ec2-user.
INFO:  Revoked EXECUTE on function pg_catalog.pg_file_write(text, text, boolean) from ec2-user.
INFO:  Revoked USAGE on foreign data wrapper postgres_fdw from ec2-user.
┌────────────────────────────────┐
│ revoke_clone_schema_privileges │
├────────────────────────────────┤
│ t                              │
└────────────────────────────────┘
(1 row)
← Prev

Performing cleanup tasks

↑ Up

Copying a database

Next →

EDB Postgres Advanced Server security features