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)

Examples

This example shows how to clone a schema locally as a non-super user:

Create a non-super user named forcs for this example:

edb-psql -p 6543 -U enterprisedb edb
CREATE USER forcs password 'abc123';
Output
CREATE ROLE

Give CREATE privileges to forcs user on edb database:

GRANT CREATE on DATABASE edb to forcs;
Output
GRANT

Create the following extensions:

CREATE EXTENSION postgres_fdw;
CREATE EXTENSION dblink;
CREATE EXTENSION parallel_clone;
CREATE EXTENSION edb_cloneschema;
CREATE EXTENSION edb_job_scheduler;
CREATE EXTENSION DBMS_JOB;
Output
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION

Create the log directory for cloning:

SELECT edb_util.create_clone_log_dir();
Output
 create_clone_log_dir 
----------------------
 t
(1 row)

Give clone schema privilege to forcs user:

SELECT edb_util.grant_clone_schema_privileges('forcs', true, true);
Output
INFO:  Granted USAGE on schema edb_util to forcs.
INFO:  Granted CREATE on schema edb_util to forcs.
INFO:  Granted pg_read_all_settings to forcs.
INFO:  Granted EXECUTE on package SYS.UTL_FILE to forcs.
INFO:  Granted EXECUTE on function pg_catalog.pg_stat_file(text) to forcs.
INFO:  Granted EXECUTE on function pg_catalog.pg_read_file(text, bigint, bigint) to forcs.
INFO:  Granted SELECT on pg_authid to forcs.
INFO:  Granted SELECT on pg_user_mapping to forcs.
INFO:  Granted EXECUTE on function dblink_connect(text, text) to forcs.
INFO:  Granted EXECUTE on function dblink(text, text) to forcs.
INFO:  Granted ALL on directory edb_cs_lfp to forcs.
INFO:  Granted USAGE on foreign data wrapper postgres_fdw to forcs.
 grant_clone_schema_privileges 
-------------------------------
 t
(1 row)

Connect to edb database as forcs user:

edb-psql -h 127.0.0.1 -p 6543 -U forcs edb
Output
Password for user forcs:
edb-psql (17.1.0)
Type "help" for help.

Create a foreign server and a user mapping:

CREATE SERVER local_postgres_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '6543', dbname 'edb');
CREATE USER MAPPING FOR forcs SERVER local_postgres_server OPTIONS (user 'forcs', password 'abc123');
Output
CREATE SERVER
CREATE USER MAPPING

Create a sample schema and tables:

CREATE SCHEMA src;
SET search_path=src, public, "$user$";
create table t1 as (select generate_series(1,100000));
create table t2 as (select generate_series(1,100000));
create table t3 as (select generate_series(1,100000));
create table t4 as (select generate_series(1,100000));
Output
CREATE SCHEMA
SET
SELECT 100000
SELECT 100000
SELECT 100000
SELECT 100000

Clone the schema locally:

SELECT edb_util.localcopyschema('local_postgres_server','src','src2','src2_log',true,true,true,4);
Output
 localcopyschema 
-----------------
 t
(1 row)

This example shows how to clone a schema remotely as a non-super user:

Create a non-super user named forcs for this example:

edb-psql -p 4422 -U enterprisedb edb
CREATE USER forcs PASSWORD 'abc123';
Output
CREATE ROLE

Give CREATE privileges on edb database to forcs user:

GRANT CREATE on DATABASE edb to forcs;
Output
GRANT

Create the following extensions:

CREATE EXTENSION postgres_fdw;
CREATE EXTENSION dblink;
CREATE EXTENSION parallel_clone;
CREATE EXTENSION edb_cloneschema;
CREATE EXTENSION edb_job_scheduler;
CREATE EXTENSION DBMS_JOB;
Output
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION

Create the log directory for cloning:

SELECT edb_util.create_clone_log_dir();
Output
 create_clone_log_dir 
----------------------
 t
(1 row)

Give the clone schema privileges to the non-super user forcs:

SELECT edb_util.grant_clone_schema_privileges('forcs', true, true);
Output
INFO:  Granted USAGE on schema edb_util to forcs.
INFO:  Granted CREATE on schema edb_util to forcs.
INFO:  Granted pg_read_all_settings to forcs.
INFO:  Granted EXECUTE on package SYS.UTL_FILE to forcs.
INFO:  Granted EXECUTE on function pg_catalog.pg_stat_file(text) to forcs.
INFO:  Granted EXECUTE on function pg_catalog.pg_read_file(text, bigint, bigint) to forcs.
INFO:  Granted SELECT on pg_authid to forcs.
INFO:  Granted SELECT on pg_user_mapping to forcs.
INFO:  Granted EXECUTE on function dblink_connect(text, text) to forcs.
INFO:  Granted EXECUTE on function dblink(text, text) to forcs.
INFO:  Granted ALL on directory edb_cs_lfp to forcs.
INFO:  Granted USAGE on foreign data wrapper postgres_fdw to forcs.
 grant_clone_schema_privileges 
-------------------------------
 t
(1 row)

Connect to the edb database as forcs user:

edb-psql -h 127.0.0.1 -p 4422 -U forcs edb
__OUPUT__
Password for user forcs: 
edb-psql (17.1.0)
Type "help" for help.

Create a foreign server and user mapping in source and target database:

CREATE SERVER local_postgres_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '4422', dbname 'edb');
CREATE USER MAPPING FOR forcs SERVER local_postgres_server OPTIONS (user 'forcs', password 'abc123');

CREATE SERVER src_postgres_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '6543', dbname 'edb');
CREATE USER MAPPING FOR forcs SERVER src_postgres_server OPTIONS (user 'forcs', password 'abc123');
Output
CREATE SERVER
CREATE USER MAPPING
CREATE SERVER
CREATE USER MAPPING

Clone the schema from source to target database using remotecopyschema function:

SELECT edb_util.remotecopyschema('src_postgres_server','local_postgres_server','src','src','src_log',true,true,true,4);
Output
 remotecopyschema 
------------------
 t
(1 row)
← Prev

Performing cleanup tasks

↑ Up

Copying a database

Next →

EDB Postgres Advanced Server security features