I'm using Airbyte to sync data into a Postgres server. I want to use a dedicated Postgres user for this purpose, so that it can't read or write on anything else outside of its intendend scope.
All the data synced by Airbyte will come into a single database, but in multiple schemas. So, if Airbyte fetches data from databases Alice, Bob and Charlie, the schemas in the target Postgres database will be sync_alice, sync_bob and sync_charlie.
I'm facing issues with creating the right GRANT statements for the airbyte_user that I would like Airbyte to use when accessing the Postgres server. What I would like to do is to provide this user with:
- The ability to create new schemas.
- The ability to create, select, insert, update and delete on tables ONLY in schemas that follow the naming pattern
sync_<whatever>. - For clarity, I don't want the user to be able to read or modify anything outside of schemas that don't follow the
sync_<whatever>convention.
I'm having a hard time finding a way to use some kind of wildcard in the GRANT statements. I was hoping something like this would work:
GRANT CREATE, TEMPORARY ON DATABASE postgres TO airbyte_user;
GRANT CREATE, SELECT, INSERT, UPDATE, DELETE ON SCHEMA 'sync_*' TO airbyte_user;
But postgres complains on the second statement syntax.
Is there anyway to achieve what I want, or am I doomed to only give my airbyte_user the permissions on the different sync_<whatever> schemas one by one as I create them?
Thanks