[pgpool-general: 4994] Re: pgpool handling of User through pool password

[Tatsuo Ishii]

> Hi Tatsuo,
> 
> Thank you for your reply. Could you please point us to the code base so that we can have a look ?

Assuming you are familiar with PostgreSQL's frontend/backend protocol,
I think you need to handle the case like this:

1) ALTER ROLE/ALTER USER query is accepted mainly in SimpleQuery() if
   the protocol is "simple" on.

2) ALTER USER query is accepted mainly in Parse/Bind/Execute() if the
   protocol is "extended" one.

3) You need to wait until PostgreSQL returns "Command Complete"
   message which indicates the command succeeded.

4) In addition to #3, if the command is executed within an explicit
   transaction, you need to wait until the transaction committed.

5) You need to take care of CREAE ROLE/USER and DROP ROLE/USER as
   well.

I'm not sure that I do not miss anything but those are surely
necessary things.

BTW, if PostgreSQL supports event trigger for those SQL, things will
be much easier...

> Also is it possible to create a PR for the same and get it part of the same Pgpool-II source code and release?

If your patch is good, we are looking forward to accepting it.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Thanks and Regards,
> Subhankar
> 
> 
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp] 
> Sent: Tuesday, September 13, 2016 5:03 AM
> To: Chattopadhyay, Subhankar <s.chattopadhyay at sap.com>
> Cc: pgpool-general at pgpool.net; Srivastava, Nishtha <nishtha.srivastava at sap.com>
> Subject: Re: [pgpool-general: 4976] pgpool handling of User through pool password
> 
>> Hi,
>> 
>> We provide PostgreSQL as a service on cloud and manage the database cluster. We have pgpool to handle the client queries.
>> 
>> Currently, as I understand, pgpool has md5 mechanism of authentication where the PostgreSQL users will have to be added in the pool-password file.
>> 
>> Now if an application user creates any additional users from these users, these additional users are not able to connect to PostgreSQL through pgpool as the user password are not registered in the pool password file.
>> 
>> Is there a way pgpool can automatically add these users to pool-password? Is there a hook available for this?
> 
> Unfortunately there's no such a hook is implemented. Probably by
> intercepting "ALTER USER" query in Pgpool-II, you could automatically
> update pool_passwd but it requires non trivial modification to
> Pgpool-II source code.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp