Streamlit and role grants

[kokorin]

In one of our PoC we wanted to use SnowDDL to create a DB and schema to create Streamlit app.
Streamlit app itself will be deployed outside of SnowDDL so we don't need to have StreamlitBlueprint in this project.

First, I have to describe the problem: Snowflake doesn't support future grants on Streamlit, but it can grant privileges on existing ones.

We want to use SnowDDL to grant USAGE privilege on Streamlit to reader role. For that we even added STREAMLIT object type into SnowDDL (by modifying py-files in venv) like this:

class ObjectType(Enum):
    # ...
    STREAMLIT = {
        "singular": "STREAMLIT",
        "plural": "STREAMLITS",
    }
    # ...

And then added Streamlit to Permission Model:

out_model:
  inherit_from: default
  ruleset: SCHEMA_OWNER
  owner_create_grants:
    - STREAMLIT
  read_future_grants:
    STREAMLIT: [USAGE] 

Unfortunately, snowddl plan suggests a query to remove grants on existing Streamlit app from a Reader Role (we granted it manually).

Then we added is_future_grant_supported to Streamlit:

class ObjectType(Enum):
    # ...
    STREAMLIT = {
        "singular": "STREAMLIT",
        "plural": "STREAMLITS",
        "is_future_grant_supported": True,
    }
    # ...

After that SnowDDL doesn't suggest to revoke permissions on Streamlit, but it instead suggests to grant permissions on future Streamlits, which is not possible in Snowflake ATM.

As a workaround for the PoC we decided to create a dedicated DB and use DB Owner Role to create/update Streamlit. A Business Role (which is granted with Owner Role) is granted with Read Role on another DB where actual data is stored, so for now it's enough for use to make sure that a BR can't occasionally modify data but can modify Streamlit.

Now the question: is it possible to Streamlit object type to SnowDDL with support of grants ON ALL SRTEAMLITS but without ON FUTURE STREAMLITS.

My idea is to add another property to ObjectType to control such behavior:

class ObjectType(Enum):
    # ...
    STREAMLIT = {
        "singular": "STREAMLIT",
        "plural": "STREAMLITS",
        "is_all_grant_supported": True,
        "is_future_grant_supported": False,
    }
    # ...
    @property
    def is_all_grant_supported(self) -> bool:
        return self.value.get("is_all_grant_supported", self.is_future_grant_supported) 

[littleK0i]

In theory, global roles can be used to cover almost any edge case.

Since Streamlit app is managed outside of SnowDDL, it might be ok to also maintain a global role with grants for all streamlits. This global role can be granted to schema owner role or business role.

Btw, I also heard a lot of other negatives about Snowflake native Streamlit implementation. Lack of future grants is not the only problem. As far as I know, it is also outdated in terms of version and overpriced, because it requires a warehouse to run.

Self-hosting might be easier than trying to find work-arounds for Snowflake laziness. Also, there are lots of better products on the market, e.g. Hex.

[littleK0i]

Consider an option with Streamlit running in container: https://www.reddit.com/r/snowflake/comments/1d5kwl5/why_streamlit_in_native_apps_is_so_old_and_has_so/

Maybe it can help to alleviate most of known problems, including a problem with unfinished implementation of permissions.

[DoMy91]

It seems that Snowflake now supports future grants on Streamlits: https://docs.snowflake.com/en/release-notes/streamlit-in-snowflake#january-31-2025-support-for-future-grants-in-sis-general-availability

[littleK0i]

Does not seem to work for ownership, at least not on my test account.

(
    message   =>  Unsupported feature GRANT/REVOKE OWNERSHIP ON STREAMLIT'.
    errno     =>  391811
    sqlstate  =>  0A000
    sfqid     =>  01ba35ca-010c-25eb-0002-30d301b997fa
    sql       =>  GRANT OWNERSHIP ON FUTURE STREAMLITS IN SCHEMA "PYTEST__STREAMLIT_DB1"."STREAMLIT_SC1" TO ROLE "PYTEST__STREAMLIT_DB1__STREAMLIT_SC1__OWNER__S_ROLE"
)