Database Privileges

Repstance connects to each database with the login that is given in the user parameter of the commands prepare database, validate database and remove database, and in the configuration of the Capture and Apply Processes. The same login is used for all of them: Repstance has no separate administrator and runtime accounts. This page lists what the login has to be allowed to do, command by command, for every supported database.

Repstance does not create the login and does not grant privileges to it. The administrator of the database creates the login and gives it the privileges described below. The only grant that Repstance makes itself is GRANT EXECUTE ON SCHEMA::[repi] TO [public] on a SQL Server Source (see SQL Server as Source).

The Repstance Objects

The commands keep their own objects in the database:

What the Commands Do with the Login

• prepare database creates the Repstance objects. With generatesql=1 it creates nothing and returns the SQL script that an administrator can run. See Prepare Source Database and Prepare Target Database.
• validate database checks that the Repstance objects exist. It does not test the privileges that the Capture and Apply Processes need later; only the Oracle Source also checks that the login can read the system views it uses. It adds a warning to a valid result when objects that only some functions need (the heartbeat, the data comparison, the initial load) do not exist. See Validate Database.
• Capture and Apply Processes read and write with the same login. A missing privilege is reported by the database itself: its error text appears in the result of the command, in the log of the process (log/capture-{id}-{DD-MM-YYYY}.log, log/apply-{id}-{DD-MM-YYYY}.log) and in the status of the process.
• remove database drops the Repstance objects. The replicated tables are not touched. See Remove Repstance Objects from Database.

Note – The catalog views that the checks read (INFORMATION_SCHEMA of PostgreSQL, MySQL, Redshift and Snowflake, the catalog views of SQL Server) list only the objects that the login is allowed to see. A missing privilege can therefore show up as Schema objects do not exist.

Note – With generatesql=1 an administrator runs the script, so the Repstance objects belong to the administrator, and the login of the processes has to be given the privileges on them: SELECT, INSERT, UPDATE and DELETE on the tables, EXECUTE on the procedures and functions, USAGE on the schema. The simplest way is to run the script as the login that the processes use.

Note – The privileges below are what the statements that Repstance runs need under the rules of the database. A login with the administrator role of the database normally has them; some belong to the server and not to the database: sysadmin for sys.sp_cdc_enable_db on SQL Server, a superuser (or the SET privilege) for repuser=1 on PostgreSQL, SYSDBA on the ASM instance of Oracle.

SQL Server

SQL Server as Source

The command prepare database=source runs the steps below, each in its own batch, in this order; the first error stops it. With generatesql=1 the statements are returned as a script instead (the connection is still opened, to find out whether the server is a PaaS one). The last column is what SQL Server requires for the statement. The parameters are described in SQL Server as a Source Database.

The grant of step 8 gives every database user EXECUTE on the procedures of repi. The DDL trigger repi_ddl_capture_tr calls repi.ddl_capture_prc with the rights of the user who runs the DDL statement, so each user who runs DDL needs that right. If you run the generated script, run the two statements of step 8 yourself. The parameter housekeeping decides which jobs of step 7 are created.

The command validate database=source runs one query and answers the first problem it finds as Database is not prepared for capture: [{message}: {number}]. The messages are:

• CDC is not enabled
• repi schema does not exist
• DDL trigger does not exist
• Schema objects do not exist, with a number that identifies the missing object: a Repstance table, the change table of ddl_capture, hb_stat or ld_capture, the trigger on the change table of hb_stat, the procedure repi.ddl_capture_prc or the table repi.capture_ckpt
• SQL Server Agent is not running: not checked on a PaaS database; the check reads sys.dm_server_services, which needs the VIEW SERVER STATE permission (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later)

A valid database is answered with Database {NAME} is valid; the same check runs when a Capture Process starts. validate adds a warning to a valid result when repi.ValidateTables, its change table cdc.repi_repi_ValidateTables_CT or the procedure repi.Housekeeping_prc does not exist (the data comparison and the housekeeping use them; run prepare database again to create them), and when msdb cannot be read (see Amazon RDS, Google Cloud SQL and Azure below).

