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

[Srivastava, Nishtha]

Hi,

Regarding the setup -
“If it is possible to switch off the authentication at the pgpool layer and let postgres handle that.
In that case all the requests go through pgpool, only postgres verifies them.”

Following settings in pgpool.conf is not working for us:
enable_pool_hba = off
pool_passwd = ‘’

It gives error:
MD5 authentication is unsupported in replication, master-slave and parallel mode

As is discussed in this thread http://www.sraoss.jp/pipermail/pgpool-general/2015-November/004299.html
And http://pgpool.net/mediawiki/index.php/FAQ

Various combination of settings in pg_hba.conf, pool_hba.conf and pool_password and result (or error) of such combinations:

pg_hba.conf

pool_hba.conf

Entry in pool_passwd

Result/error

md5

md5

Yes

md5 auth

md5

md5

No

"MD5" authentication with pgpool failed for user "XX"

md5

trust

Yes/no

MD5 authentication is unsupported in replication, master-slave and parallel mode

trust

md5

Yes

no auth

trust

md5

No

"MD5" authentication with pgpool failed for user "XX"

trust

trust

Yes/no

no auth



Is it somehow possible to achieve the above mentioned setup (to bypass authentication at pgpool layer) ?

Because for us, following is not working:
enable_pool_hba = off
pool_passwd = ‘’



Regards,
Nishtha

From: Chattopadhyay, Subhankar
Sent: Tuesday, September 13, 2016 9:48 AM
To: Vlad Novikov <xou.slackware at gmail.com<mailto:xou.slackware at gmail.com>>
Cc: pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>; Srivastava, Nishtha <nishtha.srivastava at sap.com<mailto:nishtha.srivastava at sap.com>>
Subject: RE: [pgpool-general: 4976] pgpool handling of User through pool password

Hi,

I just tried setting it.

pool_passwd = ''  (two single quotes)

Now when I try to connect, I get this error.
$ psql -h 10.3.6.24 -p 9999 -U root -d uaa
psql: ERROR:  unable to get password, password file descriptor is NULL

Thanks and Regards,
Subhankar

From: Vlad Novikov [mailto:xou.slackware at gmail.com]
Sent: Tuesday, September 13, 2016 9:39 AM
To: Chattopadhyay, Subhankar <s.chattopadhyay at sap.com<mailto:s.chattopadhyay at sap.com>>
Cc: pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>; Srivastava, Nishtha <nishtha.srivastava at sap.com<mailto:nishtha.srivastava at sap.com>>
Subject: Re: [pgpool-general: 4976] pgpool handling of User through pool password

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<mailto: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<mailto:xou.slackware at gmail.com>]
Sent: Tuesday, September 13, 2016 9:30 AM

To: Chattopadhyay, Subhankar <s.chattopadhyay at sap.com<mailto:s.chattopadhyay at sap.com>>
Cc: pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>; Srivastava, Nishtha <nishtha.srivastava at sap.com<mailto: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<mailto: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<mailto:xou.slackware at gmail.com>]
Sent: Tuesday, September 13, 2016 9:14 AM

To: Chattopadhyay, Subhankar <s.chattopadhyay at sap.com<mailto:s.chattopadhyay at sap.com>>
Cc: pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>; Srivastava, Nishtha <nishtha.srivastava at sap.com<mailto: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<mailto: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<mailto:xou.slackware at gmail.com>]
Sent: Tuesday, September 13, 2016 6:21 AM
To: Chattopadhyay, Subhankar <s.chattopadhyay at sap.com<mailto:s.chattopadhyay at sap.com>>
Cc: pgpool-general at pgpool.net<mailto:pgpool-general at pgpool.net>; Srivastava, Nishtha <nishtha.srivastava at sap.com<mailto: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<mailto: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<mailto: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/20160913/2f6f10d0/attachment-0001.html>