Thread: dropdb utility command prompts for password despite valid .pgpass file in home directory

Problem:

I'm trying to use the dropdb utility command in non-interactive mode to drop an existing database (with active connections) in a test environment, but it keeps prompting me for the password.

Here's what I'm running with `.pgpass` with valid permissions (0600) and location (users home directory, though I've also explicitly set the path using PGPASSFILE envvar). This pass file was validated to work with other utility commands like pg_dump.

Note: "myserverhost" is not a real host - I've just redacted our actual hostname for this bug report.

Command:

```
dropdb --host <myserverhost.amazonaws.com> -w <mydbname>
```

Response:

```
dropdb: error: could not connect to database template1: connection to server at "<myserverhost.amazonaws.com>" (<redacted IP>), port 5432 failed: fe_sendauth: no password supplied
```

Note the `template1` error despite me passing the correct database name.

To confirm that nothing is wrong with my `.pgpass` file, I ran `pg_dump` and `pg_restore` and they ran fine (without any assistance of envvars like PGPASSWORD). `dropdb` also seems to work fine if I provide an explicit password through the environment.

What I expect to happen:

The command should read from my valid `.pgpass` file and drop the database

Thank you.

Additional Information

Postgres Version:

psql (PostgreSQL) 13.7 (Debian 13.7-1.pgdg100+1)

System Information

On a AWS Ubuntu VM

Output of `lscpu`

Architecture:        x86_64
CPU op-mode(s):      32-bit, 64-bit
Byte Order:          Little Endian
Address sizes:       48 bits physical, 48 bits virtual
CPU(s):              2
On-line CPU(s) list: 0,1
Thread(s) per core:  2
Core(s) per socket:  1
Socket(s):           1
NUMA node(s):        1
Vendor ID:           AuthenticAMD
CPU family:          23
Model:               1
Model name:          AMD EPYC 7571
Stepping:            2
CPU MHz:             2199.998
BogoMIPS:            4399.99
Hypervisor vendor:   KVM
Virtualization type: full
L1d cache:           32K
L1i cache:           64K
L2 cache:            512K
L3 cache:            8192K
Hi,

On Thu, Jun 23, 2022 at 03:21:52PM -0400, Alan Lin wrote:
> Problem:
>
> I'm trying to use the dropdb utility command in non-interactive mode to
> drop an existing database (with active connections) in a test environment,
> but it keeps prompting me for the password.
>
> Here's what I'm running with `.pgpass` with valid permissions (0600) and
> location (users home directory, though I've also explicitly set the path
> using PGPASSFILE envvar). This pass file was validated to work with other
> utility commands like pg_dump.
>
> Note: "myserverhost" is not a real host - I've just redacted our actual
> hostname for this bug report.
>
> Command:
>
> ```
> dropdb --host <myserverhost.amazonaws.com> -w <mydbname>
> ```
>
> Response:
>
> ```
> dropdb: error: could not connect to database template1: connection to
> server at "<myserverhost.amazonaws.com>" (<redacted IP>), port 5432 failed:
> fe_sendauth: no password supplied
> ```
>
> Note the `template1` error despite me passing the correct database name.

This is not a bug.  As mentioned in the documentation for the
maintenance-db option
(https://www.postgresql.org/docs/current/app-dropdb.html):

> --maintenance-db=dbname
> Specifies the name of the database to connect to in order to drop the target
> database. If not specified, the postgres database will be used; if that does
> not exist (or is the database being dropped), template1 will be used. This
> can be a connection string. If so, connection string parameters will override
> any conflicting command line options.

You should either specify a maintenance-db option for which there is a valid
match in your .pgass file, or adapt your .pgpass file to match the default
maintenance databases.