gprestore Fails with “unrecognized password type” on Restore (Advanced Password Check)

Products

• VMware Tanzu Data Intelligence

Issue/Introduction

During a database restore using a backup from another Greenplum cluster, you might encounter errors as below:

ERROR: unrecognized password type: 2 (advanced_password_check.c:553) (SQLSTATE XX000)

ERROR: unrecognized password type: 3 (advanced_password_check.c:553) (SQLSTATE XX000)

Cause

The issue can be reproduced by below steps

DB1: Backup

gpconfig -c password_hash_algorithm -v 'SCRAM-SHA-256'
gpconfig -c shared_preload_libraries -v 'advanced_password_check'
gpstop -ar
psql -c "SHOW password_hash_algorithm;"  # Ensure SCRAM-SHA-256 '

gpadmin=# CREATE USER sha_user WITH PASSWORD 'YourStrongPassword123!';
ERROR:  password is too long
gpadmin=# CREATE USER sha_user WITH PASSWORD 'YourStronsss';
ERROR:  Incorrect password format: number missing, special character missing (needs to be one listed in "!@#$%^&*()_+{}|<>?=")
gpadmin=# CREATE USER sha_user WITH PASSWORD 'YourStronsss!22';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
gpadmin=# select * from pg_authid;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit |
                                                          rolpassword
 | rolvaliduntil | rolresqueue | rolcreaterextgpfd | rolcreaterexthttp | rolcreatewextgpfd | rolresgroup
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+----
----------------------------------------------------------------------------------------------------------------------------------
-+---------------+-------------+-------------------+-------------------+-------------------+-------------
 gpadmin  | t        | t          | t             | t           | t            | t           | t              |           -1 | md5
b44a9b06d576a0b083cd60e5f875cf48
 |               |        6055 | t                 | t                 | t                 |        6438
 sha_user | f        | t          | f             | f           | f            | t           | f              |           -1 | SCR
AM-SHA-256$4096:o50IfrKzsIPK9CYSxVlMDg==$YyoZnRqvtrHcXkmWPNox5C0Sh5vRH8YUaKDSYo2ZH/k=:gFJ1l/yv8TKfkQIbydydXB3wipX6f8Sew53G+OOQKhY=
 |               |        6055 | f                 | f                 | f                 |        6437
(2 rows)

$ gpbackup --dbname gpadmin --backup-dir /mnt/remote_share/
...
20251103:22:35:03 gpbackup:gpadmin:gpdb14:2638841-[INFO]:-Greenplum Database Version = 6.27.5 build commit:cbd73d75d095da399432354af436a366073d4bec
20251103:22:35:03 gpbackup:gpadmin:gpdb14:2638841-[INFO]:-Starting backup of database gpadmin
20251103:22:35:03 gpbackup:gpadmin:gpdb14:2638841-[INFO]:-Backup Timestamp = 20251103223503
...
20251103:22:35:04 gpbackup:gpadmin:gpdb14:2638841-[INFO]:-Beginning cleanup
20251103:22:35:05 gpbackup:gpadmin:gpdb14:2638841-[INFO]:-Cleanup complete
20251103:22:35:05 gpbackup:gpadmin:gpdb14:2638841-[INFO]:-Backup completed successfully

DB2: Restore:

gpconfig -c password_hash_algorithm -v 'SCRAM-SHA-256'
gpconfig -c shared_preload_libraries -v 'advanced_password_check'
gpstop -ar
psql -c "SHOW password_hash_algorithm;"  # Ensure SCRAM-SHA-256 '

$ gprestore --create-db  --timestamp 20251103223503 --with-globals --backup-dir /mnt/nfs_share/ --verbose --on-error-continue
....
....
20251103:22:36:04 gprestore:gpadmin:gpdb11:041840-[INFO]:-Restoring global metadata
20251103:22:36:05 gprestore:gpadmin:gpdb11:041840-[DEBUG]:-Error encountered when executing statement: CREATE ROLE sha_user;
ALTER ROLE sha_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD 'SCRAM-SHA-256$4096:o50IfrKzsIPK9CYSxVlMDg==$YyoZnRqvtrHcXkmWPNox5C0Sh5vRH8YUaKDSYo2ZH/k=:gFJ1l/yv8TKfkQIbydydXB3wipX6f8Sew53G+OOQKhY=' RESOURCE QUEUE pg_default RESOURCE GROUP default_group; Error was: ERROR: unrecognized password type: 3 (advanced_password_check.c:553) (SQLSTATE XX000)20251103:22:36:05 gprestore:gpadmin:gpdb11:041840-[ERROR]:-Encountered 1 errors during metadata restore; see log file /home/gpadmin/gpAdminLogs/gprestore_20251103.log for a list of failed statements.
...
...

Resolution

This is a known issue, and we have fixed the issue on 6.31.1. 

The reason we hit this issue is that advanced_password_check only handles PASSWORD_TYPE_PLAINTEXT=0 and PASSWORD_TYPE_MD5=1 on the versions before 6.31.1, SCRAM-SHA-256 was not supported.

 

Workaround:

Remove advanced_password_check in shared_preload_libraries. And reboot this cluster.

After the restore, add advanced_password_check back in shared_preload_libraries and also reboot again to let this GUC take effect.