Обсуждение: Question about password character in ECPG's connection string

Hi,

I have question about connection string of ECPG's CONNECT statement.

I'm using postgresql 9.5.17 with ECPG application.
When I trying to connect to the database, I found that some characters as password in ECPG's connection_option cannot
beaccepted.
 
Is the specification of ECPG's connection string (connection_option) different from libpq's one?

I referred the below documents.
- CONNECT of ecpg: https://www.postgresql.org/docs/9.5/ecpg-sql-connect.html
- Connection string of libpq: https://www.postgresql.org/docs/9.5/libpq-connect.html#LIBPQ-CONNSTRING


Example: 
1. Creating database role with password which contains '&'.
   > CREATE ROLE myuser LOGIN PASSWORD 'pass&word';
2. Modifying the pg_hba.conf to use "MD5" as auth-method.
3. pg_ctl reload
4. Connecting from ECPG application with below CONNECT statement.
   EXEC SQL CONNECT "tcp:postgresql://localhost?user=myuser&password=pass&word";
     -> The connection was refused because of password failure.

I thought that the '&' should be percent-encoded in connection string. However, it was also failed.
   EXEC SQL CONNECT "tcp:postgresql://localhost?user=myuser&password=pass%26word";
     -> The connection was refused because of password failure.

On the other hand, the percent-encoded connection string was able to use in psql.
   > psql "postgresql://localhost?user=myuser&password=pass%26word"
     -> I could login as "myuser".

So, I think that the connection string specification of ECPG is different from libpq's one.
Is there the password character's limitation in ECPG?

Regards.
--
Yusuke, Egashira

Hello Yusuke,

Il giorno mar, 27/08/2019 alle 07.29 +0000, Egashira, Yusuke ha
scritto:
[...]
> I referred the below documents.
> - CONNECT of ecpg: 
> https://www.postgresql.org/docs/9.5/ecpg-sql-connect.html
> - Connection string of libpq: 
> https://www.postgresql.org/docs/9.5/libpq-connect.html#LIBPQ-CONNSTRING
[...]

It seems to me that ECPG documentation does not allow specifying
username and/or password in the connection string. The correct syntax
should be:

EXEC SQL CONNECT TO "unix:postgresql://localhost/connectdb" USER
"myuser" IDENTIFIED BY "pass&word"

Bye,
Giuseppe

Hi, Giuseppe,

Thanks to response to my question!

> It seems to me that ECPG documentation does not allow specifying
> username and/or password in the connection string. The correct syntax
> should be:
> 
> EXEC SQL CONNECT TO "unix:postgresql://localhost/connectdb" USER
> "myuser" IDENTIFIED BY "pass&word"

Yes, I could connect to database with "USER" and "IDENTIFIED BY" phrase in CONNECT statement.
However, I could also connect to database with password in connection string when my password does not contains '&'
character.

1. In database, 
   > CREATE ROLE myuser LOGIN PASSWORD 'password';
2. In ECPG application,
   EXEC SQL CONNECT "tcp:postgresql://localhost?user=myuser&password=password";
     -> The connection was succeeded.

This behavior confuse me. 

If user and password cannot write in connection string, what are parameters allowed in "connection_option" ?
I hope I can get this information from the ECPG documentation.

Regards.

--
Yusuke, Egashira.

On 8/27/19 6:18 PM, Egashira, Yusuke wrote:
> Hi, Giuseppe,
> 
> Thanks to response to my question!
> 
>> It seems to me that ECPG documentation does not allow specifying
>> username and/or password in the connection string. The correct syntax
>> should be:
>>
>> EXEC SQL CONNECT TO "unix:postgresql://localhost/connectdb" USER
>> "myuser" IDENTIFIED BY "pass&word"
> 
> Yes, I could connect to database with "USER" and "IDENTIFIED BY" phrase in CONNECT statement.
> However, I could also connect to database with password in connection string when my password does not contains '&'
character.
> 
> 1. In database,
>     > CREATE ROLE myuser LOGIN PASSWORD 'password';
> 2. In ECPG application,
>     EXEC SQL CONNECT "tcp:postgresql://localhost?user=myuser&password=password";
>       -> The connection was succeeded.
> 
> This behavior confuse me.

My guess it that what is happening is:

1) Given password: my&pwd

2)  "tcp:postgresql://localhost?user=myuser&password=password" looks like

  "tcp:postgresql://localhost?user=myuser&password=my&pwd"

and password is parsed on the & and you also end up with an extra 
parameter pwd

Have you tried quoting the password?


> 
> If user and password cannot write in connection string, what are parameters allowed in "connection_option" ?
> I hope I can get this information from the ECPG documentation.
> 
> Regards.
> 
> --
> Yusuke, Egashira.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

> 2)  "tcp:postgresql://localhost?user=myuser&password=password" looks like
>
> "tcp:postgresql://localhost?user=myuser&password=my&pwd"
>
> and password is parsed on the & and you also end up with an extra parameter pwd

