How to Connect to a Remote PostgreSQL DB

[nofue]

Servus!

My setup looks like this:
There's a client at 192.168.1.38 running the web browser,
an application server listening on 192.168.1.231, supposed to run sqlpage.bin
and a PostgreSQL DB Server listening on 192.168.1.250

Trying to connect from the application server to the database server shows an error when launching ./sqlpage.bin:

[{timestamp} ERROR sqlpage] "postgresql://postgres:PassWord#[email protected]&port=5433/rushdb" is not a valid database URL. Please change the "database_url" option in the configuration file.
    
    Caused by:
        0: error with configuration: invalid port number
        1: invalid port number

postgres:PassWord#9876 is the username:password part, rushdb the name of the database.

The port number works perfectly well using pgAdmin 4 on the client at 192.168.1.38.

I also tried the 'usual'
"postgresql://postgres:PassWord#[email protected]:5433/rushdb"

notation and several more variants, but the error remains the same no matter what.

What am I missing?

[lovasoa]

The issue lies in the way the password and port are represented in the URL.

1. The # in the password is a special character in URLs and must be percent-encoded.

2. The port should be specified with a colon (:), not an ampersand (&).

Here's the corrected URL:

postgresql://postgres:PassWord%[email protected]:5433/rushdb

Explanation:

PassWord#9876 becomes PassWord%239876, where %23 is the URL-encoded value for #.

The port is correctly indicated with :5433.

This should resolve your issue.

[nofue]

Perfect, thanks a ton. Now the insanity of using two servers works like a charm. Have a great day!

Oh, here's one more thing: On GitHub there is
https://github.com/lovasoa/SQLpage/blob/main/configuration.md

and the Database connection URL is written as

• • • (a quote tag would be nice, just saying ;)
      Database connection URL, in the form dbname://user:password@host:port/dbname. Special characters in user and password should be percent-encoded.

---

I think the first bold "dbname" should be "dbengine" (or whatever fits), and the second sentence I'd extend to

Characters which have a special use in URLs, like # (%23), (%20) etc., should be …

I think the same paragraph reads different in your documentation website, but as I'm in a hurry I cannot check that.

Oh, and the idea of using the ampersand way of specifying the port was in the online documentation. I first used the usual colon but this didn't because I hadn't clicked the link to the list of percent encodings – your tipps need to be read like contracts ;)

[nofue]

Hey, this thing is quoting by my incidentally used characters. Awesome ;)

[nofue]

OK, got it -- it's from your link to
https://www.postgresql.org/docs/15/libpq-connect.html#id-1.7.3.8.3.6 where one possible way to set the port is shown as
postgresql:///mydb?host=localhost&port=5433

[lovasoa]

I updated https://github.com/lovasoa/SQLpage/blob/main/configuration.md . Do not hesitate to open a pull request on the file directly if you want to contribute improvements !

[nofue]

As if I'd know what a pull request could possibly be … I have to admit, I was pretty successful by avoiding GitHub like the plague up to this day ;)

[lovasoa]

Haha ! Nowadays it's quite easy ! You can just click the little pencil on the top right of the file whenever you see something you don't like:

[nofue]

OK, I see your point. Clicking a pencil shouldn't be rocket science … ;)

[lovasoa]

I added a new configuration parameter for the next version of sqlpage: database_password. It allows storing the password separately from the connection string. This way it does not have to be percent-encoded, and it is never logged.

[nofue]

Great feature. Keeping the password hidden should help anybody's paranoia a lot ;)