The Capture Process (SQL Server Capture Process Parameters) needs, for the statements it runs:

• SELECT on the CDC change tables (cdc.*, cdc.lsn_time_mapping), on the tables of repi and on the catalog views (sys.tables, sys.columns, sys.indexes and others).
• INSERT, UPDATE and DELETE on repi.dml_capture, repi.dml_capture_obj, repi.ddl_capture_obj, repi.dml_capture_map, repi.ld_capture and repi.capture_ckpt.
• db_owner (and ALTER ANY DATABASE DDL TRIGGER) when the include or exclude rules change and when a captured table is created, altered or dropped: sys.sp_cdc_enable_table, sys.sp_cdc_disable_table, CREATE TRIGGER and ALTER TRIGGER on a change table, enabling and disabling the trigger repi_ddl_capture_tr.
• When the CDC job makes no progress for 15 minutes and the parameter cdcrestart is not 0: SELECT on msdb.dbo.sysjobs, msdb.dbo.sysjobactivity and sys.dm_cdc_errors, and db_owner for sys.sp_cdc_start_job.
• Initial load and repair: SELECT on the captured tables. With loadconsistent the snapshot of a database that already has snapshot isolation is used; Repstance never enables it.

remove database=source drops the trigger, the jobs, the procedures, the functions repi.map and repi.map_obj, the tables (ValidateTables and hb_stat only when repi.apply_ckpt does not exist) and the schema repi, and disables CDC for the database: sys.sp_cdc_disable_db needs sysadmin (Amazon RDS: msdb.dbo.rds_cdc_disable_db).

Amazon RDS, Google Cloud SQL and Azure. Repstance recognises Amazon RDS by the database rdsadmin and Google Cloud SQL by the procedure msdb.dbo.gcloudsql_cdc_enable_db. A database is handled as a PaaS database when Repstance cannot read msdb.dbo.sysjobs, which is what it expects on Azure SQL Database and on Google Cloud SQL: no SQL Server Agent jobs are created or checked, and the Capture Process writes the heartbeat and runs the housekeeping itself. If the login cannot read msdb.dbo.sysjobs on any other server, validate database and prepare database add a warning with the error of SQL Server. When that server has SQL Server Agent, give the login the role SQLAgentUserRole in msdb and prepare the database again.

SQL Server as Target

The command prepare database=target creates the schema repi, the tables apply_ckpt, apply_ckpt_history, hb_stat and ValidateTables (and the column ldckpt of an older apply_ckpt), and the procedures repi.resetCkpt, repi.updateCkpt, repi.updateMDCkpt and repi.updateLDCkpt. It creates no CDC, no jobs, no triggers and grants nothing. It needs CREATE SCHEMA, CREATE TABLE and CREATE PROCEDURE, and ALTER on the schema and its tables. With generatesql=1 no connection is made. The parameters are described in SQL Server as a Target Database.

The command validate database=target answers repi schema does not exist, or Schema objects do not exist for repi.apply_ckpt, repi.apply_ckpt_history, the procedures resetCkpt, updateCkpt, updateMDCkpt and updateLDCkpt, or repi.hb_stat, in the form Database is not prepared for apply: [{message}: {number}]. A valid database is answered with Database {NAME} is valid, with a warning when repi.ValidateTables does not exist. The command validate process=apply (Validate Capture or Apply Process) adds warnings about the tables and columns of the Target, for example MISSED TABLES: or COLUMNS NARROWER ON TARGET THAN SOURCE NEEDS:; a table that the login cannot see is reported as missing.

The Apply Process (SQL Server Apply Process Parameters) needs, for the statements it runs:

