Preset.io and the pg_tls handler

Questions / Help
1

@shaun after following the above instructions when i ran the below command it gave me an error:

psql "sslmode=require host=<db-app-name>.fly.dev dbname=<db-name> user=<user-name>"
psql: error: connection to server at "<db-app-name>.fly.dev" (<IPv4 address>), port 5432 failed: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

and by running fly info -a <db-app-name>, i get:

App
  Name     = <db-app-name>
  Owner    = <owner>
  Platform = machines
  Hostname = <db-app-name>.fly.dev

Services
PROTOCOL	PORTS                 	FORCE HTTPS
TCP     	80 => 8080 [HTTP]     	True
        	443 => 8080 [TLS,HTTP]	False
TCP     	5432 => 5432 [PG_TLS] 	False

IP Addresses
TYPE	ADDRESS            	REGION	CREATED AT
v4  	<IPv4 address>   	global	2022-11-21T07:01:30Z
v6  	<IPv6 address>	    global	2022-11-21T07:00:45Z
2

It works for me. I used your db’s .fly.dev hostname and it prompted me for a password.

Is it still not working for you?

3

yes it is not working for me :sweat_smile:

4

I need to attach it to the preset.io and it is not working for me. Although, if i add this in my toml file:

[[services]]
  internal_port = 5432
  protocol = "tcp"

  [[services.ports]]
    handlers = []
    port = 12500

then it gets attached to the preset.io but @shaun said this is not secure that is why i am trying to implement the one mentioned here.

5

@jerome can you please help me out here.

6

I’m assuming your replaced all the <db-app-name> and other “variables” enclosed in <>?

Since this works for me, it’s a bit hard to troubleshoot.

What psql version are you using? Run psql --version.

7 
psql (PostgreSQL) 14.5

yes, <> everything enclosed in these means i replaced my original names with these dummy ones

8

What does telnet <db-app-name>.fly.dev 5432 does?

9

@jerome Do you want me to run this command and tell you the output?

10

Yes. That’s just to see if you can connect to your DB app’s ip on port 5432.

11

@jerome, this is the output:

Trying <Pv4 Address>...
Connected to <DB-app-name>.fly.dev.
Escape character is '^]'.
12

It seems like i am able to connect to the DB, right?

13

Looks like that works.

There’s no reason psql wouldn’t work or at least, it shouldn’t exit with the error you got. Try the psql command again please.

You should at least see:

Password for user <user-name>:

More than one of us are able to connect (but we can’t auth because we don’t have the username / password for you database, that’s fine). We get to the password prompt, which indicates it can connect just fine.

Can you also provide a traceroute to <db-app-name>.fly.dev please?

14

@jerome i am unable to get to the password part. Should i send you my toml file?
and how can i provide you my tracroute? :sweat_smile:

15

The fly.toml is likely ok. Or else we wouldn’t be able to connect to it.

traceroute <db-app-name>.fly.dev

You’ll have to wait a bit until you get a bunch of * * * hops.

16

@jerome , this is the output:

traceroute to <db-app-name>.fly.dev (<IPv4 Address>), 64 hops max, 52 byte packets
 1  10.10.20.1 (10.10.20.1)  3.858 ms  4.684 ms  2.139 ms
 2  103.73.101.33 (103.73.101.33)  4.282 ms  19.852 ms  23.075 ms
 3  59.103.231.138 (59.103.231.138)  6.216 ms  4.870 ms  4.001 ms
 4  10.253.8.18 (10.253.8.18)  4.528 ms  3.726 ms  3.700 ms
 5  10.253.4.36 (10.253.4.36)  22.576 ms  23.374 ms  20.874 ms
 6  10.253.4.24 (10.253.4.24)  27.661 ms
    10.253.4.2 (10.253.4.2)  22.877 ms *
 7  * * *
17

Wait a bit more :slight_smile:

18

sure.

19 
 1  10.10.20.1 (10.10.20.1)  2.791 ms  2.920 ms  3.266 ms
 2  103.73.101.33 (103.73.101.33)  3.006 ms  9.010 ms  8.405 ms
 3  59.103.231.138 (59.103.231.138)  7.597 ms  8.702 ms  4.305 ms
 4  10.253.8.18 (10.253.8.18)  10.812 ms  6.030 ms  5.686 ms
 5  10.253.4.36 (10.253.4.36)  22.521 ms  21.091 ms  21.134 ms
 6  10.253.4.24 (10.253.4.24)  22.081 ms *
    10.253.4.2 (10.253.4.2)  26.164 ms
 7  * * *
 8  * * *
 9  * * *
10  * * *
11  * * *
12  * * *
13  * * *
14  * * *
15  * * *
16  * * *
17  * * *
18  * * *
19  * * *
20  * * *
21  * * *
22  * * *
23  * * *
24  * * *
25  * * *
26  * * *
27  * * *
28  * * *
29  * * *
30  * * *
31  * * *
32  * * *
33  * * *
34  * * *
35  * * *
36  * * *
37  * * *
38  * * *
39  * * *

@jerome New ones are getting * * *.

20

Are you sure the <IPv4 Address> matches one of your assigned ipv4?

Can you try a mtr now?

mtr <db-app-name>.fly.dev

Let it run for ~2 minutes and paste the output please.