> ## Documentation Index
> Fetch the complete documentation index at: https://www.integrate.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# Google Cloud SQL PostgreSQL source for ELT & CDC
> How to configure Google Cloud SQL for PostgreSQL as a CDC source in Integrate.io ELT & CDC, including logical decoding and role setup.
### Enable Logical Replication
Go to Google Cloud Console and select the project that contains the Cloud SQL instance for which you want to set a database flag.
Edit the instance and go to the Flags section. Add item > `cloudsql.logical_decoding` and set to `ON` then save your changes
This change will require your instance to restart
Confirm your changes under Flags on the Overview page or by checking the replication status,
```bash theme={null}
show cloudsql.logical_decoding;
```
### Create role for sync
Create a sync user for ELT & CDC by executing,
```bash theme={null}
CREATE ROLE integrateio WITH PASSWORD '' LOGIN;
```
Assign the replication role to the user:
```bash theme={null}
ALTER ROLE integrateio WITH REPLICATION;
```
### Grant necessary privileges
Grant the privileges for the database and schema by running the following queries,
```bash theme={null}
GRANT CREATE ON SCHEMA TO integrateio;
GRANT USAGE ON SCHEMA TO integrateio;
GRANT CREATE ON DATABASE TO integrateio;
GRANT SELECT ON ALL TABLES IN SCHEMA TO integrateio;
GRANT REFERENCES ON ALL TABLES IN SCHEMA TO integrateio;
```
### Assign table ownership
Create a replication group role which will allow for shared ownership of the tables by the original owner as well as the `integrateio` user.
```bash theme={null}
CREATE ROLE replication_group;
```
Add `integrateio` user to `replication_group`.
```bash theme={null}
GRANT replication_group to integrateio;
```
Then, create the following function which adds the existing table owners in a given schema to the `replication_group` role. This lets the original users **retain ownership of the tables**.
```sql expandable theme={null}
CREATE OR REPLACE FUNCTION public.add_existing_owners_to_replication_group(
newowner text,
pschem text)
RETURNS TABLE
(
added_users text
)
AS
$BODY$
DECLARE
tblnames CURSOR FOR
SELECT DISTINCT tableowner
FROM pg_tables
WHERE schemaname = pschem
AND tableowner <> newowner;
is_already_member boolean;
added_users text[] := array []::text[];
BEGIN
FOR stmt IN tblnames
LOOP
EXECUTE $$SELECT * from pg_has_role('$$ || stmt.tableowner || $$', '$$ || newowner ||
$$', 'member');$$ INTO is_already_member;
IF NOT is_already_member THEN
EXECUTE 'GRANT ' || newowner || ' TO ' || stmt.tableowner || ';';
EXECUTE 'ALTER DEFAULT PRIVILEGES FOR ROLE ' || stmt.tableowner || ' IN SCHEMA ' || pschem ||
' GRANT SELECT ON TABLES TO ' || newowner || ';';
EXECUTE 'ALTER DEFAULT PRIVILEGES FOR ROLE ' || stmt.tableowner || ' IN SCHEMA ' || pschem ||
' GRANT REFERENCES ON TABLES TO ' || newowner || ';';
added_users = array_append(added_users, stmt.tableowner::text);
END IF;
END LOOP;
RETURN QUERY SELECT unnest(added_users::text[]);
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
```
Execute the function,
```sql theme={null}
SELECT public.add_existing_owners_to_replication_group('replication_group', '');
```
Now create the function which will change the owners of all tables in a given schema to `replication_group`,
```sql expandable theme={null}
CREATE OR REPLACE FUNCTION public.change_schema_tables_owner_to_replication_group(
newowner text,
pschem text, numtables int default 999999)
RETURNS TABLE
(
changed_tables text
)
AS
$BODY$
DECLARE
changed_tables text[] := array []::text[];
tblnames CURSOR FOR
SELECT tablename
FROM pg_tables
WHERE schemaname = pschem
and tableowner <> newowner
limit numtables;
BEGIN
FOR stmt IN tblnames
LOOP
EXECUTE 'alter table ' || pschem || '.' || stmt.tablename || ' owner to ' || newowner || ';';
changed_tables = array_append(changed_tables, stmt.tablename::text);
END LOOP;
RETURN QUERY SELECT unnest(changed_tables::text[]);
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
```
The function accepts an optional third parameter which specifies the number of tables to change ownership of. You can use this parameter to change ownership in batches if the number of tables is very high.
Execute the function,
```sql theme={null}
SELECT public.change_schema_tables_owner_to_replication_group('replication_group', '');
```
Or batch it (10 tables in the example below),
```sql theme={null}
SELECT public.change_schema_tables_owner_to_replication_group('replication_group', '', 10);
```