Cannot connect to a PostgreSQL remote server

Community Cloud
,
1

Dear Streamlit Community,

I am trying to store the user’s input on a Postgres database (I am using PGAdmin).
To this aim, I generate a new server with:
Host name/address = 192.168.29.4
Port = 5432
And I added a new database named “Example” (where I defined a table “participants” with the primary key “email”)

This is the piece of code where I ask for an email to the user and I try to store it on the database when he/she presses a button:

email = st.text_input(
    "Insert your email address:", 
    placeholder="email@address.com",
)
regex = r'\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,7}\b'
valid = False
columns = st.columns((2, 1, 2))
buttonStart = columns[1].button('Start the survey')
if buttonStart:
    if (re.fullmatch(regex, email)): 
        conn = st.connection("postgresql", type="sql") 
        try:
            with conn.session as s: 
                try:
                    st.write('Before execute()')
                    s.execute(text('INSERT INTO participants (email) VALUES(:email)'), {"email": email})
                    st.write('After execute() and before commit()')
                    s.commit()   
                    st.write('After commit()')     
                except Exception as e:
                    st.write(f"Error: {e}")
                
        except Exception as e:
            st.write(f"Error: {e}")
    else:
        st.write(':red[Please, write a valid email address]')

However, when I press the button, the execution stops after writing the first “debugging point” and an error:

Before execute()
Error: (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.11.102), 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?

This is the content of the secrets.toml file:

[postgresql]
dialect = "postgresql"
host = "192.168.29.4"
port = "5432"
database = "Example"
username = "exa"
password = "exa"

And this is the content of Settings → Secrets on Streamlit:

[postgresql]
dialect = "postgresql"
host = "192.168.29.4"
port = "5432"
database = "Example"
username = "exa"
password = "exa"

This is the output when I execute this line on the cmd:

 psql -U postgres -h 192.168.29.4 SurveyResults

psql (16.4)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.
SurveyResults=#

pg_hba.conf file contains these rows:

host	all 		  all		  0.0.0.0/0		      trust
host    replication 		  all		   0.0.0.0/0      trust

And postgresql.conf contains:

listen_addresses = '*'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
port = 5432				# (change requires restart)
max_connections = 100			# (change requires restart)

I don’t understand where the problem is. Do you have any idea?

2

192.168.* is a private IP address Private vs. Public IP Addresses | Differences Explained. .

Your PostgreSQL instance must be accessible from the internet.