Determine if a user and database are available
Hi Everyone,
I have another beginner question. I am trying to use pg_isready to
determine if a database and user are present. The program seems to
always succeed, even when I delete the user or the database.
This baffles me from the man page. I guess this explains the behavior
I am seeing.
NOTES
It is not necessary to supply correct user name, password, or database
name values to obtain the server status; however, if incorrect values
are provided, the server will log a failed connection attempt.
A typical usage is shown below, where variables are parsed from a config file.
password=$(grep 'DD_DB_Rpass' dojoConfig.yml | awk '{ print $2 }')
hostname=$(grep 'DD_DB_Host' dojoConfig.yml | awk '{ print $2 }')
database=$(grep 'DD_DB_Name' dojoConfig.yml | awk '{ print $2 }')
username=$(grep 'DD_DB_Ruser' dojoConfig.yml | awk '{ print $2 }')
PGPASSWORD=${password} pg_isready \
-h "${hostname}" -U "${username}" -d "${database}"
Given the NOTES in the man page, how do we determine if a user and
database are present using the shell? Is there another utility we
should be using?
Thanks in advance,
Jeff
On Sep 2, 2022, at 14:22, Jeffrey Walton <noloader@gmail.com> wrote:
Given the NOTES in the man page, how do we determine if a user and
database are present using the shell? Is there another utility we
should be using?
pg_isready literally only checks that the server can be reached over the connection path (network or sockets), not that any login credentials work. You can use psql do that, though:
psql <connection info) -c "SELECT 1"
... will return an error if the connection information can't be used to successfully log in.
On Friday, September 2, 2022, Jeffrey Walton <noloader@gmail.com> wrote:
Given the NOTES in the man page, how do we determine if a user and
database are present using the shell? Is there another utility we
should be using?
Literally every other shell program that requires logging into the database
will fail if invalid credentials are provided.
Or, you can use good credentials and psql to connect to a known database
and then query the system to learn, without an error, if other roles or
databases exist in the same cluster.
David J.
Jeffrey Walton <noloader@gmail.com> writes:
I have another beginner question. I am trying to use pg_isready to
determine if a database and user are present. The program seems to
always succeed, even when I delete the user or the database.
That's a feature actually. The intended use of pg_isready is to
find out if the server is alive, not whether any particular user
or database name is correct. So it treats responses like "no such
database" as sufficient proof that the server is alive.
As David says, you could try to log in with any other client
software, or connect using known-good parameters and check
the system catalogs.
regards, tom lane
On 9/2/22 17:21, Tom Lane wrote:
Jeffrey Walton <noloader@gmail.com> writes:
I have another beginner question. I am trying to use pg_isready to
determine if a database and user are present. The program seems to
always succeed, even when I delete the user or the database.That's a feature actually. The intended use of pg_isready is to
find out if the server is alive, not whether any particular user
or database name is correct.
Then what's the point of the --username=USERNAME connection option?
--
Angular momentum makes the world go 'round.
On 9/2/22 17:33, Ron wrote:
On 9/2/22 17:21, Tom Lane wrote:
Jeffrey Walton <noloader@gmail.com> writes:
I have another beginner question. I am trying to use pg_isready to
determine if a database and user are present. The program seems to
always succeed, even when I delete the user or the database.That's a feature actually. The intended use of pg_isready is to
find out if the server is alive, not whether any particular user
or database name is correct.Then what's the point of the --username=USERNAME connection option?
My guess so you can specify a 'test' user that you can track in the logs.
--
Adrian Klaver
adrian.klaver@aklaver.com
Ron <ronljohnsonjr@gmail.com> writes:
On 9/2/22 17:21, Tom Lane wrote:
That's a feature actually. The intended use of pg_isready is to
find out if the server is alive, not whether any particular user
or database name is correct.
Then what's the point of the --username=USERNAME connection option?
That's explained in the documentation extract already quoted:
if you do use a bad user/db/password, the server will log a message
about that, since it just sees a failed connection attempt. If you'd
rather not have such chatter in your log, then you want to give
pg_isready valid connection data. But that's incidental to the
purpose of the program.
regards, tom lane
On Fri, Sep 2, 2022 at 5:43 PM Christophe Pettus <xof@thebuild.com> wrote:
On Sep 2, 2022, at 14:22, Jeffrey Walton <noloader@gmail.com> wrote:
Given the NOTES in the man page, how do we determine if a user and
database are present using the shell? Is there another utility we
should be using?pg_isready literally only checks that the server can be reached over the connection path (network or sockets), not that any login credentials work. You can use psql do that, though:
psql <connection info) -c "SELECT 1"
... will return an error if the connection information can't be used to successfully log in.
Now available as a direct replacement for pg_isready :
https://github.com/noloader/pg_check_conn .
Jeff