Configure Connections to PostgreSQL

Learn about user privileges required on the database side to connect to Oracle GoldenGate and configuration instructions to set up connections for PostgreSQL.

Grant 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;

To capture data from PostgreSQL standby database specifically, you must have either the SUPERUSER privilege or the pg_read_all_stats privilege, along with the other required privileges to capture data from PostgreSQL database.