charmed-postgresql.psql not working for login to postgres units

[Paul Goins]

Software versions in use:

* LXD: 5.15-002fa0f
* Juju: 3.1.5
* Postgres charm: 14/stable, revision 288, base ubuntu@22.04
* Charm deployed as: both container and virtual-machine virt-types

Expected behavior: Following the tutorial (https://discourse.charmhub.io/t/charmed-postgresql-tutorial-deploy-postgresql/9697), and using Juju 3.x equivalents for the Juju 2.9 commands listed in the tutorial, I should be able to run "juju run postgresql/leader get-password username=operator" to get the operator password, and then from within a shell session on the postgres unit I should be able to run e.g. "charmed-postgresql.psql --host=$POSTGRES_UNIT_IP --username=operator --password" to open a psql shell.

Observed behavior: The charmed-postgresql.psql command fails to connect. Whether run via the ubuntu user or root, it gives me errors like this:

  psql: error: connection to server at "10.29.120.113", port 5432 failed: could not create socket: Permission denied

I am suspecting there may be snapcraft permissions that need to be set so charmed-postgresql.psql can open socket connections.

[Alex Lutay (taurus)]

Dear Paul,

Thank you for the bugreport!

I believe it is a duplicate of GitHub issue #176: https://github.com/canonical/postgresql-operator/issues/176 which has been addressed already in 14/edge, revision 309+ (and pending charm upgrade support to release in 14/stable).

Please note, the modern charm uses GitHub and not a Launchpad, I have updated the manual https://charmhub.io/postgresql/docs/e-legacy-charm to indicate such a detail.

Please re-check it from your side using 14/edge and report to GitHub if the issue still exist for you.
Note, revision 309+ creates a snap alias `psql`, so you can use it directly, no need to use long `charmed-postgresql.psql`. We believe it improves usability!

[Paul Goins (vultaire)]

Thank you Alex.

Indeed, 14/edge works. The socket access issue is fixed, plus the psql alias to charmed-postgresql.psql is also present as well. Thank you!