View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000699 | Pgpool-II | Bug | public | 2021-03-23 04:48 | 2021-06-15 12:46 |
| Reporter | G79189 | Assigned To | t-ishii | ||
| Priority | normal | Severity | major | Reproducibility | always |
| Status | closed | Resolution | open | ||
| Product Version | 4.2.2 | ||||
| Summary | 0000699: SCRAM authentication failed for username having more than 32 characters | ||||
| Description | Pgpool authentication failing with scram-sha-256 mechanism. Error in logs seem to indicate that username is being trimmed down to 32 chars. psql -Uadmin@vmw-hcs-3ce2c6a1-b771-4149-a79c-b7c54c78d4c1-2743-0-postgres -h 127.0.0.1 -p 6432 fdb psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. syslog: Mar 22 19:31:08 vmw-hcs-3ce2c6a1-b771-4149-a79c-b7c54c78d4c1-2743-0-node-1 pgpool[29075]: [12-1] 2021-03-22 19:31:08: pid 29075: FATAL: SCRAM authentication failed Mar 22 19:31:08 vmw-hcs-3ce2c6a1-b771-4149-a79c-b7c54c78d4c1-2743-0-node-1 pgpool[29075]: [12-2] 2021-03-22 19:31:08: pid 29075: DETAIL: pool_passwd file does not contain an entry for "admin@vmw-hcs-3ce2c6a1-b771-4149" pool_passwd: admin@vmw-hcs-3ce2c6a1-b771-4149-a79c-b7c54c78d4c1-2743-0-postgres:AESl2k6o2GTNIqOGFYqcGLobahBVd9rKnzy4Yn2DcRMrfg7VGyB7pqQ7CX0pEkR4x7L pool_hba.conf: # "local" is for Unix domain socket connections only local all all scram-sha-256 # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 | ||||
| Steps To Reproduce | - Install pgpool-II-4.2.2 - Configure pgpool.conf to use below configuration (along with appropriate backend server) backend_clustering_mode = 'raw' enable_pool_hba = on - Configure pool_hba.conf to use 'scram-sha-256' authentication type - Configure pool_password echo 'uyDAHr35VIftIEX4RyQAdEH9sXY1DWMmiL4UiRCvlBA=' > ~/.pgpoolkey chmod 600 ~/.pgpoolkey /usr/local/pgpool/bin/pg_enc -m -k ~/.pgpoolkey -f /usr/local/pgpool/etc/pgpool.conf -u 'admin@vmw-hcs-3ce2c6a1-b771-4149-a79c-b7c54c78d4c1-2743-0-postgres' -p | ||||
| Additional Information | Here backend node is Azure PostgreSQL database where username is expected in the format username@hostname . | ||||
| Tags | pgpool authentication | ||||
|
|
Can you share pgpool.log with log_min_messages = debug1? I failed to find any place where pgpool cuts off user name in 32 bytes. The only possibility is you are using old v2 protocol but I don't believe it... BTW, the user name is very long and PostgreSQL will trim down to 63 bytes unless Azure PostgreSQL changes the limitation. |
|
|
Please find attached pgpool log with log_min_messages = debug1. What do we mean by old v2 protocol ? I see below in logs. Trying to understand why it is logging Protocol Major: 3 and Protocol Major: 2. 2021-03-23 05:03:51: pid 24240: DETAIL: Protocol Major: 3 Minor: 0 database: fdb user: admin@vmw-hcs-3ce2c6a1-b771-4149-a79c-b7c54c78d4c1-2743-0-postgres 2021-03-23 05:03:51: pid 24240: LOCATION: child.c:744 .. .. 2021-03-23 05:03:51: pid 24240: DETAIL: Protocol Major: 2 Minor: 0 database: fdb user: admin@vmw-hcs-3ce2c6a1-b771-4149 2021-03-23 05:03:51: pid 24240: LOCATION: child.c:744 2021-03-23 05:03:51: pid 24240: FATAL: SCRAM authentication failed 2021-03-23 05:03:51: pid 24240: DETAIL: pool_passwd file does not contain an entry for "admin@vmw-hcs-3ce2c6a1-b771-4149" 2021-03-23 05:03:51: pid 24240: LOCATION: pool_hba.c:1273 Actual username in database is admin (much lesser than 63 chars). Azure expects username@hostname while connecting, where hostname is proxy gateway (it think). |
|
|
> What do we mean by old v2 protocol ? I see below in logs. Trying to understand why it is logging Protocol Major: 3 and Protocol Major: 2. At first your client requests protocol Major 3 (v3) but Azure backend replied with 'E' (error). Then pgpool forward the error message to the client. The client decided to fall back to old v2 protocol. Unfortunately in v2 protocol the user name is limited to 32 bytes. So you see the error. You'd better to consult Azure support. |
|
|
> Azure expects username@hostname while connecting, where hostname is proxy gateway (it think). I think it's specific to Azure and it does not follow the standard PostgreSQL frontend/backend protocol. Pgpool-II is designed to follow the standard PostgreSQL frontend/backend protocol. |
|
|
I got below feedback from MSFT support team: The authentication method SCRAM-SHA-256 is not supported on our service nor md5 but only password. If the goal here is to secure the password while establishing the connection, you can do that using SSL encryption with the server root trusted certificate and use that certificate in the connection string to ensure that connections is securely made. Sounds like SCRAM-SHA-256 is not supported with Azure PostgreSQL. Also, it appears the protocol version error is mileading here and switching to supported password method along with SSL support resolved the issue for me. Thanks for looking into this issue. I am good to resolve this as not a bug. |
|
|
Thanks for confirmation. Status is changed to "resolved". |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2021-03-23 04:48 | G79189 | New Issue | |
| 2021-03-23 04:48 | G79189 | Tag Attached: pgpool authentication | |
| 2021-03-23 09:27 | t-ishii | Assigned To | => t-ishii |
| 2021-03-23 09:27 | t-ishii | Status | new => assigned |
| 2021-03-23 09:33 | t-ishii | Note Added: 0003780 | |
| 2021-03-23 09:33 | t-ishii | Status | assigned => feedback |
| 2021-03-23 14:12 | G79189 | File Added: pgpool-2021-03-23_045851.log | |
| 2021-03-23 14:12 | G79189 | Note Added: 0003781 | |
| 2021-03-23 14:12 | G79189 | Status | feedback => assigned |
| 2021-03-23 14:37 | t-ishii | Note Added: 0003782 | |
| 2021-03-23 14:38 | t-ishii | Status | assigned => feedback |
| 2021-03-23 14:56 | t-ishii | Note Added: 0003783 | |
| 2021-03-26 12:28 | G79189 | Note Added: 0003784 | |
| 2021-03-26 12:28 | G79189 | Status | feedback => assigned |
| 2021-03-27 11:43 | t-ishii | Note Added: 0003786 | |
| 2021-03-27 11:43 | t-ishii | Status | assigned => resolved |
| 2021-06-15 12:46 | administrator | Status | resolved => closed |
