Обсуждение: BUG #16746: [PG12.5 psql] Password ignored by connect meta-command

The following bug has been logged on the website:

Bug reference:      16746
Logged by:          Krzysztof Gradek
Email address:      kgradekx@gmail.com
PostgreSQL version: 12.5
Operating system:   Centos
Description:

Hi
I just migrated my app to PG 12.5 and jumped into an issue which didn't
exist in PG12.3: the password provided in connection URI is ignored by
connect meta-command.

Steps to reproduce: 
1. open PG12.5 psql
2. run connect command using connection URI -> instead of opening the
connection I get password prompt.

    $ psql
    psql (12.5)
    Type "help" for help.

    postgres=# \connect
postgresql://ph_quartz_user:password@127.0.0.1/ph_quartz
    Password for user ph_quartz_user:

Same sequence using earlier version:
    $  /usr/pgsql-9.4/bin/psql
    psql (9.4.4, server 12.5)
    WARNING: psql major version 9.4, server major version 12.0.
             Some psql features might not work.
    Type "help" for help.

    postgres=# \connect
postgresql://ph_quartz_user:password@127.0.0.1/ph_quartz
    psql (9.4.4, server 12.5)
    WARNING: psql major version 9.4, server major version 12.0.
             Some psql features might not work.
    You are now connected to database "ph_quartz" as user "ph_quartz_user"
on host "127.0.0.1" at port "5432".

Of course, 9.4 is very old version, but the same test at another env with
PG12.3 succeeded (I could connect).

I have checked various options for the connection string (as described in
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING),
none worked. It looks like the password parameter is simply ignored.
 

Also, I have checked the changes done to PG 12.5 - I found that 3 recent
commits relate to the connect meta-command (src/bin/psql/command.c):


https://github.com/postgres/postgres/commit/1b62d0fb3e50ede570d0d4e4a2be69d5645b48a7#diff-657963643e18c39703a2b162c0d1c002584c16289deff8614165544e4a4470c6


https://github.com/postgres/postgres/commit/94929f1cf6cb3ea070d0919d1303379b525a72a3#diff-657963643e18c39703a2b162c0d1c002584c16289deff8614165544e4a4470c6


https://github.com/postgres/postgres/commit/85c54287af56fe351b53913ea2b81e9d6145f964#diff-657963643e18c39703a2b162c0d1c002584c16289deff8614165544e4a4470c6

I cannot explain where is the issue but IMO the problem stems from these
changes.

Hope, you can help.
BR

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Tom, will need your input here.

Ugh... looks like I broke this in 85c54287a et al.  I had thought that
this was the pre-existing behavior, but after further study I see that
I was mistaken about how PQconnectdbParams() handles parameter
replacement, so I misread what the prior code was really doing.

            regards, tom lane

I wrote:
> Ugh... looks like I broke this in 85c54287a et al.  I had thought that
> this was the pre-existing behavior, but after further study I see that
> I was mistaken about how PQconnectdbParams() handles parameter
> replacement, so I misread what the prior code was really doing.

While I take full responsibility for having messed up here, I note that
the documentation for PQconnectdbParams is seriously misleading on the
point, in that it fails to point out that parameters appearing after
"dbname" will only override the connstring if they supply non-empty
values.  Besides that, it's rather in need of copy-editing, not to mention
some minimal thought about what is a reasonable order to make its points
in.  So I propose the attached docs fix to begin with.

            regards, tom lane

diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 06bd412044..310a886dfb 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -122,36 +122,52 @@ PGconn *PQconnectdbParams(const char * const *keywords,
        <xref linkend="libpq-paramkeywords"/>.
       </para>

-      <para>
-       When <literal>expand_dbname</literal> is non-zero, the
-       <parameter>dbname</parameter> key word value is allowed to be recognized
-       as a connection string. Only the first occurrence of
-       <parameter>dbname</parameter> is expanded this way, any subsequent
-       <parameter>dbname</parameter> value is processed as plain database name. More
-       details on the possible connection string formats appear in
-       <xref linkend="libpq-connstring"/>.
-      </para>
-
       <para>
        The passed arrays can be empty to use all default parameters, or can
-       contain one or more parameter settings. They should be matched in length.
-       Processing will stop at the first <symbol>NULL</symbol> element
+       contain one or more parameter settings. They must be matched in length.
+       Processing will stop at the first <symbol>NULL</symbol> entry
        in the <literal>keywords</literal> array.
+       Also, if the <literal>values</literal> entry associated with a
+       non-<symbol>NULL</symbol> <literal>keywords</literal> entry is
+       <symbol>NULL</symbol> or an empty string, that entry is ignored and
+       processing continues with the next pair of array entries.
+      </para>
+
+      <para>
+       When <literal>expand_dbname</literal> is non-zero, the value for
+       the first <parameter>dbname</parameter> key word is checked to see
+       if it is a <firstterm>connection string</firstterm>.  If so, it
+       is <quote>expanded</quote> into the individual connection
+       parameters extracted from the string.  Only the first occurrence
+       of <parameter>dbname</parameter> is treated in this way; any
+       subsequent <parameter>dbname</parameter> parameter is processed as
+       a plain database name.
+       Details on the possible connection string formats appear in
+       <xref linkend="libpq-connstring"/>.
       </para>

       <para>
-       If  any  parameter is <symbol>NULL</symbol> or an empty string, the corresponding
-       environment variable (see <xref linkend="libpq-envars"/>) is checked.
-       If the  environment  variable is not set either, then the indicated
-       built-in defaults are used.
+       In general these arrays are processed from start to end.  If any
+       key word is repeated, the last value (that is
+       not <symbol>NULL</symbol> or empty) is used.  This rule applies in
+       particular when a key word found in a connection string conflicts
+       with one appearing in the <literal>keywords</literal> array.  Thus,
+       the programmer may determine whether array entries can override or
+       be overridden by values taken from a connection string.  Array
+       entries appearing before an expanded <parameter>dbname</parameter>
+       entry can be overridden by fields of the connection string, and in
+       turn those fields are overridden by array entries appearing
+       after <parameter>dbname</parameter> (but, again, only if those
+       entries supply non-empty values).
       </para>

       <para>
-       In general key words are processed from the beginning of these arrays in index
-       order. The effect of this is that when key words are repeated, the last processed
-       value is retained. Therefore, through careful placement of the
-       <parameter>dbname</parameter> key word, it is possible to determine what may
-       be overridden by a <parameter>conninfo</parameter> string, and what may not.
+       After processing all the array entries and any expanded connection
+       string, any connection parameters that remain unset are filled with
+       default values.  If an unset parameter's corresponding environment
+       variable (see <xref linkend="libpq-envars"/>) is set, its value is
+       used.  If the environment variable is not set either, then the
+       parameter's built-in default value is used.
       </para>

      </listitem>