Datastream Error Postgres: Backfill Permissions Failed

[RyanSchulte]

Hello,

I am trying to create a datastream from postgres to Big Query.  I followed the instructions.  Logical decoding is enabled, replication / publication slots are there and the PG user account can run selects on the tables.

When I create the stream and validate I get all check marks except for the backfill permissions.  When I try to run the stream it says the table is not there or the user account does not have permissions.  

Everything seems to be there and there is nothing I have not tried.

Does anyone know what is going on?

Thank you

[ms4446]

Here is a through a comprehensive checklist to ensure that all necessary permissions and configurations are correctly set up:

1. Verify PostgreSQL User Permissions:

   Ensure that the PostgreSQL user configured for Datastream has the following permissions:

   • SELECT on all tables to be streamed.
   • REPLICATION privilege for creating and managing replication slots.
   • USAGE on the schema containing the tables (if the user is not the owner of the schema).
   • EXECUTE on any functions or triggers used within the tables (if applicable).

   You can verify and grant these permissions with the following SQL commands:

   GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO your_datastream_user;
   GRANT USAGE ON SCHEMA your_schema TO your_datastream_user;
   GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA your_schema TO your_datastream_user; 
   -- Only if functions/triggers are used
   GRANT REPLICATION TO your_datastream_user;
   

2. Check Logical Replication Setup:

   Ensure logical replication is enabled in your PostgreSQL configuration (postgresql.conf) :

   wal_level = logical
   max_replication_slots = [number of slots needed] 
   max_wal_senders = [number of senders needed] 
   

3. Validate Publication and Slot Configuration:

   Ensure that the publication and replication slot are correctly set up and the user has the necessary permissions:

   -- Create publication if not already done
   CREATE PUBLICATION your_publication FOR ALL TABLES;
   
   -- Verify publication
   SELECT * FROM pg_publication;
   
   -- Check replication slots
   SELECT * FROM pg_replication_slots;
   
   -- Verify that tables are in the publication:
   SELECT * FROM pg_publication_tables WHERE pubname = 'your_publication'; 
   

4. Ensure Datastream Service Account Permissions:

   Ensure the Datastream service account has the required permissions to access and manage the PostgreSQL database. This typically involves granting IAM roles for Cloud SQL if using a managed PostgreSQL instance.

5. Network Configuration:

   Ensure that there are no network issues preventing Datastream from accessing your PostgreSQL instance. Check firewall rules, VPC peering, and ensure that the Datastream IP ranges are whitelisted.

6. Verify Table Existence and Visibility:

   Ensure that the tables are present in the database and that there are no schema visibility issues. Sometimes, tables might not be visible to the user if the search path is not set correctly.

7. Review Datastream Logs and Metrics:

   Check the logs and metrics in the Google Cloud Console for Datastream. Look for any specific error messages or warnings that could provide more details on why the backfill permissions check is failing.

8. Check for PostgreSQL Extensions and Dependencies:

   Ensure that any required extensions or dependencies are installed and configured properly in your PostgreSQL instance (e.g., PostGIS for geospatial data).

Example Configuration Check:

The example code provided earlier is an excellent way to test the publication and replication slot creation.

Additional Debugging Steps:

If the issue persists, consider the following additional debugging steps:

•  Increase the verbosity of PostgreSQL logs to capture more detailed information.
•  Manually perform a backfill operation to verify permissions and ensure that Datastream can read the data.

 If all else fails, contact Google Cloud Support with detailed logs and error messages for further assistance.

[RyanSchulte]

Thank you for your response, I really appreciate it.  

Verify PostgreSQL User Permissions:

I logged into the postgres instance as the user datastream uses and the user can select all for the tables I am trying to pull.  Verified the replication slot and that the user has replication rights.  I ran the USAGE rights for the user and it was successful so that is not an issue.  For the EXECUTE, is this required?  We are not running any functions or triggers. 

Check Logical Replication Setup:

I know this works because we had CDC set up with a 3rd party and it was working.  We are using datastream as a potential replacement.

Validate Publication and Slot Configuration:

