First post here, woop! Been using streamlit for a project of mine for the past 6 months, and I’m loving how active the community is and all the new features that keep coming!
To my question - How can I provide a secret file to my deployed streamlit app on community cloud? I know I can provide secrets in .toml format and have done so for some credentials, however I’m trying to connect to a SQL Server database hosted in Google Cloud SQL and would like to enable SSL authentication connection for that database. This means that, according to google’s provided example, I need to specify a path to my servers CA.pem file (which I have on my PC at home). Is there any way to provide a secret file to my webapp in addition to the currently implemented secrets feature? I’ve copied the current code for the connection implementation I’m using below, which works for now as I don’t have SSL enabled on the remote database. I would like to change this going forward, if we were to move to a more permanent solution.
from google.cloud.sql.connector import Connector, IPTypes
import pytds
import sqlalchemy
import streamlit as st
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
def create_cloud_database_connection() -> sqlalchemy.engine.base.Engine:
"""
Initializes a connection pool for a Cloud SQL instance of SQL Server.
Uses the Cloud SQL Python Connector package.
"""
# Note: Saving credentials in environment variables is convenient, but not
# secure - consider a more secure solution such as
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
# keep secrets safe.
instance_connection_name = st.secrets.DB_CREDENTIALS.INSTANCE_CONNECTION_NAME # e.g. 'project:region:instance'
instance_ip_address = st.secrets.DB_CREDENTIALS.IP_ADDRESS
db_user = st.secrets.DB_CREDENTIALS.DB_USERNAME # e.g. 'my-db-user'
db_pass = st.secrets.DB_CREDENTIALS.DB_PASSWORD # e.g. 'my-db-password'
db_name = st.secrets.DB_CREDENTIALS.DB_NAME # e.g. 'my-database'
ip_type = IPTypes.PRIVATE if "PRIVATE_IP" in st.secrets else IPTypes.PUBLIC
connector = Connector(ip_type)
connect_args = {}
# If your SQL Server instance requires SSL, you need to download the CA
# certificate for your instance and include cafile={path to downloaded
# certificate} and validate_host=False. This is a workaround for a known issue.
if "DB_ROOT_CERT" in st.secrets: # e.g. '/path/to/my/server-ca.pem'
connect_args = {
"cafile" : st.secrets.DB_ROOT_CERT,
"validate_host": False,
}
def getconn() -> pytds.Connection:
conn = pytds.connect(
instance_ip_address,
user=db_user,
password=db_pass,
database=db_name,
**connect_args
)
return conn
pool = sqlalchemy.create_engine(
"mssql+pytds://",
creator=getconn,
poolclass=NullPool
)
return pool
