Feature request: psql --idle

Hi,
Your first sentence is wrong. Changing pg_hba.conf file does not require
server restart. It is enough to reload the configuration using "pg_ctl
reload", "select pg_reload_conf();" in psql or just sending SIGHUP from
linux terminal to postmaster process after changing the pg_hba file.

To achieve something like this you could use simple bash script like the
one below and add it to your /etc/passwd file, like
/etc/passwd
test:x:1001:1001::/home/test:/home/test/skrypt.sh

/home/test/skrypt.sh
#!/bin/bash
echo "Select a database"
echo "1. local"
echo "2. other_dbs"

read dbname

if [ "$dbname" == "1" ] || [ "$dbname" == "local" ]
then
psql -h 127.0.0.1 -p 5432 -U postgres postgres
elif [ "$dbname" == "2" ] || [ "$dbname" == " other_dbs " ]
then
psql -h 127.0.0.1 -p 5555 -U postgres postgres
fi

Hope it helps.

Cheers,
Mateusz

śr., 27 lip 2022 o 14:50 Wiwwo Staff <wiwwo@wiwwo.com> napisał(a):

Hi,

On Wed, Jul 27, 2022 at 02:49:45PM +0200, Wiwwo Staff wrote:

Since changing ph_hda.conf file to give users access involves the restart
of server, many companies I work(ed) use a bastion host, where users ssh
to, and are allowed "somehow" use postgresql.

You mean pg_hba.conf right? It doesn't need a restart, only a reload as
documented at https://www.postgresql.org/docs/current/auth-pg-hba-conf.html:

The pg_hba.conf file is read on start-up and when the main server process
receives a SIGHUP signal. If you edit the file on an active system, you will
need to signal the postmaster (using pg_ctl reload, calling the SQL function
pg_reload_conf(), or using kill -HUP) to make it re-read the file.

That being said, it's usually not a good idea to allow connection from all
around the world, so not all users may be able to connect from their local
machine anyway.

What would help, is a --idle option, where psql does not exit, stays idle
and waits for user to give a \conn command.
Something similar to
sqlplus /nolog

Is anything like that feasible or is there another solution/workaround?

That might be a good thing to have, as some users may want to rely on psql for
things like \h or \? to work on stuff while not being able to connect to a
remote server (and for some reason who wouldn't want to, or maybe couldn't,
install a local instance). I would call it something like "--no-connection"
more than "--idle" though.

On Wed, Jul 27, 2022 at 7:50 AM Wiwwo Staff <wiwwo@wiwwo.com> wrote:

Since changing ph_hda.conf file to give users access involves the restart
of server, many companies I work(ed) use a bastion host, where users ssh
to, and are allowed "somehow" use postgresql.

Still, those users need some login shell.

No, they don't need login shells. You can set up an SSH tunnel to the
bastion server on the user's system that in turn sets up a tunnel to the
database server on the bastion server.

Something like this:
ssh -f -N user@bastion -L XXXX:dbserver:YYYY

So when the user connects to port XXXX on the local server it tunnels
through to port YYYY on the dbserver through the bastion server.

This way you can limit who has access to the bastion server, and you can
set the PostgreSQL server to accept (only) the IP address of the bastion
server. We use this to access a database on an RDS server at AWS from a
server at a different data center.
--
Mike Nolan

Thanks all for the alternative solutions.
Yet, despite of my (wrong and misleading) use-case, I still share Julien's
view of this being useful, whatever the parameter or the use-case.

My 2 cents :-)

On Wed, Jul 27, 2022 at 2:49 PM Wiwwo Staff <wiwwo@wiwwo.com> wrote: