Improve dev experience for creating Streamlit apps in Snowflake

[ian-r-rose]

Our current RBAC structure doesn't work very well for developing streamlit apps based on data in the prod marts database (ANALYTICS_PRD):

1. Streamlit apps are database objects that need to be created in a specific place with a specific role
2. When developing a streamlit app, it makes most sense to use the ANALYTICS_DEV database and REPORTER_DEV role.
3. But that role doesn't have access to the prod data
4. Normally when using external BI tools, people can connect using the REPORTER_PRD role. This is pretty safe, since it only has read-only permissions on that data.
5. But if they enable the TRANSFORMER_PRD role, they have to create the streamlit app in the ANALYTICS_PRD database. This isn't particularly satisfying as a developer to create test objects in prod.

There are a few things we might want to consider:

1. Are there any tweaks to the RBAC structure that would make this work better?
2. Can we solve this with documentation? Perhaps the best way is to just have the user build the appropriate marts in ANALYTICS_DEV and develop against that. With appropriate dbt deferral, it needn't be super onerous to do that.
3. Can we change the execution role of a streamlit app after it is created?

[jkarpen]

It seems like the ideal scenario is they develop in the dev environment/role, then switch to the prod environment/role once the app is production-ready. I did a quick scan of the documentation here and don't see a way to change the associated role once the app is created. But I'm wondering if the developer could simply copy the SQL used by the app and create a new version with the production role/environment when they are done with development?

https://docs.snowflake.com/en/developer-guide/streamlit/owners-rights

Another thought - maybe this is worth scheduling time with Gabe to discuss?

[ian-r-rose]

Yeah, I might discuss this with Gabe the next time we meet. What I'm worried about is that in order to create the app, we would need the transformer role (the reporter role can't create objects). But then the transformer role is used when running the app, and I'd rather have a role with read-only permissions used for running the app.

[jkarpen]

Next step on this will be to have a dedicated call on this topic including the Caltrans team.

[ram-kishore-odi]

Hi @jkarpen - Can you please let me know if you plan to setup a meeting with CalTrans for this based on your last comment ?

Hi @ian-r-rose - Could you please review the following proposed next steps and let me know if you approve? Additionally, if you've met with Gabe, please share any relevant information from that discussion.

The main issue we have is that the current RBAC hinders Streamlit app development using production data while maintaining separation of dev/prod environments.

Next steps ?:

1. Are there any possible tweaks to the RBAC structure ? :
   • Yes, Create a STREAMLIT_DEV role.
   • Implementation: Grant STREAMLIT_DEV CREATE APPLICATION on ANALYTICS_DEV and SELECT on required tables in ANALYTICS_PRD.
   • Rationale: Enforces least privilege, allowing development in ANALYTICS_DEV with necessary prod data access.
2. Can we solve this with documentation?:
   • Yes, documenting dbt deferral for replicating necessary marts in ANALYTICS_DEV is going to be helpful
   • Implementation: Create detailed, version-controlled documentation with specific dbt commands and configurations.
   • Rationale: Provides a clear, repeatable process for developers, minimizing production impact.
3. Change Execution Role ? :
   • No, Snowflake Streamlit app roles cannot be changed post-creation.
   • Workaround: Copy the app's SQL and recreate it in ANALYTICS_PRD with REPORTER_PRD (as suggested by Josh in his comments)
   • Rationale: Aligns with Snowflake's documented behavior for role management.

[jkarpen]

Added Ram to 3/20 biweekly checkin with Caltrans to discuss this further.

[ian-r-rose]

Thanks for writing up this proposal @ram-kishore-odi. A bit of feedback on the next steps:

1. **Are there any possible tweaks to the RBAC structure ? :**
   
   * Yes, Create a `STREAMLIT_DEV` role.

I was hoping to avoid a new role, but I take your point that this is enough of a distinct function from REPORTER_DEV that it might merit a new role. We'd also need prod versions of these, right?

   * Implementation: Grant `STREAMLIT_DEV` `CREATE APPLICATION` on `ANALYTICS_DEV` and `SELECT` on required tables in `ANALYTICS_PRD`.
   * Rationale: Enforces least privilege, allowing development in `ANALYTICS_DEV` with necessary prod data access.

This sounds reasonable, though I think the specific permissions needed to create streamlit apps is different from this

2. **Can we solve this with documentation?:**
   
   * Yes, documenting dbt deferral for replicating necessary marts in `ANALYTICS_DEV` is going to be helpful

Yes, dbt deferral can be helpful for creating marts in ANALYTICS_DEV. In fact, if we recommend users do that, we may not need to grant the streamlit role read permissions on ANALYTICS_PRD at all! I recommend we choose one of the two approaches as the recommended path for streamlit development.

   * Implementation: Create detailed, version-controlled documentation with specific dbt commands and configurations.
   * Rationale: Provides a clear, repeatable process for developers, minimizing production impact.

3. **Change Execution Role ? :**
   
   * No, Snowflake Streamlit app roles cannot be changed post-creation.
   * Workaround: Copy the app's SQL and recreate it in `ANALYTICS_PRD` with `REPORTER_PRD` (as suggested by Josh in his comments)
   * Rationale: Aligns with Snowflake's documented behavior for role management.

I don't think we can create it with REPORTER_PRD, at least not how the permissions are currently structured: REPORTER_PRD doesn't have the permissions to create stages or streamlit apps. As I see it, we could either give the reporter roles the permissions to create streamlit apps, or we could create a new streamlit role with the appropriate permissions.

[ian-r-rose]

Notes from discussion with @ram-kishore-odi

1. We give REPORTER_{ENV} permissions to create a streamlit app (CREATE STREAMLIT and CREATE STAGE)
2. We give REPORTER_DEV permissions to select data from ANALYTICS_PRD (specifically grant ANALYTICS_PRD_READ)
3. We don't need to create a new role at this time

[ram-kishore-odi]

Thank for the notes @ian-r-rose !
The grants based on the notes are listed below. I am using the PUBLIC schema for the create grants.

-- DEV Environment Grants
GRANT CREATE STREAMLIT ON SCHEMA PUBLIC TO ROLE REPORTER_DEV;
GRANT CREATE STAGE ON SCHEMA PUBLIC TO ROLE REPORTER_DEV;
GRANT ANALYTICS_PRD_READ ON DATABASE ANALYTICS_PRD TO ROLE REPORTER_DEV;

-- PRD Environment Grants
GRANT CREATE STREAMLIT ON SCHEMA PUBLIC TO ROLE REPORTER_PRD;
GRANT CREATE STAGE ON SCHEMA PUBLIC TO ROLE REPORTER_PRD;