Single-source data warehouse permissions management

Question

We (Data Platform team) are reviewing how we configure and apply permissions against our data warehouse objects, and I'm curious what tools or custom systems you might be using for this.

For context our warehouse is AWS Redshift, and tools that manage db objects include both self-hosted tools with ability to hook into the execution flow and perform arbitrary work such as dbt + dagster, as well as managed/serverless tools over which we have limited control on the execution flow and logic such as Fivetran.

Our current core issue is the variety of ways permissions can be applied (direct GRANT 1-off migrations, alter default privileges, scripts that blankly re-apply grants on a fixed schedule, tools like dbt that can be configured to apply grants immediately on table (re)creation..), which generate a lot of confusion as to where and how grants need to be applied, and how to actually apply them. The end result is a permissions model where it's really complex to understand intent, users and models that on/off fail to gain access to objects they should, misconfigured pipelines, etc.

We are looking for a more sane way to configure and apply our permissions. In my mind an ideal system in a nutshell would include:

Single source of truth for defining permissions intent over all db objects (IaaC style, terraform?)

One off:

2.a. Service does continuous reconciliation of permission intent vs current state (think k8s state reconciliation)

2.b. Permission applied/revoked on deployment (think terraform) + allow systems to read the intent (eg. sql tables) and decide how and when to re-apply permissions

With such a setup, you'd effectively be able to point end-users at a single place and system to describe permissions intent (and look it up if/when there are issues), and enforcing grants it's either something noone needs to worry about (reconcile service) or it is system owners' responsibility to integrate with the source of truth to apply permissions.

I have not come across such a tool in the wild, but we would much prefer to take on / pay for some industry standard solution than build our own.

How do you yourselves handle the messy world of SQL GRANTs management?

Answer 1

Just a Summary of discussion answer

The problem is quite specific to redshift in that tables need to be copied/recreated inorder to manage indexing and volume. This recreation is often managed by third party ingestion tools.

"normally" you would put your database schema into source control and deploy via

1. Change table in source control
2. Change permissions by role in source control
3. Build and test database/migration script
4. Run migration
5. new table with role based permissions is deployed
6. User permissions set to roles by dba/security are unaffected

However postgres source control tools are a bit limited in this regard and you may be forced to write manual scripts to apply the roles to tables after each deployment.

This can be simplified, by having a small set of basic roles. ie Read, Write, Admin and a script which loops through all the tables in a schema adding the permissions by role.

Obviously if you have a long complicated role set, this can also be done in the same way. But the mapping script will be ugly with hardcoded table and role relationships.

In your example with dbt, dbt does reapply the roles, so if you stick to a single tool you should be good. If you have a random combination of tools AND want to keep the permissions central it seems to me that you will have to be willing to give up on some of the tools features.