Subject: Re: Should rolpassword be toastable?

From: "Jonathan S. Katz" <[email-masked]>
Date: Fri, 20 Sep 2024 10:06:28 -0400

On 9/20/24 1:23 AM, Michael Paquier wrote:
> On Thu, Sep 19, 2024 at 09:46:00PM -0500, Nathan Bossart wrote:
>> On Thu, Sep 19, 2024 at 07:37:55PM -0400, Jonathan S. Katz wrote:
>>>> Shouldn't we enforce the limit in every case in encrypt_password,
>>>> not just this one?  (I do agree that encrypt_password is an okay
>>>> place to enforce it.)
>>
>> Yeah, that seems like a good idea.  I've attached a more fleshed-out patch
>> set that applies the limit in all cases.
> 
> Not sure.  Is this really something we absolutely need?  Sure, this
> generates a better error when inserting a record too long to
> pg_authid, but removing the toast relation is enough to avoid the
> problems one would see when authenticating.  Not sure if this argument
> is enough to count as an objection, just sharing some doubts :)

The errors from lack of TOAST are confusing to users. Why can't we have 
a user friendly error here?

Jonathan

---

From: Nathan Bossart <[email-masked]>
Date: Fri, 20 Sep 2024 11:09:51 -0500

On Fri, Sep 20, 2024 at 10:06:28AM -0400, Jonathan S. Katz wrote:
> The errors from lack of TOAST are confusing to users. Why can't we have a
> user friendly error here?

If I wanted to argue against adding a user-friendly error, I'd point out
that it's highly unlikely anyone is actually trying to use super long
hashes unless they are trying to break things, and it's just another
arbitrary limit that we'll need to maintain/enforce.  But on the off-chance
that someone is building a custom driver that generates long hashes for
whatever reason, I'd imagine that a clear error would be more helpful than
"row is too big."

Here is a v3 patch set that fixes the test comment and a compiler warning
in cfbot.

-- 
nathan
>From bb3aad9105b1997bc088403437ac6294e22809d9 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Thu, 19 Sep 2024 20:59:10 -0500
Subject: [PATCH v3 1/2] place limit on password hash length

---
 src/backend/libpq/crypt.c              | 60 ++++++++++++++++++--------
 src/include/libpq/crypt.h              | 10 +++++
 src/test/regress/expected/password.out |  7 +++
 src/test/regress/sql/password.sql      |  4 ++
 4 files changed, 63 insertions(+), 18 deletions(-)

