How to work around "row is too big" for role definitions without recreating the database?

Question

I am running into the row is too big (ERROR: 54000: row is too big: size 9976, maximum size 8160) error in Postgres related to role permissions on a couple of my instances. I understand that after v12 there was a TOAST table added to help mitigate that issue. I have upgraded the affected instances to v15, but the issue is still occurring.

I have seen many answers about how to structure permissions to avoid this problem, but have not had any luck in finding answers as to how to resolve the problem in a running database. In the email thread where the Postgres developers discussed the implementation of the TOAST tables to address this problem they alluded to manual steps needed to re-init a database to take advantage of the fix, but no reference to the actual steps.

For context, I am running on AWS RDS (not Aurora) and using Vault to generate dynamic database credentials. Because of the error I am unable to drop any permissions or modify existing permissions. I have also been unsuccessful in finding a way to locate the specific row in the pg_catalog that is generating the error.

Can someone please share any steps that they have taken to resolve this situation without having to recreate the entire database? Specifically, being able to drop permissions and users, and/or initialize the missing TOAST tables in the running database. Thank you for your help!

Answer 1

The problem is that the access control list for the table (column relacl) is part of the pg_class row for the table. That row has to fit into a single table block (8kB), so the row cannot grow any larger than that. If you grant permissions for too many roles on that table, you can get to this impasse.

The only possible solution is to redesign you permission model. Rather than granting privileges to hundreds of individual roles, use a group system: only the groups get privileges on your tables, and login roles are assigned membership in these roles.