• INSERT, UPDATE, DELETE and SELECT on every replicated table, and on repi.hb_stat and repi.ValidateTables (they are replicated like user tables); SELECT and INSERT on repi.apply_ckpt; EXECUTE on the procedures of repi.
• Bulk load and initial load: INSERT BULK (INSERT on the table; SQL Server also asks for ALTER on the table when a bulk load goes into a table that has constraints or triggers and does not check or fire them), temporary tables #{schema}_{table}_TMP in tempdb, and merge for the parameter mergetables.
• ALTER on a table with identity columns (set identity_insert {table} on and off).
• The privileges of the replayed DDL statement (CREATE, ALTER or DROP TABLE, CREATE or DROP INDEX, sp_rename). With ddldisabletriggers=1 also ALTER on the table for ALTER TABLE ... DISABLE TRIGGER.
• The initial load modes: delete from {table} needs DELETE, truncate table {table} needs ALTER, and creating or dropping a table needs the privileges for it.
• A login with the role db_owner of the Target database has all of them.

remove database=target drops the four tables, the four procedures and the schema repi (ValidateTables and hb_stat only when repi.dml_capture does not exist). Removing an Apply Process deletes only its rows in apply_ckpt and apply_ckpt_history.

Oracle as Source

The login is an ordinary login: Repstance never connects to the Source AS SYSDBA (only the ASM connection does). The parameter user is required; with connectiontype=tns the password is optional, a wallet can be used. In a container database dbname names the container to capture. The parameters are described in Oracle as a Source Database and, for the Capture Process, in Oracle Capture Process Parameters.

The command prepare database=source runs on the connection of the login:

With generatesql=1 the DDL statements are not run but returned as a script (each one is followed by /); the queries of step 1 and 3 still run, so the login still needs the SELECT privileges.

Note – REPI is a locked user and Repstance grants nothing on its objects. The login that runs the Capture Process can use them only when it has the privileges for it: object grants made by an administrator, ANY privileges (SELECT ANY TABLE, INSERT ANY TABLE, DELETE ANY TABLE), or the DBA role.

The command validate database=source runs select 1 from {object} where 0=1 for each object below (for the packages an anonymous block). A failure is answered with No privileges were granted for "{OBJECT}" object; when there is such a message the result is Invalid.

• Always: DBA_CONSTRAINTS, DBA_IND_COLUMNS, DBA_INDEXES, DBA_LOBS, DBA_OBJECTS, DBA_TAB_COLS, DBA_TABLES, DBA_USERS, GV$ARCHIVED_LOG, GV$LOG, GV$LOGFILE, GV$TRANSACTION, V$DATABASE, V$LOG_HISTORY, V$RECOVERY_FILE_DEST, V$ARCHIVE_DEST and the package DBMS_FLASHBACK (EXECUTE).
• With directlogmode=0 also V$LOGMNR_CONTENTS and the package DBMS_LOGMNR (EXECUTE).
• With directlogmode=1 also DBA_DIRECTORIES.
• For a container database (parameter dbname) also CDB_TABLES, CDB_OBJECTS, CDB_LOBS, CDB_INDEXES, CDB_CONSTRAINTS, CDB_TAB_COLS and CDB_IND_COLUMNS.

When these can be read, the command checks the database and answers one of these messages:

• Archive log mode is not enabled
• Minimum Database-level Supplemental Logging is not enabled
• "{REPI}" user does not exist
• "{REPI}.DML_CAPTURE_OBJECTS" table does not exist (directlogmode=0)
• ARCHIVELOG_DIR directory does not exist, ONLINELOG_DIR directory does not exist (directlogmode=1)
• DB[] has not been found. Available DBs are:[...]: a container database needs dbname

A valid database is answered with Database {NAME} is valid; the same check runs every time a Capture Process starts. With locallogdir and directlogmode=1 the checks of the directories are skipped. Warnings are added to a valid result when the login cannot read V$INSTANCE, GV$THREAD or GV$LOG_HISTORY (the Capture Process reads them; the warning gives the statements to run, for example GRANT SELECT ON SYS.V_$INSTANCE TO {user}, GRANT SELECT ON SYS.GV_$THREAD TO {user}), when REPI.VALIDATETABLES or REPI.VALIDATE_SQ does not exist (data comparison), and when the login cannot read the XDB token tables that the binary XMLType columns need (then these columns are NULL in the Target; the warning lists the GRANT SELECT ON XDB.XDB$TTSET, XDB.X$QN{suffix} and XDB.X$NM{suffix} statements).

