Skip schema-level grants when database-level grants exist

Describe the feature

Right now, Permifrost runs schema-/table-level grant statements even when database-level grants render them inconsequential. This is best illustrated by an example.

Suppose we had this spec on a fresh Snowflake instance:

    - db_database_1_r:
        owner: useradmin
        privileges:
            databases:
                read:
                    - database_1
            schemas:
                read:
                    - database_1.*
            tables:
                read:
                    - database_1.*.*

where database_1 contains one schema, schema_1, which contains one table, table_1.

If we execute Permifrost, we would run the following statements:

GRANT usage ON database database_1 TO ROLE db_database_1_r;
GRANT usage ON FUTURE schemas IN database database_1 TO ROLE db_database_1_r;
GRANT usage ON schema database_1.schema_1 TO ROLE db_database_1_r;
GRANT select ON FUTURE tables IN database database_1 TO ROLE db_database_1_r;
GRANT select ON ALL tables IN database database_1 TO ROLE db_database_1_r;
GRANT select ON FUTURE views IN database database_1 TO ROLE db_database_1_r;
GRANT select ON ALL views IN database database_1 TO ROLE db_database_1_r;
GRANT select ON FUTURE tables IN schema database_1.schema_1 TO ROLE db_database_1_r;
GRANT select ON ALL tables IN schema database_1.schema_1 TO ROLE db_database_1_r;
GRANT select ON FUTURE views IN schema database_1.schema_1 TO ROLE db_database_1_r;
GRANT select ON ALL views IN schema database_1.schema_1 TO ROLE db_database_1_r;

The final four schema-level grant statements aren't needed, since the previous 4 statements accomplish the same thing at the database level.

If we create a new table database_1.schema_1.table_2 and run Permifrost, no new grant statements are generated, as expected.

If we create a new schema with a new table, database_1.schema_2.table_1, the future grants on the database mean that role db_database_1_r is given select privileges on the table on creation. But if we run Permifrost, the following grant statements are generated:

GRANT select ON FUTURE tables IN schema database_1.schema_2 TO ROLE db_database_1_r;
GRANT select ON ALL tables IN schema database_1.schema_2 TO ROLE db_database_1_r;
GRANT select ON FUTURE views IN schema database_1.schema_2 TO ROLE db_database_1_r;
GRANT select ON ALL views IN schema database_1.schema_2 TO ROLE db_database_1_r;

These are no-ops as well, as they are superseded by the database level grants.

With this change, the first run would be limited to the first 7 lines, and the second run would run no statements.

Additional context

I'm curious if this behavior is intentional for one reason or another - is there actually a need for these schema-level grants / are they doing something that I'm missing? Would be interested in some validation on this point. I do know that on the write side we would need to make sure GRANT {write_privileges} ON ALL schemas IN database {db} TO ROLE {role};, as it looks like right now we don't run this and instead rely on granting on each schema individually.

Who will this benefit?

Teams with large projects where performance and clear diffs are priorities. The existing functionality can add a lot of lines to any given Permifrost run, making it difficult to see from a dry run what statements are actually modifying permissions versus which are generated by the behavior described here. It can also slow down the execution time of a Permifrost run by quite a bit when there are so many extra statements to go through. This particularly becomes an issue in databases where schemas might be recreated regularly.

Are you interested in contributing this feature?

Yep!