0

I want to connect to my postgres databases using the dbcrossbar packages. So I'd connect to my database using the code below. This works perfectly fine. However, when using dbcrossbar I seem to have errors connecting and I have no idea how this can occur.

psql -h localhost -d my_db -U postgres 
psql -h <server_ip> -d my_db2 -U postgres

The dbcrossbar code is as follows:

1)
dbcrossbar cp \
    --if-exists=overwrite \
    --schema=postgres-sql:table.sql \
    csv:"my_csv.csv" \
    'postgres://postgres@localhost:5432/my_db#my_table'

2)
dbcrossbar cp \
    --if-exists=overwrite \
    --schema=postgres-sql:table.sql \
    csv:"my_csv.csv" \
    'postgres://postgres@<server_ip>:5432/my_db#my_table'

The first piece of code connecting to localhost gived me the error (hostname mismatch) and the second piece of code connecting to the server gives the error (unable to get local issuer certificate). To solve it I already looked at SSL Error: unable to get local issuer certificate but could not find the problem. Here I tried openssl s_client -connect <server>:<port> and for both methods it outputs Verify OK (0), so everything seems to be working. I think it has to do something with my setup, as this issue is not found on the dbcrossbar github, what could be the issue when receiving such errors?

complete output:

1)
Error: could not connect to PostgreSQL
  caused by: error performing TLS handshake: error:0A000086:SSL routines:tls_post_process_server_certificate:certificate verify failed:../ssl/statem/statem_clnt.c:1883: (hostname mismatch)

2)
Error: could not connect to PostgreSQL
  caused by: error performing TLS handshake: error:0A000086:SSL routines:tls_post_process_server_certificate:certificate verify failed:../ssl/statem/statem_clnt.c:1883: (unable to get local issuer certificate)
Jeroen Vermunt
  • 672
  • 1
  • 6
  • 19
  • I never get "Verify OK (0)" from openssl s_client. I do get "Verification: OK" and "Verify return code: 0 (ok)", but I get those even when connecting to a port that doesn't do ssl at all, so they appear to be worthless as diagnostic messages. – jjanes May 11 '22 at 13:31
  • 1
    What you get with `PGSSLMODE=verify-full psql -h localhost -d my_db -U postgres`? – jjanes May 11 '22 at 13:32
  • This gives `psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: root certificate file "/home/username/.postgresql/root.crt" does not exist `. Should I create these? – Jeroen Vermunt May 11 '22 at 14:01
  • 1
    If you want to test ssl to localhost with psql, then yes you should install the root.crt. Or of you don't care, then try to get dbcrossbar not to use ssl. – jjanes May 11 '22 at 14:23
  • There does not seem to be an option to disable the ssl, does this mean I need to create a certificate for both servers and place them in /home/username/.postgresql/root.crt – Jeroen Vermunt May 12 '22 at 08:27

0 Answers0