Connecting to AWS/RDS Postgres instance

[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

Connection failed: connection to server at "<my server>.rds.amazonaws.com" (35.1xx.1xx.2xx), port 5432 failed: FATAL: no pg_hba.conf entry for host "1xx.xx.xxx.54", user "postgres", database "<my db>", no encryption

Does anyone know what's not working here and why?

[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.

[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

[Fred]

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 ?

[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.

[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

[infratec]

My entry:

Code: Select all

hostssl all     all     0.0.0.0/0       md5

[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?

[infratec]

Yes.
But as written: you need to write a SSL proxy for Postgres.

[HeX0R]

Why did you write your own proxy, was stunnel not working/allowed?

[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.

[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!