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

[Vlad Novikov]

enable_pool_hba is for host-based authentication, like pg_hba.conf in
PostgreSQL. You can use it if you need it, e.g. to allow some specific
subnets connect to your pgpool-II instance. Changing it won't affect the
pool_passwd functionality.

Regards,
Vlad

On Mon, Sep 12, 2016 at 9:03 PM, Chattopadhyay, Subhankar <
s.chattopadhyay at sap.com> wrote:

> Sorry,
>
>
>
> Is it ?
>
>
>
> enable_pool_hba = off
>
> pool_passwd=’’
>
>
>
>
>
> or only,
>
>
>
> pool_passwd=’’
>
>
>
> Thanks and Regards,
>
> Subhankar
>
>
>
> *From:* Vlad Novikov [mailto:xou.slackware at gmail.com]
> *Sent:* Tuesday, September 13, 2016 9:30 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
>
>
>
> Then in your pgpool.conf file just set pool_passwd=''. That should do the
> thing.
>
>
>
> Regards,
>
> Vlad
>
>
>
> On Mon, Sep 12, 2016 at 8:57 PM, Chattopadhyay, Subhankar <
> s.chattopadhyay at sap.com> wrote:
>
> Hi,
>
>
>
> Thanks. I would definitely try this. I was thinking if it is possible to
> switvh off the authentication at the pgpool layer and let postgres handle
> that. In that case all the request goes through pgpool, only postgres
> verifies them. I was trying to set enable_pool_hba = off in the pgpool.conf
> file.
>
>
>
> But with that when I try to login I get this error.
>
>
>
> psql: ERROR:  MD5 authentication is unsupported in replication and
> master-slave modes.
>
> HINT:  check pg_hba.conf
>
>
>
>
>
> Is there a way I can set the correct configuration so that I can achieve
> the previously mentioned setup.
>
>
>
> Thanks and Regards,
>
> Subhankar
>
>
>
> *From:* Vlad Novikov [mailto:xou.slackware at gmail.com]
> *Sent:* Tuesday, September 13, 2016 9:14 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
>
>
>
> Something like this should work:
>
> ````
>
> USER=$1
>
> PWD=$2
>
> POOL_PASSWD="/etc/pgpool/pool_passwd"
>
> pg_md5 -m -u ${USER} -p ${PWD}
>
>
>
> ENCRYPTED_PWD=$(grep ${USER} ${POOL_PASSWD} | cut -d: -f2)
>
> psql -U postgres -w -c "CREATE ROLE ${USER} PASSWORD '${ENCRYPTED_PWD}'
> CREATEDB CREATEROLE INHERIT LOGIN"
>
> ````
>
> Then you can call it ./createuser.sh joe mypassword (given the script name
> is createuser.sh). CREATE ROLE arguments may differ based on what
> permissions you want to grant.
>
>
>
> Regards,
>
> Vlad
>
>
>
>
>
> On Mon, Sep 12, 2016 at 8:33 PM, Chattopadhyay, Subhankar <
> s.chattopadhyay at sap.com> wrote:
>
> Hi Vlad Novikov,
>
>
>
> Thanks for your reply. As far as I know, pg_md5 is a utility to get the
> md5 hash of a password. Is there any such utility/sql command to insert
> into pool_password also? Could you please elaborate and let me know of this?
>
>
>
> How would the script/query look like from the user’s perspective?
>
>
>
> Thanks and Regards,
>
> Subhankar
>
>
>
> *From:* Vlad Novikov [mailto:xou.slackware at gmail.com]
> *Sent:* Tuesday, September 13, 2016 6:21 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
>
>
>
> How about just write a script that combines CREATE ROLE and pg_md5 and
> tell the customers to use it or call it from a web interface?
>
>
>
> On Mon, Sep 12, 2016 at 3:17 AM, Chattopadhyay, Subhankar <
> s.chattopadhyay at sap.com> wrote:
>
> 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?
>
>
>
>
>
> Thanks
>
> Subhankar
>
>
>
>
>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
>
>
>
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160912/21fbd711/attachment.html>