Not able to access Postgresql database that is hosted on Google Cloud SQL server instance from my deployed app

☁️ Community Cloud
1

Summary

Currently, I have finished a web app that is using Postgresql as the database queries information from the db on one of my pages. This feature works locally. When I deployed my web app to streamlit-cloud I made sure to connect my PostgreSQL server to a Google Cloud PostgreSQL server instance. I used my GCP instance information for the secrets.toml file wherein my secrets.toml looks like this:

[postgres]
host = "my public ip address from GCP server instance"
port = 5432
dbname = "postgres"
user = "postgres"
password = "my GCP server instance password"
### Steps to reproduce

Am I setting up my secrets.toml file wrong for production? I know the dbname, user password are correct (since it works locally), but not as sure about the port or host to utilize the GCP server instance that’s connected to my PostgreSQL database.
Expected behavior:

I expect that my deployed app on streamlit cloud should be able to connect to the google cloud server instance connected to my PostgreSQL database and query from the database like the web app does locally.

Actual behavior:
When the deployed app tries to query from the database it errors instead.

Error:
psycopg2.OperationalError: This app has encountered an error. The original error message is redacted to prevent data leaks. Full error details have been recorded in the logs (if you're on Streamlit Cloud, click on 'Manage app' in the lower right of your app).

Original Error:

psycopg2.OperationalError: connection to server at
"host(my public ip address from GCP server instance) ", port 5432 failed: 
Connection timed out

Is the server running on that host and accepting TCP/IP connections?

Does anyone know how to fix this error?

Debug info

  • Streamlit version: 1.22.0
  • Python version: 3.10.11
  • Using PipEnv
  • OS version: macOs 13.3.1

Requirements file

Using pipenv

Links

2

Hi @Esau_Hutcherson

So it seems that there may need to be authorization of IP address for apps to access the database on the Google Cloud platform. Particularly, Google Cloud seems to have an in-depth coverage on how to access the database instance using a public IP or private IP as well as using authorized networks for whitelisting access to the database.

Please explore more in the suggested resources from Google Cloud:

  • https://cloud.google.com/sql/docs/postgres/connect-overview
  • https://cloud.google.com/sql/docs/postgres/configure-ip
  • Also check out their section on Connect from applications in the left sidebar.

A similar service called Bit.io shows a code snippet on connecting a Streamlit app to their database here.

Hope this helps!

Best regards,
Chanin

3

Hi I read the documentation and I’m still currently stuck on how to utilize GCSQL with my deployed app on streamlit community cloud. Could you help me troubleshoot my issue? I’ll detail some of the steps I’ve followed thus far I must be missing something but I am not familiar with networking so I’m a little lost.

  1. Set up GoogleCloudSQL server instance with PostgreSQL DB in pgadmin4
    1a. Utilized the public IP address as the Host name/address
    1b. Made sure in pgadmin4 that i could query from the data (successful)

  2. Set up private IP address for GCSQL server instance (From what I read this is needed to allow my web app that is deployed on streamlit cloud to access the server might be incorrect on this assumption)
    2a. To set up private IP address had to set up VPC network for it (not exactly sure about this process either)

  3. Tried utilizing my private IP address given by GCSQL inside my .secrets file for host and for my PostgreSQL database however I still run into the same error but this time even locally I cannot connect to the private IP address. When I utilize the public IP address given by GCSQL I am able to connect locally but not through my deployed app.

I tried researching about how to utilize GCSQL sever instances however I’m still confused on how to configure the settings (which ip address to use public or private?) to allow the deployed streamlit clout web app to access the SQL server on Google Cloud.

As to why I can connect locally to the server instance using the public ip address I’m assuming its because I added the ip address of my machine to the Authorized networks. I’m still lost on how to setup the networking but did I miss any important steps to help me connect my app to GCSQL?

4

I was able to fix the issue by adding 0.0.0.0/0 (should allow any ipv4 address to access the deployed website) to authorized public IP addresses inside the google cloud SQL sever instance.

From what I’ve read you shouldn’t do this however I couldn’t get the VPC and private IP address configuration to work even though it had the same 0.0.0.0/0 allowance.

1 Like
5

Hi @Esau_Hutcherson

Glad to hear that it worked out and thanks for sharing your solution!

Best regards,
Chanin

6

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