Prepare Database Users and Privileges for PostgreSQL

Learn about creating database users and assigning privileges for Oracle GoldenGate for PostgreSQL.

Oracle GoldenGate processes require a database user to capture and deliver data to a PostgreSQL database and it is recommended to create a dedicated PostgreSQL database user for Extract and Replicat.

The following database user privileges are required for Oracle GoldenGate to capture from and apply to a PostgreSQL database.

Privilege	Extract	Replicat	Purpose
Database Replication Privileges
CONNECT	Yes	Yes	Required for database connectivity. GRANT CONNECT ON DATABASE dbname TO gguser;
WITH REPLICATION	Yes	NA	Required for the user to register Extract with a replication slot. ALTER USER gguser WITH REPLICATION;
WITH SUPERUSER	Yes	NA	Required to enable table level supplemental logging (ADD TRANDATA) but can be revoked after TRANDATA is enabled for the table(s). ALTER USER gguser WITH SUPERUSER; For Azure Database for PostgreSQL, only the Admin user has SUPERUSER authority and is the only user that can enable TRANDATA.
USAGE ON SCHEMA	Yes	Yes	For metadata access to tables in the schema to be replicated. GRANT USAGE ON SCHEMA tableschema TO gguser;
SELECT ON TABLES	Yes	Yes	Grant select access on tables to be replicated. GRANT SELECT ON ALL TABLES IN SCHEMA tableschema TO gguser;
INSERT, UPDATE, DELETE,TRUNCATE on target tables. Alternatively, if replicating every table, then you can use the GRANT INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA TO... to the Replicat user, instead of granting INSERT, UPDATE, DELETE to every table.	NA	Yes	Apply replicated DML to target objects. GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE tablename TO gguser;
Heartbeat and Checkpoint Table Privileges
CREATE ON DATABASE	Yes	Yes	Required by the Extract and Replicat user to add an Oracle GoldenGate schema for heartbeat and checkpoint table creation. GRANT CREATE ON DATABASE dbname TO gguser; Alternatively, if GGSCHEMA is the same as the user, then the objects can be created under the user by issuing CREATE SCHEMA AUTHORIZATION ggsuser;
CREATE, USAGE ON SCHEMA	Yes	Yes	For heartbeat and checkpoint table creation/deletion if the Extract or Replicat user does not own the objects. GRANT CREATE, USAGE ON SCHEMA ggschema TO gguser;
EXECUTE ON ALL FUNCTIONS	Yes	Yes	For heartbeat update and purge function execution if the user calling the functions does not own the objects. GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ggschema TO gguser;
SELECT, INSERT, UPDATE, DELETE	Yes	Yes	For heartbeat and checkpoint table inserts, updates and deletes if the user does not own the objects. GRANT SELECT, INSERT, UPDATE, DELETE, ON ALL TABLES IN SCHEMA ggschema TO gguser;