View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000411 | Pgpool-II | Enhancement | public | 2018-07-05 17:48 | 2018-10-20 15:52 |
| Reporter | jhiemer | Assigned To | t-ishii | ||
| Priority | high | Severity | major | Reproducibility | always |
| Status | closed | Resolution | open | ||
| Platform | all | OS | all | OS Version | all |
| Product Version | 3.7.4 | ||||
| Fixed in Version | 3.7.5 | ||||
| Summary | 0000411: Disable authentication in PgPool | ||||
| Description | We are using PgPool in one of our environments, where we have a growing number of users. With md5 authentication it is a huge pain to create users in PostgreSQL and PgPool and additionally we don't see the benefit of doing authentication in PgPool and PostgreSQL. So our question is: is there a technical limitation why we can configure pool_hba.conf with trust, pool_passwd = '' and pg_hba.conf with md5? Currently this seems to be possible, but not with replication enabled. We would be really interested about the intention behind and how much effort it is to patch this. We did some tests with the following configuration (see steps to reproduce). Although we have configured it according to the docs: "Specify the password file name for md5 authentication. Default value is "pool_passwd". Specifying '' (empty) disables the use of password file. See Section 6.2.2 for more details. This parameter can only be set at server start." we get the following error: "ERROR: unable to get password, password file descriptor is NULL" | ||||
| Steps To Reproduce | See configuration files attached. | ||||
| Tags | No tags attached. | ||||
|
|
|
|
|
It's impossible just because of nature of md5 auth (i.e. not an implementation limitation) Please take a look at the FAQ especially this: https://pgpool.net/mediawiki/index.php/FAQ#How_does_pgpool-II_handle_md5_authentication.3F |
|
|
I read this, but I still don't understand why this "is just impossible". If it would be a normal HaProxy in front of the Posgtres Servers, then I would be able to configure md5 in pg_hba.conf and authentication would also work. So why is this different in context of PgPool? The FAQ just describe the process, but not why this is specifically mandatory to do this kind of authentication with PgPool. Could you explain that? |
|
|
The point is, Pgpool-II needs to deal with 2 or more PostgreSQL servers unlike HAProxy. Below is the copy of the FAQ. 1. PostgreSQL and pgpool stores md5(password+username) into pool_password or pg_authid. From now on I denote string "password+username" as "S". 2. When md5 auth is requested, pgpool sends a random number salt "s0" to frontend. 3. Frontend replies back to pgpool with md5(S+s0). 4. pgpool extracts S from pgpool_passwd and calculate md5(S+s0). If 0000003 and 0000004 matches, goes to next step. 5. Each backend sends salt to pgpool. Suppose we have two backends b1 and b2, and salts are s1 and s2. 6. pgpool extracts S from pgpool_passwd and calculate md5(S+s1) and send it to b1. pgpool extracts S from pgpool_passwd and calculate md5(S+s2) and send it to b2. 7. If b1 and b2 agree with the authentication, the whole md5 auth process succeeds. In 0000005, in order for Pgpool-II to return md5(S+s1) to backend1 while md5(S+s2) to backend2, Pgpool-II needs to maintain S, which is stored in pool_passwd. If you want to do this without pool_passwd, you need to allow Pgpool-II to access raw password string, which you will not want to do. |
|
|
Ok I understand but this is just the case, when we are using Load Balancing, right? When are doing native streaming replication on the Postgres Server and no query load balancing for SELECT operations, then we are fine, because that would be the same as having a HaProxy just in front of the active PG node that makes a pass through. One thing I would like to ask though is: why to username and password need to be stored? I imagine the following case: auth request is coming in, md5(S) is created based on the payload, we get the salt back from b1 and b1 and do the auth request to b1 and b2. If it succeeds, connection is put into connection cache. For the initial creation of a new connection this would increase the amount of load due to the md5() creation, but it would also reduce file access etc. >If you want to do this without pool_passwd, you need to allow Pgpool-II to access raw password string, which you will not want to do. I am not sure if I get this as well. PgPool is accessing the raw password also in the pool_password scenario, or am I mistaken? How is the entry from pool_passwd picked up? Only by username? |
|
|
> Ok I understand but this is just the case, when we are using Load Balancing, right? When are doing native streaming replication on the Postgres Server and no query load balancing for SELECT operations, then we are fine, because that would be the same as having a HaProxy just in front of the active PG node that makes a pass through. No. You are using "raw" mode of Pgpool-II (that is: replication_mode = off, master_slave_mode = off). Even with this mode, Pgpool-II needs pool_passwd because you have more than 1 backend. > One thing I would like to ask though is: why to username and password need to be stored? I imagine the following case: auth request is coming in, md5(S) is created based on the payload, we get the salt back from b1 and b1 and do the auth request to b1 and b2. If it succeeds, connection is put into connection cache. Sorry, the FAQ was wrong. what we store is md5(username+password), not username+password. > auth request is coming in, md5(S) is created based on the payload No. Client and Pgpool-II creates md5(b0+S), where bo is the random salt generated by Pgpool-II and S is md5(username+password). If Pgpool-II do not have pool_passwd, Pgpool-II needs to calculate S from md5(b0+S), which is impossible (at least in a cheap way). And Pgpool-II cannot calculate md5(b1+S) and md5(b2+S) without knowing S. |
|
|
I realized that you are using Pgpool-II in raw mode. Yes, in this mode Pgpool-II actually only connects to master PostgreSQL as you said. So this should be treated as if there's only 1 backend: no pool_passwd is required in this case. But I found that Pgpool-II actually did not treat raw mode correctly. Can you please try attached diff (auth.diff)? After applying the patch, you should be able to set the auth method in pool_hba.conf to "trust" (thus no pool_passwd is required). Or even you could disable enable_pool_hba if you like. |
|
|
This is really good, works like expected. What do you think how much overhead it generates to create the md5(username+password) hash on runtime? Could this be some kind of feature to add, when user is willingly accepting this because he/she enable connection_cache? Then you could benefit of the md5 authentication mechanism provided by PgPool and the query load balancing without having to synch in pool_passwd and Postgres. From our perspective this would be a really nice feature. |
|
|
> What do you think how much overhead it generates to create the md5(username+password) hash on runtime? Well, for Pgpool-II that's not possible because clients only send md5(md5(username+password)+b) to Pgpool-II. So there's no way for Pgpool-II to extract md5(username+password), or username+password. (b is a random salt generated by Pgpool-II). BTW, the feature is not necessary in raw mode. Do you want to use streaming replication mode (that is, actually master_slave_mode = on and master_slave_sub_mode = 'stream') instead? |
|
|
Yes actually we had streaming replication with master/slave on and mode stream configured before. Would that also work in this context? As we are in the context of authentication for Postgres. For Postgres 10 it will be changed to SCRAM. Will this be adapted/applied in PgPool as well? |
|
|
> Yes actually we had streaming replication with master/slave on and mode stream configured before. Would that also work in this context? No. > Will this be adapted/applied in PgPool as well? Yes, Muhammad Usama is working on this. |
|
|
Can we close this? |
|
|
Yes |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2018-07-05 17:48 | jhiemer | New Issue | |
| 2018-07-05 17:48 | jhiemer | File Added: pgpool.conf | |
| 2018-07-05 17:48 | jhiemer | File Added: pool_hba.conf | |
| 2018-07-05 17:59 | t-ishii | Note Added: 0002095 | |
| 2018-07-05 18:11 | jhiemer | Note Added: 0002097 | |
| 2018-07-05 21:44 | t-ishii | Note Added: 0002099 | |
| 2018-07-05 22:02 | jhiemer | Note Added: 0002101 | |
| 2018-07-06 08:05 | t-ishii | Note Added: 0002103 | |
| 2018-07-06 15:30 | t-ishii | File Added: auth.diff | |
| 2018-07-06 15:30 | t-ishii | Note Added: 0002106 | |
| 2018-07-06 20:21 | jhiemer | Note Added: 0002107 | |
| 2018-07-06 23:59 | t-ishii | Note Added: 0002108 | |
| 2018-07-07 21:29 | jhiemer | Note Added: 0002109 | |
| 2018-07-09 14:31 | t-ishii | Note Added: 0002110 | |
| 2018-07-09 14:31 | t-ishii | Assigned To | => t-ishii |
| 2018-07-09 14:31 | t-ishii | Status | new => assigned |
| 2018-10-20 11:26 | t-ishii | Note Added: 0002222 | |
| 2018-10-20 11:38 | t-ishii | Status | assigned => feedback |
| 2018-10-20 11:45 | t-ishii | Fixed in Version | => 3.7.5 |
| 2018-10-20 15:06 | jhiemer | Note Added: 0002223 | |
| 2018-10-20 15:06 | jhiemer | Status | feedback => assigned |
| 2018-10-20 15:52 | t-ishii | Status | assigned => closed |
