Thread: Feature request: psql --idle
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.
It would be great to give them psql as a login shell (in /etc/passwd).
But doing so, will result in psql exiting with error with the usual
$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
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?
Tnx!
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.
read dbname
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 "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 5432 -U postgres postgres
elif [ "$dbname" == "2" ] || [ "$dbname" == " other_dbs " ]
then
psql -h 127.0.0.1 -p 5555 -U postgres postgres
fi
fi
Hope it helps.
Cheers,
Mateusz
Mateusz
śr., 27 lip 2022 o 14:50 Wiwwo Staff <wiwwo@wiwwo.com> napisał(a):
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.It would be great to give them psql as a login shell (in /etc/passwd).But doing so, will result in psql exiting with error with the usual$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?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 tosqlplus /nologIs anything like that feasible or is there another solution/workaround?Tnx!
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:
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.It would be great to give them psql as a login shell (in /etc/passwd).But doing so, will result in psql exiting with error with the usual$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?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 tosqlplus /nologIs anything like that feasible or is there another solution/workaround?Tnx!