The Capture Process reads, by the value of directlogmode:

• All values: ALL_USERS, ALL_OBJECTS, V$CONTAINERS, V$INSTANCE, NLS_DATABASE_PARAMETERS, the DBA_ views (CDB_ views with dbname) and the package DBMS_FLASHBACK.
• 0 (LogMiner): the package DBMS_LOGMNR (add_logfile, start_logmnr, end_logmnr), V$LOGMNR_CONTENTS, GV$ARCHIVED_LOG, GV$LOG, GV$LOGFILE, V$LOG_HISTORY, and INSERT, DELETE and SELECT on REPI.DML_CAPTURE_OBJECTS. Oracle may require further privileges for LogMiner (for example LOGMINING or EXECUTE_CATALOG_ROLE, depending on the version): see the Oracle documentation.
• 1 (directories): the log list from GV$ARCHIVED_LOG, GV$LOG and GV$LOGFILE; the files are read through the directory objects ONLINELOG_DIR and ARCHIVELOG_DIR (bfilename, DBMS_LOB, and UTL_COMPRESS with compressredo=1), so the login needs READ on both directories. The user that created a directory has READ and WRITE; another login needs GRANT READ ON DIRECTORY .... With asyncredo=1 also GV$THREAD, GV$LOG_HISTORY and DBMS_LOB.FILEEXISTS.
• 2 (ASM): a second connection with asmuser, asmpassword and asmservicename (a TNS alias) as SYSDBA on the ASM instance, and the package DBMS_DISKGROUP. prepare and validate do not use these parameters; a wrong ASM login shows at the first read.
• Initial load, compare and repair: select ... from {owner}.{table} as of scn {n}, so SELECT on every captured table (a flashback query: depending on the configuration of the database the FLASHBACK privilege may be needed too), alter session set nls_..., alter session set container={dbname} in a container database, a global temporary table REPI_TMP_CAP_{id} in the schema of the login (CREATE TABLE), and DML on REPI.ValidateTables and the sequence REPI.VALIDATE_SQ.

remove database=source drops the tables of REPI (truncate, then drop), VALIDATETABLES (unless REPI.APPLY_CKPT exists), the sequence VALIDATE_SQ, the functions, packages and types of older versions, and the user REPI (without CASCADE, only when it owns no object). The supplemental logging, the directories and the grants stay. It needs SELECT on dba_tables, dba_objects and dba_users, and DROP ANY TABLE, DROP ANY SEQUENCE, DROP ANY PROCEDURE, DROP ANY TYPE and DROP USER.

Oracle as Target

The parameters are described in Oracle as a Target Database and, for the Apply Process, in Oracle Apply Process Parameters. The command prepare database=target is one anonymous PL/SQL block; each step runs only when its object does not exist. A failure is answered with ORA-20010: SQL: [{statement}] failed. Error: [{error}], and the steps before it stay. The helper user is created with the default tablespace USERS (it must exist) and an unlimited quota on it. With generatesql=1 the block is returned and not run (the connection is still opened, to find the name of the helper user).

As on the Source, REPI is locked and nothing is granted on its objects: the login of the Apply Process can use them only when it has the privileges for it (object grants, ANY privileges or the DBA role). The helper user is named REPI on Amazon RDS, in a pluggable database and in a database that is not a container database; in the root of a container database and on Oracle 11g it is C##REPI.

