Schema and Table Authorization

[0814celsus]

Hello,
first of all thank you so much for your work. I am very exited about this new Project. It's really amazing that it is possible to build up a working 🦆lake with s3 encrypted storage and pg catalog within a few hours of time. Amazing! The only feature that I am missing so far is the possibility to manage schema or table access grants to different catalog users. Is this planned in the future? pg piggyback is mentioned in the Documentation but I have not found anything in detail so far.

[dkrieger]

as far as I've been able to tell, out-of-the-box, the only way to accomplish multiple access levels is to have multiple lakes. This is somewhat mitigated by the relative ease of setting up "frozen" (read-only) ducklakes, but IMO that is not good enough if you truly need both access controls and full data lineage. The ability to provide commit messages for snapshots can be used to mitigate the loss of data lineage when copying from one lake to another, however.

Different policies could be placed on the different schemas' data paths, i.e. subdirectories in a single bucket, but this would be more universally viable (across s3-compatible object storage providers) if different schemas could use entirely different buckets.

[0814celsus]

I didn't see it in the first place, but there is a table called ducklake_column_tag, So I used that table in conjuncion with a security lookup table in a schema (__my_security_schema) where I defined user roles, clearance levels and so on for users. In the tag column I was able to store key value pairs for the relevant columns. I enabled row level security on the ducklake_column table and added a bunch of Policies which are filtering down the columns(rows in ducklake_column) a user might or might not see. This is, I need to say a very elegant way to push security down to the catalog database (eg. Postgres). And it works perfectly well, when I connect different user to the ducklake depending on department, role, and clearance they will only see tables and columns according to privileges defined for them in the security lookup table! Thank you so much for ducklake, it's so elegant I love it! 🚀

[adityawarmanfw]

Hi, sounds like awesome workaround. If you have the time, would you be willing to share a sanitized snippet of how you set this up?

[berndsen-io]

I made an example as well, if you still need something: https://github.com/berndsen-io/ducklake-guard

Intend to make it a more friendly CLI (or DuckDB extension) in the future, but the info and included tests should show you the way.

[pratikshetty1994]

Wow. I was looking for something like this.