SQL cannot connect

Dear friends,
After a lot of effort I still could not connect postgreSQL server with streamlit.
I configured all the settings but still problem!
On postgresql.conf file, I put:

listen_addresses = ‘*’

On pg_hba.conf file, I have:

> # TYPE  DATABASE        USER            ADDRESS                 METHOD
> 
> # "local" is for Unix domain socket connections only
> local   all             all                                     scram-sha-256
> host    all             all              0.0.0.0/0                       md5
> host    all             all              ::/0                            md5
> # IPv4 local connections:
> host    all             all             127.0.0.1/32            scram-sha-256
> host    all             all              0.0.0.0/0                       md5
> host    all             all              ::/0                            md5
> # IPv6 local connections:
> host    all             all             ::1/128                 scram-sha-256
> host    all             all              0.0.0.0/0                       md5
> host    all             all              ::/0                            md5
> # Allow replication connections from localhost, by a user with the
> # replication privilege.
> local   replication     all                                     scram-sha-256
> host    replication     all             127.0.0.1/32            scram-sha-256
> host    replication     all             ::1/128                 scram-sha-256
> host    all             all              0.0.0.0/0                       md5
> host    all             all              ::/0                            md5

I restarted postgresql server. I put the secrets exactly based on guidlines when I deployed the app. But it just does not work.
This is the error:

022-08-12 15:56:29.139 Uncaught app exception

Traceback (most recent call last):

File “/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/caching/cache_utils.py”, line 225, in get_or_create_cached_value

result = cache.read_result(value_key)

File “/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/caching/singleton_decorator.py”, line 272, in read_result

raise CacheKeyNotFoundError()

streamlit.runtime.caching.cache_errors.CacheKeyNotFoundError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):

File “/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/scriptrunner/script_runner.py”, line 556, in _run_script

exec(code, module.__dict__)

File “/app/test/app.py”, line 13, in

conn = init_connection()

File “/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/caching/cache_utils.py”, line 251, in wrapper

return get_or_create_cached_value()

File “/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/caching/cache_utils.py”, line 242, in get_or_create_cached_value

return_value = func(*args, **kwargs)

File “/app/test/app.py”, line 11, in init_connection

return psycopg2.connect(**st.secrets["postgres"])

File “/home/appuser/venv/lib/python3.9/site-packages/psycopg2/init.py”, line 122, in connect

conn = _connect(dsn, connection_factory=connection_factory, **kwasync)

psycopg2.OperationalError: connection to server at “localhost” (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?

connection to server at “localhost” (10.12.64.67), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?

connection to server at “localhost” (::1), port 5432 failed: Cannot assign requested address
Is the server running on that host and accepting TCP/IP connections?

Did you manage to find a solution? I am having the same issue. When I run my app locally the db connection os successful, however when I run on streamlit cloud the db connection is not working. Even after all the secret settings and .conf file settings.

I am leadind with the same problem trying to set postgres on streamlit sharing:

Traceback (most recent call last):
  File "/home/appuser/venv/lib/python3.9/site-packages/streamlit/runtime/scriptrunner/script_runner.py", line 565, in _run_script
    exec(code, module.__dict__)
  File "/app/rocketkitchens/main.py", line 8, in <module>
    from Dashboard.View import login_page
  File "/app/rocketkitchens/Dashboard/View/login_page.py", line 9, in <module>
    from Dashboard.View.Pages import _1_main_page, _2_admin_page, _3_communication_page, _4_financial_page, _5_operations_page, \
  File "/app/rocketkitchens/Dashboard/View/Pages/_1_main_page.py", line 9, in <module>
    from Dashboard.View.Pages import _2_admin_page
  File "/app/rocketkitchens/Dashboard/View/Pages/_2_admin_page.py", line 48, in <module>
    from Dashboard.Controller import client
  File "/app/rocketkitchens/Dashboard/Controller/client.py", line 2, in <module>
    import Services.database as db
  File "/app/rocketkitchens/Services/database.py", line 17, in <module>
    conn = init_connection()
  File "/app/rocketkitchens/Services/database.py", line 9, in init_connection
    return psycopg2.connect(
  File "/home/appuser/venv/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (10.12.73.26), port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 5432 failed: Cannot assign requested address
	Is the server running on that host and accepting TCP/IP connections?

You need these info:

[postgres]
host = ""
port = 
dbname = ""
user = ""
password = ""

Just take it into consideration that, you cannot create a database on your local machine (laptop, or pc) and then connect it with streamlit cloud. You have to have the above mentioned information of a cloud database. Then you can connect your cloud database with streamlit.

1 Like

@gustavofelicidade
You cannot access a local database running on localhost if your streamlit app runs on streamlit cloud. Your database must be reachable from the outside world, as @Sinakian already mentioned.