Thread: 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
anylogin 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.

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?

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
thatany 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