diff --git a/src/backend/libpq/crypt.c b/src/backend/libpq/crypt.c
index 629e51e00b..753e5c11da 100644
--- a/src/backend/libpq/crypt.c
+++ b/src/backend/libpq/crypt.c
@@ -116,7 +116,7 @@ encrypt_password(PasswordType target_type, const char *role,
 				 const char *password)
 {
 	PasswordType guessed_type = get_password_type(password);
-	char	   *encrypted_password;
+	char	   *encrypted_password = NULL;
 	const char *errstr = NULL;
 
 	if (guessed_type != PASSWORD_TYPE_PLAINTEXT)
@@ -125,32 +125,56 @@ encrypt_password(PasswordType target_type, const char *role,
 		 * Cannot convert an already-encrypted password from one format to
 		 * another, so return it as it is.
 		 */
-		return pstrdup(password);
+		encrypted_password = pstrdup(password);
 	}
-
-	switch (target_type)
+	else
 	{
-		case PASSWORD_TYPE_MD5:
-			encrypted_password = palloc(MD5_PASSWD_LEN + 1);
+		switch (target_type)
+		{
+			case PASSWORD_TYPE_MD5:
+				encrypted_password = palloc(MD5_PASSWD_LEN + 1);
 
-			if (!pg_md5_encrypt(password, role, strlen(role),
-								encrypted_password, &errstr))
-				elog(ERROR, "password encryption failed: %s", errstr);
-			return encrypted_password;
+				if (!pg_md5_encrypt(password, role, strlen(role),
+									encrypted_password, &errstr))
+					elog(ERROR, "password encryption failed: %s", errstr);
+				break;
 
-		case PASSWORD_TYPE_SCRAM_SHA_256:
-			return pg_be_scram_build_secret(password);
+			case PASSWORD_TYPE_SCRAM_SHA_256:
+				encrypted_password = pg_be_scram_build_secret(password);
+				break;
 
-		case PASSWORD_TYPE_PLAINTEXT:
-			elog(ERROR, "cannot encrypt password with 'plaintext'");
+			case PASSWORD_TYPE_PLAINTEXT:
+				elog(ERROR, "cannot encrypt password with 'plaintext'");
+				break;
+		}
 	}
 
+	Assert(encrypted_password);
+
 	/*
-	 * This shouldn't happen, because the above switch statements should
-	 * handle every combination of source and target password types.
+	 * Valid password hashes may be very long, but we don't want to store
+	 * anything that might need out-of-line storage, since de-TOASTing won't
+	 * work during authentication because we haven't selected a database yet
+	 * and cannot read pg_class. 256 bytes should be more than enough for all
+	 * practical use, so fail for anything longer.
 	 */
-	elog(ERROR, "cannot encrypt password to requested type");
-	return NULL;				/* keep compiler quiet */
+	if (encrypted_password &&	/* keep compiler quiet */
+		strlen(encrypted_password) > MAX_ENCRYPTED_PASSWORD_LEN)
+	{
+		/*
+		 * We don't expect any of our own hashing routines to produce hashes
+		 * that are too long.
+		 */
+		Assert(guessed_type != PASSWORD_TYPE_PLAINTEXT);
+
+		ereport(ERROR,
+				(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+				 errmsg("encrypted password is too long"),
+				 errdetail("Encrypted passwords must be no longer than %d characters.",
+						   MAX_ENCRYPTED_PASSWORD_LEN)));
+	}
+
+	return encrypted_password;
 }
 
 /*
diff --git a/src/include/libpq/crypt.h b/src/include/libpq/crypt.h
index f744de4d20..a2c99cd16a 100644
--- a/src/include/libpq/crypt.h
+++ b/src/include/libpq/crypt.h
@@ -15,6 +15,16 @@
 
 #include "datatype/timestamp.h"
 
+/*
+ * Valid password hashes may be very long, but we don't want to store anything
+ * that might need out-of-line storage, since de-TOASTing won't work during
+ * authentication because we haven't selected a database yet and cannot read
+ * pg_class.  256 bytes should be more than enough for all practical use, and
+ * our own password encryption routines should never produce hashes longer than
+ * this.
+ */
+#define MAX_ENCRYPTED_PASSWORD_LEN (256)
+
 /*
  * Types of password hashes or secrets.
  *
diff --git a/src/test/regress/expected/password.out b/src/test/regress/expected/password.out
index 924d6e001d..8ab995a162 100644
--- a/src/test/regress/expected/password.out
+++ b/src/test/regress/expected/password.out
@@ -127,6 +127,13 @@ SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassw
  regress_passwd_sha_len2 | t
 (3 rows)
 
+-- Test that valid hashes that are too long are rejected
+CREATE ROLE regress_passwd10 PASSWORD 'SCRAM-SHA-256$00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004096:NuDacwYSUxeOeFUEf3ivTQ==$Wgvq3OCYrJI6eUfvKlAzn4p/j3mzgWzXbVnWeFK1qhY=:r1qSP0j2QojCjLpFUjI0i6ckInvxJDKoyWnN3zF8WCM=';
+ERROR:  encrypted password is too long
+DETAIL:  Encrypted passwords must be no longer than 256 characters.
+ALTER ROLE regress_passwd9 PASSWORD 'SCRAM-SHA-256$00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004096:NuDacwYSUxeOeFUEf3ivTQ==$Wgvq3OCYrJI6eUfvKlAzn4p/j3mzgWzXbVnWeFK1qhY=:r1qSP0j2QojCjLpFUjI0i6ckInvxJDKoyWnN3zF8WCM=';
+ERROR:  encrypted password is too long
+DETAIL:  Encrypted passwords must be no longer than 256 characters.
 DROP ROLE regress_passwd1;
 DROP ROLE regress_passwd2;
 DROP ROLE regress_passwd3;
diff --git a/src/test/regress/sql/password.sql b/src/test/regress/sql/password.sql
index bb82aa4aa2..442c903c00 100644
--- a/src/test/regress/sql/password.sql
+++ b/src/test/regress/sql/password.sql
@@ -95,6 +95,10 @@ SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassw
     WHERE rolname LIKE 'regress_passwd_sha_len%'
     ORDER BY rolname;
 
+-- Test that valid hashes that are too long are rejected
+CREATE ROLE regress_passwd10 PASSWORD 'SCRAM-SHA-256$00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004096:NuDacwYSUxeOeFUEf3ivTQ==$Wgvq3OCYrJI6eUfvKlAzn4p/j3mzgWzXbVnWeFK1qhY=:r1qSP0j2QojCjLpFUjI0i6ckInvxJDKoyWnN3zF8WCM=';
+ALTER ROLE regress_passwd9 PASSWORD 'SCRAM-SHA-256$00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004096:NuDacwYSUxeOeFUEf3ivTQ==$Wgvq3OCYrJI6eUfvKlAzn4p/j3mzgWzXbVnWeFK1qhY=:r1qSP0j2QojCjLpFUjI0i6ckInvxJDKoyWnN3zF8WCM=';
+
 DROP ROLE regress_passwd1;
 DROP ROLE regress_passwd2;
 DROP ROLE regress_passwd3;
-- 
2.39.5 (Apple Git-154)

>From 7e1736701e270d40cc1009ff4760317a8a366663 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Thu, 19 Sep 2024 21:09:30 -0500
Subject: [PATCH v3 2/2] remove pg_authid's TOAST table

XXX: NEEDS CATVERSION BUMP
---
 src/backend/catalog/catalog.c              |  4 +---
 src/include/catalog/pg_authid.h            |  2 --
 src/test/regress/expected/create_index.out | 14 +++++++-------
 src/test/regress/expected/misc_sanity.out  |  5 ++++-
 src/test/regress/expected/tablespace.out   | 12 ++++++------
 src/test/regress/sql/create_index.sql      | 10 +++++-----
 src/test/regress/sql/misc_sanity.sql       |  2 ++
 src/test/regress/sql/tablespace.sql        |  8 ++++----
 8 files changed, 29 insertions(+), 28 deletions(-)

diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index 6c39434a30..d6b07a7865 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -300,9 +300,7 @@ IsSharedRelation(Oid relationId)
 		relationId == TablespaceOidIndexId)
 		return true;
 	/* These are their toast tables and toast indexes */