I validated the pub slot and ran the sql to show which tables it has.  Oddly, only a select number of tables were available.  I re-ran the stream with one of the tables that it listed in the pub and it still failed.

Ensure Datastream Service Account Permissions:

For the DB all the permissions were made in cloud sql.  Does datastream need other IAM permissions? 

Network Configuration:

We just went with the IP whitelisting and they are listed in the cloud sql instance.

Verify Table Existence and Visibility

Not sure what you mean by this.  Is there specific things I should look at?

Review Datastream Logs and Metrics:

There are no useful logs from cloud sql or datastream.  I did meet with google support and, when running the stream, they did not find any logs or warnings from the backend which helped.

Check for PostgreSQL Extensions and Dependencies:

As mentioned before, we used a 3rd party tool which was able to run CDC so I assume there is nothing that needs to be added.  Are there any extensions or add ons besides logical decoding that need to be added / enabled.

As mentioned I did meet with google support and they could not find what was going on.  They submitted a bug to the product team and I am waiting to hear back.

If you have any additional suggestions please let me know.  It has been close to 2 weeks I have been trying to get this running and it is something our analytics team needs.

Thank you.

[ms4446]

Please provide the case number so I can check the status internally.  

To make sure you're not missing anything , please double-check the following:

• PostgreSQL Visibility:
  • Run the following command in PostgreSQL to confirm the search_path:

    SHOW search_path;
    

  • Make sure the schemas containing your tables are included in this path.
• Recreate Publication:
  • Try dropping and recreating your publication:

    DROP PUBLICATION your_publication;
    CREATE PUBLICATION your_publication FOR ALL TABLES;
    

• User Permissions:
  • Verify the Datastream user has the correct permissions:

    GRANT USAGE ON SCHEMA your_schema TO your_datastream_user;
    GRANT SELECT ON TABLE your_schema.your_table TO your_datastream_user;
    

    (Replace your_schema and your_table with your actual values)
• Service Account IAM Roles:
  • Confirm that the service account used by Datastream has these roles:
    • roles/datastream.admin
    • roles/cloudsql.client
    • roles/bigquery.dataEditor
• Network Access:
  • Double-check your firewall rules and VPC settings to ensure there's no blockage between Datastream and your PostgreSQL instance.

Specific Checks:

• Replication Slot Activity:
  • Monitor PostgreSQL's replication slots for errors:

    SELECT * FROM pg_replication_slots;
    

• PostgreSQL Logs:
  • Increase logging verbosity for more detailed information:

    log_statement = 'all'
    log_connections = on
    log_disconnections = on
    

• Extension Validation:
  • Ensure the pgoutput extension is enabled:

    SELECT * FROM pg_available_extensions WHERE name = 'pgoutput';
    

[RyanSchulte]

For the service account for datastream, there was nothing about granting roles / permissions to the SA.  Is that something we have to do?

[ms4446]

Yes, granting the right roles and permissions to the service account used by Datastream is crucial for its proper functioning. If the service account lacks the necessary permissions, it may lead to issues such as the "Backfill Permissions Failed" error you're encountering.

Here's how to ensure that the Datastream service account has the required permissions:

1. Identify the Service Account:

• Datastream typically uses a service account for accessing resources like Cloud SQL and BigQuery. This account can be:
  • User-Defined Service Account: If you've specified a service account, you need to assign roles to this account.
  • Default Compute Engine Service Account: If no specific service account is used, Datastream might use the default one for the project.

2. Grant Required IAM Roles:

Roles Needed:

• Cloud SQL Client: Allows the service account to connect to Cloud SQL instances.
• Datastream Admin: Full control over Datastream resources.
• BigQuery Data Editor: Allows the service account to write data to BigQuery.

Assign Roles Using the Console:

1. Navigate to IAM & Admin in the Google Cloud Console.
2. Locate the service account used by Datastream.
3. Click the pencil icon to edit the roles for the service account.
4. Add the following roles:
   • Datastream Admin (roles/datastream.admin)
   • Cloud SQL Client (roles/cloudsql.client)
   • BigQuery Data Editor (roles/bigquery.dataEditor)