The command validate database=target tests that seven objects exist: the table APPLY_CKPT, the sequence APPLY_CKPT_HISTORY_SQ, the table APPLY_CKPT_HISTORY and the procedures RESETCKPT, UPDATECKPT, UPDATEMDCKPT and UPDATELDCKPT. It answers Database is not prepared for apply: [Schema objects do not exist: {n}] or Database is valid. The same check runs when an Apply Process starts. A warning is added to a valid result when the login cannot read {REPI}.APPLY_CKPT (the existence is tested in DBA_TABLES, which says nothing about the right to use the table), and when {REPI}.HB_STAT or {REPI}.VALIDATETABLES does not exist. The command validate process=apply adds the warnings MISSED TABLES:, TABLES WITH ENABLED TRIGGERS: and TABLES WITHOUT INDEXES:; a login without SELECT on DBA_INDEXES or DBA_TRIGGERS gets no warnings.

The Apply Process needs, for the statements it runs:

• Session: alter session set nls_date_format, nls_timestamp_format, NLS_TIMESTAMP_TZ_FORMAT and time_zone, and dbms_application_info.set_module. At the start it drops the leftover temporary tables TMP_%_{id} of the schema REPI (DROP ANY TABLE).
• DML on OWNER.TABLE, always with the owner of the Source or the name of a map rule (there is no fallback to the current schema): INSERT, UPDATE, DELETE and SELECT on every replicated table, either as the owner (map the Source schema to the name of the login) or with the ANY TABLE privileges or object grants. merge into for mergetables.
• The objects of REPI: SELECT, INSERT and DELETE on apply_ckpt and apply_ckpt_history, EXECUTE on the four procedures, DML on HB_STAT and VALIDATETABLES.
• DDL: alter session set current_schema={owner} and then the DDL (the parameter ddlprocessing is native by default on Oracle: the text of the Source). As the login in the schema of the owner: CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, CREATE ANY INDEX and DROP ANY INDEX; or own the tables and have CREATE TABLE, CREATE INDEX and a quota.
• Initial load modes: delete from {table} (DELETE ANY TABLE), truncate table {table} (DROP ANY TABLE in another schema), creating and dropping a table (CREATE ANY TABLE, a quota of the owner).
• Compare: a global temporary table TMP_REP_{apply id} (CREATE TABLE).
• Triggers, constraints and sequences of the Target are not changed; validate process only reports them.

remove database=target drops REPI.APPLY_CKPT, HB_STAT, VALIDATETABLES (only when the sequence VALIDATE_SQ does not exist), the sequences and tables of the history, the four procedures and the user REPI (without CASCADE, only when it owns no object). It needs SELECT on the dba_ views, DROP ANY TABLE, DROP ANY SEQUENCE, DROP ANY PROCEDURE and DROP USER. Removing an Apply Process deletes its rows from APPLY_CKPT and APPLY_CKPT_HISTORY.

PostgreSQL as Target

The login connects with sslmode=require (and falls back to disable when the server has no SSL) and runs set timezone TO 'UTC'. The parameters are described in PostgreSQL as a Target Database and, for the Apply Process, in PostgreSQL Apply Process Parameters.

The command prepare database=target runs one script: create schema if not exists repi; the tables repi.apply_ckpt, repi.apply_ckpt_history, repi.hb_stat and repi.ValidateTables; alter table ... set data type bigint for three columns; drop function if exists for the functions of older versions; and create or replace function for repi.resetCkpt, repi.updateCkpt, repi.updateLDCkpt and repi.updateMDCkpt (language plpgsql). It needs CREATE on the database (also when repi exists: PostgreSQL checks it before IF NOT EXISTS), CREATE on the schema repi, ownership of the objects it replaces, and USAGE on the language plpgsql (PUBLIC has it by default). With generatesql=1 no connection is made.

The command validate database=target runs one query with five checks on INFORMATION_SCHEMA: the tables repi.apply_ckpt and repi.apply_ckpt_history and the routines resetckpt, updateckpt and updatemdckpt. It answers Database is not prepared for apply: [Schema objects do not exist: {n}] or Database {NAME} is valid, and adds a warning to a valid result when hb_stat, ValidateTables or updateLDCkpt does not exist. An empty dbname is answered with No database is provided.

