Problem connecting to postgres/AGE (remote, TLS)

[david-m-m]

Description I am trying to connect the agviewer to a remote postgres which is enforcing TLS.

The database connection fails with this error message:

Database Connection Failed

Failed to connect to the database. Are you sure the database is running on the server?
no pg_hba.conf entry for host "<my local IP address>", user "age_user", database "age", SSL off

To Reproduce Steps to reproduce the behavior:

1. Set up remote postgres running AGE with TLS enabled, use an internal CA or self signed certificate
2. Run ageviewer locally

docker run --publish=3001:3001--name=agviewer bitnine/agviewer

3. Open ageviewer FE in browser and enter data for DB
4. See above error

Expected behavior The error message should be more informative and propose actionable items. (No pg_hba.conf exists in ageviewer image...)

Ideally the error should not occur and the connection to the DB should be established.

Additional context To my understanding these factors could play a role:

• TLS (vs. unencrypted)
• internal CA / self signed certificate for DB (How would I inject the new CA in the image)

[shinhanbyeol]

I tried to find out the cause of the problem you are experiencing, but it is difficult to identify at the moment. Instead, as you said, we will correct the error log so that it can be guided in more detail.

[david-m-m]

Great, thank you very much. With that information I can either solve it or provide more information.

[ChipNowacek]

I cannot connect to a database running locally. Error:

Database Connection Failed

Failed to connect to the database. Are you sure the database is running on the server?
connect ECONNREFUSED 127.0.0.1:5432

Yes, I'm sure the database is running on the server. I have no trouble connecting with pgAdmin 4 or psql.

Edit 1: Same steps to reproduce. Edit 2: Edit 1

[joefagan]

@ChipNowacek Because you're running in a container, perhaps you need to set the URL to host.docker.internal instead of localhost or 127.0.0.1

This should work MAC and Windows environments. For other OS try 172.17.0.1 (yes 172, not 127) or try the suggestions here...

https://dev.to/natterstefan/docker-tip-how-to-get-host-s-ip-address-inside-a-docker-container-5anh

[ChipNowacek]

@ChipNowacek Because you're running in a container, perhaps you need to set the URL to host.docker.internal instead of localhost or 127.0.0.1

This should work MAC and Windows environments. For other OS try 172.17.0.1 (yes 172, not 127) or try the suggestions here...

https://dev.to/natterstefan/docker-tip-how-to-get-host-s-ip-address-inside-a-docker-container-5anh

It's wonderful finding geniuses who help non-geniuses. I'm connected. Thanks.

I hope I can use PostgreSQL for graph work. I have been searching for months.

[david-m-m]

I finally had a bit more time to investigate. Looking at the network traffic shows that the age-viewer does not try to establish a tls connection: Packet No. 4 containing the plaintext authentication request:

PostgreSQL Type: Startup message Length: 58 Parameter name: user Parameter value: age_owner Parameter name: database Parameter value: age Parameter name: client_encoding Parameter value: UTF8

In contrast the (working) connection with psql looks like this: Packet No.4 in this case is the request for SSL/TLS:

PostgreSQL Type: SSL request Length: 8

So that's most likely my problem.

Although I'm not a JavaScript developer I think I'd start looking here: https://github.com/apache/incubator-age-viewer/blob/4c912a0cdd4baaa4092f35229f8752d0214166f1/backend/src/models/agensgraph/agensGraphRepository.js#L26

[emotionbug]

@shinhanbyeol progress?

[shinhanbyeol]

@emotionbug @david-m-m

It's being developed and tested. If you are interested, you can come to my repository and take a look.

here -> https://github.com/shinhanbyeol/incubator-age-viewer/tree/feautre/ssl-connnector

[pawankukreja01]

It seems that the error message indicates that there is no entry in the pg_hba.conf file for your IP address, user, and database. This file is used to configure client authentication and can be found in the PostgreSQL data directory.

To connect to a remote PostgreSQL server with TLS enabled, you need to ensure that the server certificate and key files are available, and the SSL configuration is enabled. You also need to make sure that the pg_hba.conf file rules are updated accordingly

[vigneshkumar2016]

It seems that the error message indicates that there is no entry in the pg_hba.conf file for your IP address, user, and database. This file is used to configure client authentication and can be found in the PostgreSQL data directory.

To connect to a remote PostgreSQL server with TLS enabled, you need to ensure that the server certificate and key files are available, and the SSL configuration is enabled. You also need to make sure that the pg_hba.conf file rules are updated accordingly

Hi, Thanks for your suggestion. I have updated the pg_hba.conf file updated with rules. and even then this is not connecting with SSL. just that i dont see the Pg.js or the GraphRepository.js is calling out for SSL.

was able to connect with my psql client and pgAdmin no issues at all. is there is this working. or am i missing something. any help is greatly appreciated.

length: 173, severity: 'FATAL', code: '28000', detail: undefined, hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: undefined, table: undefined, column: undefined, dataType: undefined, constraint: undefined, file: 'auth.c', line: '545', routine: 'ClientAuthentication'