Page 1 of 1
Connecting to AWS/RDS Postgres instance
Posted: Sun Mar 03, 2024 5:02 am
by tua
I have an AWS/RDS Postgres server instance to which I can connect without any problems using Navicat and pgAdmin 4 (from the same PC/IP).
Code: Select all
UsePostgreSQLDatabase()
If OpenDatabase(0, "host=<my server>.amazonaws.com port=5432 dbname=<my_database>", "<my_user>", "<my_password>")
Debug "Connected to PostgreSQL"
Else
Debug "Connection failed: "+DatabaseError()
EndIf
Does anyone know what's not working here and why?
Re: Connecting to AWS/RDS Postgres instance
Posted: Sun Mar 03, 2024 10:35 am
by infratec
The first answer: you got in the reply: you have to add your IP to the allowed hosts in pg_hba.conf
The second answer: you may need secured access, which is not possible with PB at the moment.
For this I have written a postgres proxy. But I can not open source the code.
Re: Connecting to AWS/RDS Postgres instance
Posted: Sun Mar 03, 2024 5:37 pm
by tua
I don't think so:
As I wrote above, my IP is in the clear as I can connect to the AWS server from the SAME machine using anything but Purebasic.
You probably have a point in regards to security. I have chosen the simplest, most insecure option in the AWS user interface but it might still not allow 'zero' security if that's what PB does here.
Ah well - back to Delphi to get this project done
Re: Connecting to AWS/RDS Postgres instance
Posted: Sun Mar 03, 2024 5:41 pm
by Fred
infratec wrote: Sun Mar 03, 2024 10:35 am
The first answer: you got in the reply: you have to add your IP tothe allewd hosts in pg_hba.conf
The second answer: you may need secured access, which is not possible with PB at the moment.
For this I have written a postgres proxy. But I can not open source the code.
Which mode is missing in the PB dll ?
Re: Connecting to AWS/RDS Postgres instance
Posted: Sun Mar 03, 2024 6:20 pm
by infratec
I will look tomorrow.
With PB 5.73 it was not possible to connect via ssl to a PostgreSQL database.
I had to write a Proxy which uses gnutls.
And it was needed because customers from outside made queries over the internet.
Re: Connecting to AWS/RDS Postgres instance
Posted: Mon Mar 04, 2024 2:10 pm
by Marc56us
PostgreSQL 14 and PB 6.10 b7 Win x64 works fine for me.
Part of
pg_hba.conf in root of data folder. (my server is 192.168.0.100)
Code: Select all
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.0.100/32 scram-sha-256
Re: Connecting to AWS/RDS Postgres instance
Posted: Mon Mar 04, 2024 3:59 pm
by infratec
Re: Connecting to AWS/RDS Postgres instance
Posted: Mon Mar 04, 2024 10:17 pm
by tua
That's all good and well - I have no problems connecting to a local (i.e. under my control) PG database either.
As the title of my post states, it is connecting to AWS/RDS - has anyone managed that with Purebasic?
Re: Connecting to AWS/RDS Postgres instance
Posted: Tue Mar 05, 2024 7:45 am
by infratec
Yes.
But as written: you need to write a SSL proxy for Postgres.
Re: Connecting to AWS/RDS Postgres instance
Posted: Tue Mar 05, 2024 8:22 am
by HeX0R
Why did you write your own proxy, was stunnel not working/allowed?
Re: Connecting to AWS/RDS Postgres instance
Posted: Tue Mar 05, 2024 8:37 am
by infratec
The problem is/was how Postgres starts SSL:
https://www.postgresql.org/docs/current ... L-FLOW-SSL
To initiate an SSL-encrypted connection, the frontend initially sends an SSLRequest message rather than a StartupMessage. The server then responds with a single byte containing S or N, indicating that it is willing or unwilling to perform SSL, respectively. The frontend might close the connection at this point if it is dissatisfied with the response. To continue after S, perform an SSL startup handshake (not described here, part of the SSL specification) with the server. If this is successful, continue with sending the usual StartupMessage. In this case the StartupMessage and all subsequent data will be SSL-encrypted. To continue after N, send the usual StartupMessage and proceed without encryption. (Alternatively, it is permissible to issue a GSSENCRequest message after an N response to try to use GSSAPI encryption instead of SSL.)
stunnel does not handle this.
Re: Connecting to AWS/RDS Postgres instance
Posted: Thu Mar 07, 2024 6:17 pm
by tua
That's above my paygrade!
I'll be done writing the whole thing in Delphi or Lazarus before I figure that one out! Thanks!