The Apply Process needs, for the statements it runs:

• With repuser=1: set session_replication_role = replica (the triggers and foreign keys of the Target do not fire). That needs a superuser on PostgreSQL 14 and earlier and the SET privilege on this parameter on 15 and later; on Amazon RDS and Aurora normally a member of rds_superuser. When it fails, the connection fails.
• The objects of repi: SELECT, INSERT, UPDATE and DELETE on its tables, USAGE on the schema, USAGE on the sequence apply_ckpt_history_id_seq (the functions run with the rights of the caller).
• DML (start transaction; prepare ...; execute ...; deallocate ...; commit;): SELECT, INSERT, UPDATE and DELETE on every replicated table, also on repi.hb_stat and repi.ValidateTables, and USAGE on the schemas.
• Bulk load (parameter bulkthreshold): create temporary table {schema}_{table}_TMP (TEMPORARY on the database, which PUBLIC has by default), COPY ... FROM STDIN (no privilege on files), update ... from, delete ... using, insert ... select. merge for mergetables needs PostgreSQL 15 or later.
• Initial load: COPY {table} FROM STDIN after delete from {table}, truncate table {table}, create table if not exists or drop table if exists and create: DELETE, TRUNCATE, CREATE on the schema, or ownership.
• DDL replay (the parameter ddlprocessing is dictionary by default; each statement starts with SET SEARCH_PATH TO {schema}): create table, drop table, alter table (rename, add, drop, alter column, not null, primary key), create unique index, drop index and truncate: CREATE on the target schemas, and ownership (or membership in the role of the owner) for ALTER, DROP, TRUNCATE and CREATE INDEX.
• Compare: a temporary table TMP_VALIDATION_{id} and COPY.

remove database=target drops the four tables and the four functions, then drop schema if exists repi when nothing is left in it (ownership).

MySQL as Target

This page covers MySQL, Aurora MySQL and MariaDB. Repstance uses one connection with autocommit off. The parameters are described in MySQL as a Target Database and, for the Apply Process, in MySQL Apply Process Parameters.

The command prepare database=target runs create database if not exists repi and creates the tables repi.apply_ckpt, repi.apply_ckpt_history, repi.hb_stat and repi.ValidateTables in one call; then, for each procedure resetCkpt, updateCkpt, updateMDCkpt and updateLDCkpt, DROP PROCEDURE IF EXISTS repi.{name} and CREATE procedure repi.{name} (no DEFINER is given, so the definer is the login). It needs on repi.*: CREATE, CREATE ROUTINE and ALTER ROUTINE (for the DROP), also when repi exists. With generatesql=1 no connection is made; the script changes the delimiter with DELIMITER $$ because the procedures contain semicolons, so run it with the mysql client.

The command validate database=target runs five checks on INFORMATION_SCHEMA: the tables repi.apply_ckpt and repi.apply_ckpt_history and the routines resetCkpt, updateCkpt and updateMDCkpt. It answers Database is not prepared for apply: [Schema objects do not exist: {n}] or Database {NAME} is valid, and adds a warning to a valid result when hb_stat, ValidateTables or updateLDCkpt does not exist. The parameter dbname is optional; it is the default database of the connection.

The Apply Process needs, for the statements it runs:

• SELECT, INSERT, UPDATE and DELETE on repi.apply_ckpt and repi.apply_ckpt_history, and EXECUTE on the procedures (they are called with call).
• DML (prepare sN from ...; execute sN using ...): SELECT, INSERT, UPDATE and DELETE on the replicated tables.
• Initial load, bulk load and compare use LOAD DATA LOCAL INFILE 'Reader::repstance_bulk_stream' INTO TABLE ...: INSERT on the table. The server has to allow local_infile (a server variable; on Amazon RDS and Aurora a parameter of the parameter group). Repstance does not check it and uses no FILE or SUPER privilege.
• Bulk load: create temporary table repi.TMP_{hash}_{id} (CREATE TEMPORARY TABLES on repi.*), update ... join, delete ... where exists, insert ... select and ON DUPLICATE KEY UPDATE for mergetables.
• DDL replay (the statement carries the schema): CREATE, ALTER, DROP and INDEX on the target databases; truncate needs DROP.
• Compare: create temporary table TMP_VALIDATION_{id} in the default database of the connection (CREATE TEMPORARY TABLES there).

