Обсуждение: Determine if a user and database are available

Поиск
Список
Период
Сортировка

Determine if a user and database are available

От
Jeffrey Walton
Дата:
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



Re: Determine if a user and database are available

От
Christophe Pettus
Дата:

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


Re: Determine if a user and database are available

От
"David G. Johnston"
Дата:
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.

Re: Determine if a user and database are available

От
Tom Lane
Дата:
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



Re: Determine if a user and database are available

От
Ron
Дата:
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.



Re: Determine if a user and database are available

От
Adrian Klaver
Дата:
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



Re: Determine if a user and database are available

От
Tom Lane
Дата:
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



Re: Determine if a user and database are available

От
Jeffrey Walton
Дата:
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