Destination Postgres: Wrong permission when Full refresh | Overwrite

[isavita]

Environment

• Airbyte version: 0.40.26
• OS Version / Instance: GCP self hosted
• Deployment: Kubernetes deploy
• Source Connector and version: BigQuery 0.2.3
• Destination Connector and version: Postgres 1.0.34
• Step where error happened: Every time when we do sync with Full refresh | Overwrite. With name space in destination public.

Current Behavior

I am using full refresh with overwrite to copy a table from BigQuery to Postgresql Every time I do sync I am getting no permission to the table this is how my table with permissions looks after the full refres.

 SELECT relname, relacl FROM pg_class WHERE relname = 'my_table';
        relname        | relacl
-----------------------+--------
 my_table |

Once I manually give permission to my_user to read all tables

 SELECT relname, relacl FROM pg_class WHERE relname = 'my_table';
        relname        |                                      relacl
-----------------------+-----------------------------------------------------------------------------------
 my_table | {airbyteuser=arwdDxt/airbyteuser,"\"my_user\"=r/airbyteuser"}
(1 row)

I can see the table, but ONCE I do sync again the story repeats.

Expected Behaviour

I would expect after full refresh all users that had read access to be able to see the table still. Instead I am getting access errors and need manually to grand them access each time.

Logs

I can provide logs if needed

Steps to Reproduce

1. Create a table in BigQuery and use it as source

2. Create destination with Postgresql and use specific user for airbyte that has all writes read, write etc.

3. Create connection with following setup Full refresh | Overwrite from BigQuery to Postgresql.

   

4. Run sync.

5. Create another user in postgres and give all read access to the table with.

   GRANT SELECT ON ALL TABLES IN SCHEMA public TO "user2";

6. Do sync again from the Airbyte UI.

7. Try to read from the original table with the user2 or/and check the permission with following sql.

   SELECT relname, relacl FROM pg_class WHERE relname = 'my_table';

Are you willing to submit a PR?

I am not sure how can I fix destination behaviour if someone point me to the code for the postgresql destination, I might give a try.

[OhadSrur]

I think this is expected behaviour because the AirByte process drops and recreates the table. What I do is I run a shell script that grants the required access to my account. IMO, it would be good if the Connection settings would be extended to allow the end user to enter raw SQL commands that would be executed at the end of the process. This way, we could enter the Grant statement.

[evantahler]

Closing as the destination drops and recreates tables as needed to have 0-data-downtime