-	if (relationId == PgAuthidToastTable ||
-		relationId == PgAuthidToastIndex ||
-		relationId == PgDatabaseToastTable ||
+	if (relationId == PgDatabaseToastTable ||
 		relationId == PgDatabaseToastIndex ||
 		relationId == PgDbRoleSettingToastTable ||
 		relationId == PgDbRoleSettingToastIndex ||
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index e08863f78a..e846d75731 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -55,8 +55,6 @@ CATALOG(pg_authid,1260,AuthIdRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_OID(284
  */
 typedef FormData_pg_authid *Form_pg_authid;
 
-DECLARE_TOAST_WITH_MACRO(pg_authid, 4175, 4176, PgAuthidToastTable, PgAuthidToastIndex);
-
 DECLARE_UNIQUE_INDEX(pg_authid_rolname_index, 2676, AuthIdRolnameIndexId, pg_authid, btree(rolname name_ops));
 DECLARE_UNIQUE_INDEX_PKEY(pg_authid_oid_index, 2677, AuthIdOidIndexId, pg_authid, btree(oid oid_ops));
 
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index cf6eac5734..d3358dfc39 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2664,10 +2664,10 @@ REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation
 ERROR:  cannot reindex system catalogs concurrently
 REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index
 ERROR:  cannot reindex system catalogs concurrently
--- These are the toast table and index of pg_authid.
-REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table
+-- These are the toast table and index of pg_database.
+REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast table
 ERROR:  cannot reindex system catalogs concurrently
-REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index
+REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toast index
 ERROR:  cannot reindex system catalogs concurrently
 REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM
 ERROR:  cannot reindex system catalogs concurrently
@@ -2974,10 +2974,10 @@ ERROR:  must be owner of schema schema_to_reindex
 RESET ROLE;
 GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser;
 SET SESSION ROLE regress_reindexuser;
-REINDEX TABLE pg_toast.pg_toast_1260;
-ERROR:  permission denied for table pg_toast_1260
-REINDEX INDEX pg_toast.pg_toast_1260_index;
-ERROR:  permission denied for index pg_toast_1260_index
+REINDEX TABLE pg_toast.pg_toast_1262;
+ERROR:  permission denied for table pg_toast_1262
+REINDEX INDEX pg_toast.pg_toast_1262_index;
+ERROR:  permission denied for index pg_toast_1262_index
 -- Clean up
 RESET ROLE;
 REVOKE USAGE ON SCHEMA pg_toast FROM regress_reindexuser;
diff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out
index 45e7492877..b032a3f476 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -40,6 +40,8 @@ WHERE refclassid = 0 OR refobjid = 0 OR
 -- 2. pg_largeobject and pg_largeobject_metadata.  Large object catalogs
 -- and toast tables are mutually exclusive and large object data is handled
 -- as user data by pg_upgrade, which would cause failures.
+-- 3. pg_authid, since its toast table cannot be accessed when it would be
+-- needed, i.e., during authentication before we've selected a database.
 SELECT relname, attname, atttypid::regtype
 FROM pg_class c JOIN pg_attribute a ON c.oid = attrelid
 WHERE c.oid < 16384 AND
@@ -53,12 +55,13 @@ ORDER BY 1, 2;
  pg_attribute            | attfdwoptions | text[]
  pg_attribute            | attmissingval | anyarray
  pg_attribute            | attoptions    | text[]
+ pg_authid               | rolpassword   | text
  pg_class                | relacl        | aclitem[]
  pg_class                | reloptions    | text[]
  pg_class                | relpartbound  | pg_node_tree
  pg_largeobject          | data          | bytea
  pg_largeobject_metadata | lomacl        | aclitem[]
-(9 rows)
+(10 rows)
 
 -- system catalogs without primary keys
 --
diff --git a/src/test/regress/expected/tablespace.out b/src/test/regress/expected/tablespace.out
index 9aabb85349..dd535d41a3 100644
--- a/src/test/regress/expected/tablespace.out
+++ b/src/test/regress/expected/tablespace.out
@@ -51,13 +51,13 @@ ERROR:  cannot move system relation "pg_authid_rolname_index"
 REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid;
 ERROR:  cannot reindex system catalogs concurrently
 -- toast relations, fail
-REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1260_index;
-ERROR:  cannot move system relation "pg_toast_1260_index"
-REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1260_index;
+REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1262_index;
+ERROR:  cannot move system relation "pg_toast_1262_index"
+REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1262_index;
 ERROR:  cannot reindex system catalogs concurrently
-REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1260;
-ERROR:  cannot move system relation "pg_toast_1260_index"
-REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1260;
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1262;
+ERROR:  cannot move system relation "pg_toast_1262_index"
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1262;
 ERROR:  cannot reindex system catalogs concurrently
 -- system catalog, fail
 REINDEX (TABLESPACE pg_global) TABLE pg_authid;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index e296891cab..fe162cc7c3 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1126,9 +1126,9 @@ REINDEX TABLE CONCURRENTLY concur_reindex_tab;
 COMMIT;
 REINDEX TABLE CONCURRENTLY pg_class; -- no catalog relation
 REINDEX INDEX CONCURRENTLY pg_class_oid_index; -- no catalog index
--- These are the toast table and index of pg_authid.
-REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1260; -- no catalog toast table
-REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1260_index; -- no catalog toast index
+-- These are the toast table and index of pg_database.
+REINDEX TABLE CONCURRENTLY pg_toast.pg_toast_1262; -- no catalog toast table
+REINDEX INDEX CONCURRENTLY pg_toast.pg_toast_1262_index; -- no catalog toast index
 REINDEX SYSTEM CONCURRENTLY postgres; -- not allowed for SYSTEM
 REINDEX (CONCURRENTLY) SYSTEM postgres; -- ditto
 REINDEX (CONCURRENTLY) SYSTEM;  -- ditto
@@ -1305,8 +1305,8 @@ REINDEX SCHEMA schema_to_reindex;
 RESET ROLE;
 GRANT USAGE ON SCHEMA pg_toast TO regress_reindexuser;
 SET SESSION ROLE regress_reindexuser;
-REINDEX TABLE pg_toast.pg_toast_1260;
-REINDEX INDEX pg_toast.pg_toast_1260_index;
+REINDEX TABLE pg_toast.pg_toast_1262;
+REINDEX INDEX pg_toast.pg_toast_1262_index;
 
 -- Clean up
 RESET ROLE;
diff --git a/src/test/regress/sql/misc_sanity.sql b/src/test/regress/sql/misc_sanity.sql
index 16f3a7c0c1..135793871b 100644
--- a/src/test/regress/sql/misc_sanity.sql
+++ b/src/test/regress/sql/misc_sanity.sql
@@ -43,6 +43,8 @@ WHERE refclassid = 0 OR refobjid = 0 OR
 -- 2. pg_largeobject and pg_largeobject_metadata.  Large object catalogs
 -- and toast tables are mutually exclusive and large object data is handled
 -- as user data by pg_upgrade, which would cause failures.
+-- 3. pg_authid, since its toast table cannot be accessed when it would be
+-- needed, i.e., during authentication before we've selected a database.
 
 SELECT relname, attname, atttypid::regtype
 FROM pg_class c JOIN pg_attribute a ON c.oid = attrelid
diff --git a/src/test/regress/sql/tablespace.sql b/src/test/regress/sql/tablespace.sql
index d274d9615e..c8b83788f0 100644
--- a/src/test/regress/sql/tablespace.sql
+++ b/src/test/regress/sql/tablespace.sql
@@ -40,10 +40,10 @@ REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_am;
 REINDEX (TABLESPACE regress_tblspace) TABLE pg_authid;
 REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_authid;
 -- toast relations, fail
-REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1260_index;
-REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1260_index;
-REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1260;
-REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1260;
+REINDEX (TABLESPACE regress_tblspace) INDEX pg_toast.pg_toast_1262_index;
+REINDEX (TABLESPACE regress_tblspace) INDEX CONCURRENTLY pg_toast.pg_toast_1262_index;
+REINDEX (TABLESPACE regress_tblspace) TABLE pg_toast.pg_toast_1262;
+REINDEX (TABLESPACE regress_tblspace) TABLE CONCURRENTLY pg_toast.pg_toast_1262;
 -- system catalog, fail
 REINDEX (TABLESPACE pg_global) TABLE pg_authid;
 REINDEX (TABLESPACE pg_global) TABLE CONCURRENTLY pg_authid;
-- 
2.39.5 (Apple Git-154)

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Sep 2024 12:27:41 -0400

> Here is a v3 patch set that fixes the test comment and a compiler warning

Nitpick: the message should say "%d bytes" not "%d characters",
because we're counting bytes.  Passes an eyeball check otherwise.

			regards, tom lane

---

From: Nathan Bossart <[email-masked]>
Date: Sat, 21 Sep 2024 15:25:54 -0500

> because we're counting bytes.  Passes an eyeball check otherwise.

Thanks for reviewing.  I went ahead and committed 0002 since it seems like
there's consensus on that one.  I've attached a rebased version of 0001
with s/characters/bytes.

-- 
nathan
>From f039f02cc35d57862af64648d4152693e52fbee2 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <[email-masked]>
Date: Thu, 19 Sep 2024 20:59:10 -0500
Subject: [PATCH v4 1/1] place limit on password hash length

---

index 629e51e00b..05d289977f 100644

 

+				 errdetail("Encrypted passwords must be no longer than %d bytes.",
 
 /*

+/*
index 924d6e001d..dbfe92a27d 100644
 
+DETAIL:  Encrypted passwords must be no longer than 256 bytes.
+DETAIL:  Encrypted passwords must be no longer than 256 bytes.

---

From: Nathan Bossart <[email-masked]>
Date: Thu, 3 Oct 2024 16:27:40 -0500

> there's consensus on that one.  I've attached a rebased version of 0001

For the reasons discussed upthread [0], I can't bring myself to add an
arbitrary limit to the password hash length.  I am going to leave 0001
uncommitted for now.

[0] https://postgr.es/m/Zu2eT2H8OT3OXauc%40nathan

-- 
nathan

---

From: Tom Lane <[email-masked]>
Date: Thu, 03 Oct 2024 17:39:06 -0400

> [0] https://postgr.es/m/Zu2eT2H8OT3OXauc%40nathan

I'm confused, as in [0] you said

>> ...  But on the off-chance

I agree with the idea that complaining about the password being too
long is far more intelligible than that.  Another problem with
leaving it as it stands in HEAD is that the effective limit is now
platform-specific, if not indeed dependent on the phase of the moon
(or at least, the other contents of the pg_authid row).  I fear it
would be very easy to construct cases where a password is accepted
on one machine but fails with "row is too big" on another.  A
uniform limit seems much less fraught with surprises.

			regards, tom lane

---

From: Nathan Bossart <[email-masked]>
Date: Thu, 3 Oct 2024 17:17:01 -0500

> uniform limit seems much less fraught with surprises.

I don't mind proceeding with the patch if there is strong support for it.
I wavered only because it's hard to be confident that we are choosing the
right limit.  AFAICT 256 bytes ought to be sufficient to avoid "row is too
big" errors independent of BLCKSZ today, but maybe someone will add another
varlena column in the future that breaks it.  Or maybe we add a new
password hashing method that produces longer strings.  Or maybe someone is
doing something really out there like storing additional information in the
salt.  I don't have any reason to believe that any of these things are
happening or are likely to happen anytime soon, but they seem similar in
likelihood to someone building a custom driver that generates ginormous
hashes.  But I can also buy the argument that none of this is a strong
enough reason to avoid making the error message nicer...

-- 
nathan

---

From: Tom Lane <[email-masked]>
Date: Thu, 03 Oct 2024 18:24:54 -0400

Nathan Bossart <[email-masked]> writes:
> right limit.

I'm not that fussed about it; surely 256 is more than anyone is using?
If not, we'll get push-back and then we can have a discussion about the
correct limit that's informed by more than guesswork.

> ... But I can also buy the argument that none of this is a strong

There's that, and there's also the fact that if you assume someone is
using $sufficiently-long-passwords then we might have broken their
use-case already.  We can't have much of a conversation here without
a concrete case to look at.

			regards, tom lane

---

From: Jacob Champion <[email-masked]>
Date: Thu, 3 Oct 2024 16:29:46 -0700

> correct limit that's informed by more than guesswork.

+1.

Next up is probably SCRAM-SHA-512, which should still have smaller
entries than that -- 222 bytes, I think, with 128-bit salts and a
5-digit iteration count?

--Jacob

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Thu, 3 Oct 2024 22:17:31 -0400

> entries than that -- 222 bytes, I think, with 128-bit salts and a

The challenge is that salts can be an arbitrary length, even today (as 
can the iterator value, though IIRC I think we check if it's in int 
bounds, and a large iterator becomes pretty impractical for usage).

Probabalistically, it's unlikely there are many very large salts in the 
wild (though I don't have data on that) and most folks are using the 
default length, but that probability isn't 0.

I think Tom's initial suggestion (BLCKSZ/2) is better than 256, given we 
really don't know what' out there in the wild, and this could end up 
being a breaking change. Every other type in pg_authid is pretty small. 
That said, I'm also imagining other things we may add that could require 
TOAST support (remembering previous passwords? storing multiple 
passwords options)?

Thanks,

Jonathan

---

From: Tom Lane <[email-masked]>
Date: Thu, 03 Oct 2024 22:33:04 -0400

> being a breaking change. Every other type in pg_authid is pretty small. 

I'm having second thoughts about that though, based on the argument
that we don't really want a platform-dependent limit here.
Admittedly, nobody changes BLCKSZ on production systems, but it's
still theoretically an issue.  I don't have a problem with selecting
a larger limit such as 512 or 1024 though.

> TOAST support (remembering previous passwords? storing multiple 

Things like previous passwords probably don't need to be accessed
during authentication, so there are at least a couple of ways we
could do that:
* put the previous passwords in an auxiliary table;
* put back pg_authid's toast table, but mark rolpassword as
"STORAGE MAIN" so it doesn't go to toast, while letting columns
that don't need to be touched at startup go there.

However, if you wanted to allow multiple passwords I'm not
sure about a good way.

			regards, tom lane

---

From: Nathan Bossart <[email-masked]>
Date: Fri, 4 Oct 2024 09:47:56 -0500

> a larger limit such as 512 or 1024 though.

Since BLCKSZ can be as low as 1024, I think 512 would be a good choice.

> However, if you wanted to allow multiple passwords I'm not

The most recent proposal I'm aware of [0] did seem to target that use-case.
One option might be to move rolpassword to a different catalog.  In any
case, I don't think it matters much for the patch at hand.

[0] https://postgr.es/m/CAGB%2BVh5SQQorNDEKP%2B0G%3DsmxHRhbhs%2BVkmQWD5Vh98fmn8X4dg%40mail.gmail.com

-- 
nathan

---

From: Hannu Krosing <[email-masked]>
Date: Sun, 6 Oct 2024 11:42:53 +0200

On Fri, Oct 4, 2024 at 4:48 PM Nathan Bossart <[email-masked]> wrote:
..
> Since BLCKSZ can be as low as 1024, I think 512 would be a good choice.

Where did you get the minimal value of 1024 from ?

I vaguely remember someone testing with 256 at some point in the past

---
Hannu

---

From: Nathan Bossart <[email-masked]>
Date: Sun, 6 Oct 2024 08:12:51 -0500

> Where did you get the minimal value of 1024 from ?

	https://www.postgresql.org/docs/devel/install-make.html#CONFIGURE-OPTION-WITH-BLOCKSIZE

-- 
nathan

---

From: Nathan Bossart <[email-masked]>
Date: Mon, 7 Oct 2024 10:58:54 -0500

Committed with the limit set to 512 bytes.  We have plenty of time to
adjust this limit as needed before it takes effect in v18.

-- 
nathan