I'm using Google Cloud Datastream to replicate some tables from Postgresql into BigQuery. Setting up the source connection and destination worked well and passed the validations, but when I almost end up setting the stream, it fails on the Validation step.
After doing some investigations, I could see it is because this validation runs the following query to check if the user has the SELECT grant in the table to be backfilled:
SELECT table_schema, table_name FROM information_schema.table_privileges WHERE grantee = current_user AND privilege_type = 'SELECT';
The problem is that it tries to match direct user (current_user) and granting SELECT to a table directly for a user is not recommended. That's why I set readaccess role to my user and then enabled this readaccess for the table.
But the previous query won't return data for that table and validation will fail.
Do you know if there's a workaround for passing the validation this way through UI? (I don't want to set this user as superuser neither). I'm aware I can use Rest API to skip validation but it's not what I'm looking for.
Regards