remove database=target drops the four tables and the four procedures of repi, then drop database if exists repi when no table is left (DROP and ALTER ROUTINE).

Amazon Redshift as Target

The parameters are described in Redshift as a Target Database and, for the Apply Process, in Redshift Apply Process Parameters. The command prepare database=target runs create schema if not exists repi, creates the tables repi.apply_ckpt, repi.apply_ckpt_history, repi.hb_stat and repi.ValidateTables and the procedures repi.resetCkpt, repi.updateCkpt, repi.updateLDCkpt and repi.updateMDCkpt (create or replace procedure, language plpgsql). It needs CREATE on the database, CREATE on the schema repi and USAGE on the language plpgsql. With generatesql=1 no connection is made.

The command validate database=target runs the same five checks as PostgreSQL and answers in the same words; it adds a warning to a valid result when hb_stat or ValidateTables does not exist. It does not test the parameters s3.bucketname, s3.iamrole, the keys or the region.

The Apply Process works with three parties:

• The database user needs the same as on PostgreSQL: SELECT, INSERT, UPDATE and DELETE on the replicated tables and the tables of repi, call repi.updateCkpt(...), temporary tables {schema}_{table}_TMP (TEMP on the database), DDL replay with SET SEARCH_PATH (Redshift gets no index), and INSERT on the table for COPY.
• The host of the Apply Process writes gzip CSV files to the S3 bucket for the bulk load and the initial load. It uses the keys s3.accesskeyid and s3.secretaccesskey (static keys; there is no session token) or, when the key id is empty, the default credential chain of the host (environment variables, the .aws folder of the user that runs Repstance, the instance profile). It calls CreateMultipartUpload, UploadPart (parts of 5 MiB), CompleteMultipartUpload, AbortMultipartUpload (after a failed part), PutObject (files of size 0) and DeleteObject; it reads and lists nothing. The IAM actions are s3:PutObject, s3:AbortMultipartUpload and s3:DeleteObject on arn:aws:s3:::{bucket}/*. The files are written at the root of the bucket: {tmp}.csv.gz for the bulk load, {schema.table}.csv.gz.NNN for the initial load and TMP_VALIDATION_{id}.csv.gz for the compare.
• Redshift reads the files with COPY {table} FROM 's3://{bucket}/{key}' IAM_ROLE '{s3.iamrole}' ... GZIP. The only authorisation is the IAM role (the keys never reach Redshift): it has to be attached to the cluster and to allow s3:GetObject and s3:ListBucket on the bucket, and the use of the key when the bucket is encrypted with a KMS key. The bucket has to be in the region of the cluster. The COPY of the initial load names a prefix ({table}.csv.gz.), so any other key that starts with it is loaded too.

remove database=target runs drop schema repi cascade, which drops everything in repi without checking whether anything else is there. It needs the ownership of repi or a superuser.

Snowflake as Target

The login (a user with a password, or with an RSA key pair when authtype=1) works with its default role: Repstance has no role parameter and never runs USE ROLE, so set the DEFAULT_ROLE of the user. The warehouse is named in the connection; the login needs USAGE on it, and the warehouse should resume automatically. Repstance creates the schema REPI and nothing else: the schemas of the replicated tables have to exist. The parameters are described in Snowflake as a Target Database and, for the Apply Process, in Snowflake Apply Process Parameters.

The command prepare database=target runs, one statement at a time: alter session set MULTI_STATEMENT_COUNT = 0, create schema if not exists repi, create table if not exists for REPI.APPLY_CKPT, REPI.HB_STAT and REPI.ValidateTables, use schema REPI and create or replace file format repi_csv_format type = csv .... It needs USAGE on the warehouse and the database, CREATE SCHEMA on the database, and CREATE TABLE and CREATE FILE FORMAT on REPI (the ownership of an existing file format when the command is run again). With generatesql=1 no connection is made.

The command validate database=target tests one thing: that INFORMATION_SCHEMA.TABLES has REPI.APPLY_CKPT. It answers Database is not prepared for apply: [Schema objects do not exist: 1] or Database {NAME} is valid, and adds a warning to a valid result when HB_STAT, VALIDATETABLES or the file format REPI_CSV_FORMAT does not exist. It needs a running warehouse.

The Apply Process needs, for the statements it runs:

• SELECT, INSERT, UPDATE and DELETE on REPI.APPLY_CKPT.
• DML (EXECUTE IMMEDIATE $$ DECLARE ... BEGIN ... END; $$): SELECT, INSERT, UPDATE and DELETE on the replicated tables; merge into for mergetables.
• Bulk load and initial load use the user stage @~/staged (put, rm, then insert into {table} select ... from @~/staged/ (pattern => ..., file_format => 'repi_csv_format')). There is no COPY INTO, no named stage and no CREATE STAGE. The user stage needs no grant; the login needs USAGE on the file format repi_csv_format. The host of the Apply Process has to reach the storage of the Snowflake stage.
• Bulk load: create temporary table if not exists TMP_{hash}_{id} in REPI (CREATE TABLE on REPI).
• Initial load modes: delete from, truncate table, creating and dropping a table.
• DDL replay (the parameter ddlprocessing is dictionary by default): create table, drop table, alter table (rename, add, drop, modify, rename column, not null) and truncate: CREATE TABLE on the target schemas and the ownership of the tables for ALTER and DROP.
• Compare: create or replace temp table REPI.ValidateChunksApply_tmp_{id}.

remove database=target runs drop schema repi cascade, which needs the ownership of REPI. Removing an Apply Process only deletes its row from REPI.APPLY_CKPT; the staged files and the temporary tables stay.

AWS S3 as Target

There is no database: the host of the Apply Process writes files to an S3 bucket. The parameters are described in AWS S3 as a Target and, for the Apply Process, in AWS S3 Apply Process Parameters.

• Credentials. The parameters s3.accesskeyid and s3.secretaccesskey (static keys; there is no session token, so temporary STS keys cannot be given) or, when the key id is empty, the default credential chain of the host (environment variables, the .aws folder of the user that runs Repstance, the instance profile or role). An instance profile or role is the better choice. The parameter s3.iamrole is not used for the S3 target (it is used for Redshift). s3.region must be the region of the bucket.
• Calls. CreateMultipartUpload, UploadPart, CompleteMultipartUpload and AbortMultipartUpload (after a part failed three times) for files, and PutObject for files of size 0; ListBuckets only in validate database. Repstance reads, lists and deletes nothing in the bucket; the checkpoint and the staged files are local (trail/s3). The PutObject request for a file of size 0 sets the ACL private and the server-side encryption AES256; the multipart upload sets neither header.
• IAM actions. s3:PutObject and s3:AbortMultipartUpload on arn:aws:s3:::{bucket}/*, and s3:ListAllMyBuckets on * for validate database. validate database only lists the buckets: it does not look at the bucket of the parameter s3.bucketname, so a policy limited to one bucket passes the Apply Process and fails validate database. A request with an ACL can also need s3:PutObjectAcl. If the bucket is encrypted with a KMS key, the principal also needs the use of the key.
• prepare database and remove database with dbtype=s3 do nothing; only validate database runs (AWS S3 is valid). Removing an Apply Process deletes its local checkpoint and local files, never objects in the bucket.

Quick Reference