Обсуждение: 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.
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 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