Provide a hidden file to deployed app - CA certificate for SSL authentication to a remote database

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

Hi @Hazzles, welcome to the forum! :wave: :balloon:

Have you tried writing the .pem file from secrets to a temp file using NamedTemporaryFile?

Something like:

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'
    with NamedTemporaryFile(suffix=".pem", delete=False) as tempfile:
        tempfile.write(st.secrets.DB_ROOT_CERT.encode("utf-8"))
        connect_args = {
            "cafile": tempfile.name,
            "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

and where you paste the contents of your .pem file Community Cloud’s secrets management console, as, for example:

# .streamlit/secrets.toml

DB_ROOT_CERT = """
-----BEGIN CERTIFICATE-----
MIIEczCCA1ugAwIBAgIBADANBgkqhkiG9w0BAQQFAD..AkGA1UEBhMCR0Ix
EzARBgNVBAgTClNvbWUtU3RhdGUxFDASBgNVBAoTC0..0EgTHRkMTcwNQYD
VQQLEy5DbGFzcyAxIFB1YmxpYyBQcmltYXJ5IENlcn..XRpb24gQXV0aG9y
aXR5MRQwEgYDVQQDEwtCZXN0IENBIEx0ZDAeFw0wMD..TUwMTZaFw0wMTAy
MDQxOTUwMTZaMIGHMQswCQYDVQQGEwJHQjETMBEGA1..29tZS1TdGF0ZTEU
MBIGA1UEChMLQmVzdCBDQSBMdGQxNzA1BgNVBAsTLk..DEgUHVibGljIFBy
aW1hcnkgQ2VydGlmaWNhdGlvbiBBdXRob3JpdHkxFD..AMTC0Jlc3QgQ0Eg
THRkMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCg..Tz2mr7SZiAMfQyu
vBjM9OiJjRazXBZ1BjP5CE/Wm/Rr500PRK+Lh9x5eJ../ANBE0sTK0ZsDGM
ak2m1g7oruI3dY3VHqIxFTz0Ta1d+NAjwnLe4nOb7/..k05ShhBrJGBKKxb
8n104o/5p8HAsZPdzbFMIyNjJzBM2o5y5A13wiLitE..fyYkQzaxCw0Awzl
kVHiIyCuaF4wj571pSzkv6sv+4IDMbT/XpCo8L6wTa..sh+etLD6FtTjYbb
rvZ8RQM1tlKdoMHg2qxraAV++HNBYmNWs0duEdjUbJ..XI9TtnS4o1Ckj7P
OfljiQIDAQABo4HnMIHkMB0GA1UdDgQWBBQ8urMCRL..5AkIp9NJHJw5TCB
tAYDVR0jBIGsMIGpgBQ8urMCRLYYMHUKU5AkIp9NJH..aSBijCBhzELMAkG
A1UEBhMCR0IxEzARBgNVBAgTClNvbWUtU3RhdGUxFD..AoTC0Jlc3QgQ0Eg
THRkMTcwNQYDVQQLEy5DbGFzcyAxIFB1YmxpYyBQcm..ENlcnRpZmljYXRp
b24gQXV0aG9yaXR5MRQwEgYDVQQDEwtCZXN0IENBIE..DAMBgNVHRMEBTAD
AQH/MA0GCSqGSIb3DQEBBAUAA4IBAQC1uYBcsSncwA..DCsQer772C2ucpX
xQUE/C0pWWm6gDkwd5D0DSMDJRqV/weoZ4wC6B73f5..bLhGYHaXJeSD6Kr
XcoOwLdSaGmJYslLKZB3ZIDEp0wYTGhgteb6JFiTtn..sf2xdrYfPCiIB7g
BMAV7Gzdc4VspS6ljrAhbiiawdBiQlQmsBeFz9JkF4..b3l8BoGN+qMa56Y
It8una2gY4l2O//on88r5IWJlm1L0oA8e4fR2yrBHX..adsGeFKkyNrwGi/
7vQMfXdGsRrXNGRGnX+vWDZ3/zWI0joDtCkNnqEpVn..HoX
-----END CERTIFICATE-----"""
2 Likes

I hadn’t thought of this! Thank you so much @snehankekre! I’ll give this a try later, but looks as though it’ll work.

1 Like

This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.