5. Save the changes.

Assign Roles Using gcloud:

# Replace [SERVICE_ACCOUNT] with your service account email
SERVICE_ACCOUNT=[SERVICE_ACCOUNT]

# Grant Datastream Admin
gcloud projects add-iam-policy-binding [PROJECT_ID] \
  --member=serviceAccount:$SERVICE_ACCOUNT \
  --role=roles/datastream.admin

# Grant Cloud SQL Client
gcloud projects add-iam-policy-binding [PROJECT_ID] \
  --member=serviceAccount:$SERVICE_ACCOUNT \
  --role=roles/cloudsql.client

# Grant BigQuery Data Editor
gcloud projects add-iam-policy-binding [PROJECT_ID] \
  --member=serviceAccount:$SERVICE_ACCOUNT \
  --role=roles/bigquery.dataEditor

3. Verify Network Access and Firewall Rules:

• Ensure the service account has the necessary network access.
• If using VPC Service Controls, verify that Datastream and Cloud SQL are in the same service perimeter.

4. Verify Service Account Scope and Credentials:

• If running Datastream on a custom service account, make sure it has the appropriate OAuth scopes for Cloud SQL and BigQuery.

5. Check Datastream Configuration:

• Ensure the Datastream configuration references the correct service account and that it has the necessary permissions.

Troubleshooting Steps:

• Test with Default Permissions: Try using the default Compute Engine service account for basic operations.
• Logs and Diagnostics: Check Google Cloud logs for specific permission-related errors.

[lorenh]

@RyanSchulte were you ever able to resolve this issue? running into the same.

[Snoshone07]

Hi @RyanSchulte it seems like you’ve covered most of the requirements for configuring a Datastream between PostgreSQL and BigQuery. However, the issue with backfill permissions indicates that some table-level or user-level settings might need extra attention. Let’s go through the steps to resolve it:

1. Validate PostgreSQL Configuration

Ensure PostgreSQL Settings Are Properly Configured

• Enable Logical Decoding
  Confirm that logical decoding is enabled in your PostgreSQL server by checking the postgresql.conf file. Make sure the following settings are in place:

  wal_level = logical max_replication_slots = 4 # Adjust if needed max_wal_senders = 4

  After updating these settings, restart PostgreSQL.

• Replication Slot and Publication
  Ensure the replication slot and publication include the tables you need:

  • For all tables:
    CREATE PUBLICATION my_publication FOR ALL TABLES;
  • For specific tables:
    CREATE PUBLICATION my_publication FOR TABLE table1, table2;

Verify User Permissions

Make sure the PostgreSQL user that Datastream uses has the necessary privileges:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_user;

2. Check Datastream Backfill Configuration

Backfill Permissions

• Ensure the user has SELECT permissions for all the tables included in the publication.
• If you’re using FOR ALL TABLES, newly added tables will automatically be included in the publication, ensuring future-proof configurations.

Handle Schema Changes

• If schema changes were made after creating the stream, Datastream might not detect them. You’ll need to recreate the publication or resync the stream to incorporate the changes.

3. Troubleshoot the "Table Not Found" Error

Verify Table Visibility

• Confirm that the target tables exist in the schema Datastream expects. If the default schema isn’t public, explicitly set it in your publication:
  CREATE PUBLICATION my_publication FOR TABLE schema_name.table_name;

Check Network Connectivity

• Make sure the Datastream service has access to your PostgreSQL instance. Verify firewall settings and confirm that the appropriate IP ranges are whitelisted.

If the issue persists, you might want to explore third-party tools for data replication. These tools often simplify the setup process and handle errors more effectively.

For instance, Windsor.ai provides connectors for PostgreSQL and BigQuery, making data transfer seamless without requiring complex configurations. This could be a time-saving alternative to Google Datastream.

[lorenh]

@RyanSchulte for reference, we were able to get this working when we explicitly granted SELECT permissions to the Postgres user we set up forDatastream; for some reason Datastream did not recognize that it had permissions when granted via a role.