Perhaps it helps to URL-encode the & in the password as %26?

Alban Hertroys
--
There is always an exception to always.

On Wed, Aug 28, 2019 at 10:47 PM Alban Hertroys <haramrae@gmail.com> wrote:
> Perhaps it helps to URL-encode the & in the password as %26?

The OP already did without success.
Could it be needed to escape the & with the backslash or single ticks?

Luca

> > Perhaps it helps to URL-encode the & in the password as %26?
> 
> The OP already did without success.

Yes, I already get failed with URL-encode the &.

> Could it be needed to escape the & with the backslash or single ticks?

Thanks. I tested with "pass\&word" and "'pass&word'".
However, them also failed...

My tested ways are followings.
- [ECPG] Connection option with plain string :
  EXEC SQL CONNECT "tcp:postgresql://localhost?user=myuser&password=pass&word"
    -> Failed.
- [ECPG] Connection option with percent-encoded string :
  EXEC SQL CONNECT "tcp:postgresql://localhost?user=myuser&password=pass%26word"
    -> Failed.
- [ECPG] Connection option with backslash escaped string :
  EXEC SQL CONNECT "tcp:postgresql://localhost?user=myuser&password=pass\&word"
    -> Failed. (and gcc claims warning)
- [ECPG] Connection option with single-quoted string :
  EXEC SQL CONNECT "tcp:postgresql://localhost?user=myuser&password='pass&word'"
    -> Failed.
- [ECPG] USING or IDENTIFIED BY phrase :
  EXEC SQL CONNECT "tcp:postgresql://localhost" USER "myuser" USING "pass&word"
  EXEC SQL CONNECT "tcp:postgresql://localhost" USER "myuser" IDENTIFIED BY "pass&word"
    -> Success.
- [psql] Connection option with plain string :
  psql "postgresql://localhost?user=myuser&password=pass&word"
    -> Failed.
- [psql] Connection option with percent-encoded string :
  psql "postgresql://localhost?user=myuser&password=pass%26word"
    -> Success.

According to my tests, I think that the ECPG's connection_option seems not to accept '&' character as password
anyhow...
ECPG CONNECT's connection_option seems to have some restricted characters.
I will use "IDENTIFIED BY" phrase to connect the database in my ECPG application.

The database role's password often contains '&' in our environment.
I hope to this limitation will be documented because it causes confusion.

Regards.
--
Yusuke, Egashira

On Thu, Aug 29, 2019 at 1:08 PM Egashira, Yusuke
<egashira.yusuke@jp.fujitsu.com> wrote:
> According to my tests, I think that the ECPG's connection_option seems not to accept '&' character as password
anyhow...
> ECPG CONNECT's connection_option seems to have some restricted characters.


As far as I understand from

<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/interfaces/ecpg/ecpglib/connect.c;h=ec01f67b61d5e8f0b88f84346451d2135a78b777;hb=HEAD#l603>
unwanted characters are '&' and '='. Even if the connetion string
seems an URL, it is managed in plain text without any sort of
escaping.



> I hope to this limitation will be documented because it causes confusion.

If this is confirmed, I agree this should be documented.

Luca

Luca Ferrari <fluca1978@gmail.com> writes:
> On Thu, Aug 29, 2019 at 1:08 PM Egashira, Yusuke
> <egashira.yusuke@jp.fujitsu.com> wrote:
>> According to my tests, I think that the ECPG's connection_option seems not to accept '&' character as password
anyhow...
>> ECPG CONNECT's connection_option seems to have some restricted characters.

> As far as I understand from
>
<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/interfaces/ecpg/ecpglib/connect.c;h=ec01f67b61d5e8f0b88f84346451d2135a78b777;hb=HEAD#l603>
> unwanted characters are '&' and '='. Even if the connetion string
> seems an URL, it is managed in plain text without any sort of
> escaping.

Yeah, the format of the "options" string is not mentioned at all in the
ECPG documentation, but from looking at the code, it's
    keyword=value[&keyword=value...]
where the keywords are the same option keywords recognized by
PQconnectdbParams.  Also, you can write spaces just before
(but not after) any keyword or value.

Given this, you can't write '=' in a keyword (which is moot because
none of them have '=') and you can't write '&' in a value.

Note that the ECPG documentation does not suggest that you can set
either username or password this way.  I'm inclined to think that it's
a bad idea to do so, even if it seems to work.  At minimum it'd mean
that ecpglib has the wrong idea of what the connection's username is,
which seems likely to bite people someday.

If you're not trying to game the system like that, the set of
characters you might actually need in a value seems fairly
constrained, so the restriction about '&' doesn't seem like a huge
problem.  If we introduced URI-style escaping, we'd just make the
problem of funny characters worse, because then '%' would also become
magic.  An alternative that doesn't risk breaking cases that work
today is to say that you can write '&&' to mean a literal '&', but
that seems kind of icky; there's nothing else that does that.

On the whole I'm content with the way the code works now, but we do
need to document it.

            regards, tom lane