Cannot connect to Postgres on AWS

[ejstembler]

I have a Postgres instance on AWS (from Heroku) which I can connect to by other means (psql, Ruby, etc.). However, I cannot connect using pgo.

The sanitized url example:

postgres://user:password@ec2-nn-nnn-nnn-nn.compute-1.amazonaws.com:5432/databasename

I tried testing this in the rebar3 shell:

$ rebar3 shell
1> application:ensure_all_started(pgo).
{ok,[backoff,opentelemetry_api,pg_types,pgo]}
2> application:ensure_all_started(ssl).
{ok,[]}
3> pgo:start_pool(default, #{pool_size => 5, host => "ec2-nn-nnn-nnn-nn.compute-1.amazonaws.com", database => "databasename", user => "user", password => "password"}).
{ok,<0.254.0>}
4> pgo:query("SELECT id, author_id, slug, title FROM posts WHERE id = 5").
{error,none_available}

Notes

• I also tried ssl => true in pgo:start_pool; same {error,none_available} result though.

Reference: lpil/pog#21

[tsloughter]

@ejstembler sorry for the delay, are you on slack or somewhere you could DM me and maybe give me access to a test database in ec2 you can't connect to so I can reproduce? I'll get around to it faster if we can do that :)

[ejstembler]

@ejstembler sorry for the delay, are you on slack or somewhere you could DM me and maybe give me access to a test database in ec2 you can't connect to so I can reproduce? I'll get around to it faster if we can do that :)

Sure, I'm creating one today. What's the best way to give you the credentials? I'm on twitter and Slack as the same username as my GitHub/GitLab.

[ejstembler]

@tsloughter Let me know if you can test this. I spun up a Postgres RDS on AWS for this two weeks ago, and it costs me money...

[tsloughter]

Argh, sorry, just saw this, if I'd known you'd kept it running I'd have prioritized this better, very sorry! I'll ping you on slack.

[ejstembler]

@tsloughter No worries. I'm not on Slack any longer after I switched jobs 4 years ago. I can DM you on twitter, or if you prefer email... Or, do you already have a workspace on Slack for the repo?

[tsloughter]

email (tristan@sloughter.dev) or twitter (t_sloughter) is fine

[ejstembler]

email (tristan@sloughter.dev) or twitter (t_sloughter) is fine

Thanks. I sent an email...

[tsloughter]

Hm, so it works for me with ssl => true. What version of Erlang are you using?

> pgo:start_pool(default, #{pool_size => 1, host => "ec2-nn-nnn-nnn-nn.compute-1.amazonaws.com", database => "database", user => "user", password => "password", ssl => true}).
{ok,<0.275.0>}
> pgo:query("select 1").                 
#{command => select,num_rows => 1,rows => [{1}]}

Note it does log about the encryption:

2024-06-06T03:07:54.771982-06:00 warning: Description: "Authenticity is not established by certificate path validation", Reason: "Option {verify, verify_peer} and cacertfile/cacerts is missing"

But I'm not getting none_available.

[ejstembler]

Looks like I'm running 14.2.2:

Erlang/OTP 26 [erts-14.2.2] [source] [64-bit] [smp:12:12] [ds:12:12:10] [async-threads:1] [dtrace]
Eshell V14.2.2

Yes, It reutrns none_available, though I can retrieve data via Ruby, etc. Here's the shell using the newly setup AWS instance:

1> application:ensure_all_started(pgo).
{ok,[backoff,opentelemetry_api,pg_types,pgo]}
2> application:ensure_all_started(ssl).
{ok,[]}
3> pgo:start_pool(default, #{pool_size => 5, host => "database-1.xxxxxxxxxx.us-east-1.rds.amazonaws.com", database => "blog", user => "pgo_user", password => "xxxxxxxxxx"}).
{ok,<0.243.0>}
4> pgo:query("SELECT id, slug, title FROM books WHERE id = 5").
{error,none_available}

Thanks for taking a look at this. Any ideas?

[tsloughter]

What version of pgo? I was running on main. Also can you enable logging so we can see the error message. I use the config/sys.config in the pgo repo and run rebar3 with rebar3 shell --config config/sys.config

[ejstembler]

This is what I have in rebar.config:

{erl_opts, [debug_info]}.
{deps, [
    {pgo, {git, "https://github.com/erleans/pgo", {branch, "main"}}}
]}.

{shell, [
  % {config, "config/sys.config"},
    {apps, [er_pgo_test]}
]}.

And config/sys.config:

[
  {kernel, [
      {logger, [
          {handler, default, logger_std_h, #{
              level => debug,
              formatter => {logger_formatter, #{
                  legacy_header => true
              }}
          }}
      ]}
  ]}
].

Running the shell via dotenv -o -f ".env.aws-pgo_user" rebar3 shell --config config/sys.config outputs a bunch of log message when the shell starts up, but nothing new when typing in steps 1-4. It still ends up with the same {error,none_available}.

Forgot to mention, I also added DIAGNOSTIC=1 to my .env file.

[tsloughter]

Are you actually using .env.aws-pgo_user to read in the credentials or just putting them when running start_pool? Thats the only real difference I see here, aside from if er_pgo_test does anything or not?

[ejstembler]

The DATABASE_URL is in the .env file, but I'm testing using the individual parts in the shell, line 3:

3> pgo:start_pool(default, #{pool_size => 5, host => "database-1.xxxxx.us-east-1.rds.amazonaws.com", database => "blog", user => "pgo_user", password => "xxxxx"}).
{ok,<0.245.0>}

[tsloughter]

Oh oops! My bad, I was still running on OTP-24. I see the issue. Not sure why you didn't get any logs but when I do it on OTP-26 I see the errors that are happening:

unknown error when connecting to database: {options,incompatible,[{verify,verify_peer},{cacerts,undefined}]}

[tsloughter]

Quick "solution", add ssl_options => [{verify, verify_none}] to your options.

[ejstembler]

Yes, it's working now after adding!

ssl => true, ssl_options => [{verify, verify_none}]

Thanks for your help!

[tsloughter]

Great. I'm opening a new issue about setting ssl defaults so we provide more of a ruby experience.