postgresql

"row is too big (...) maximum size 8160" when running "grant connect on database"

I'm facing weird issue with postgres 11.

I'm creating a bunch of users and then assigning them some roles but also letting them to connect to certain database.

After successfully creating 2478 roles when I try to create new user I get this error:

db=# create user foo;
CREATE ROLE
db=# grant connect on database db to foo;
ERROR:  row is too big: size 8168, maximum size 8160

Same error shows up in db log.

I checked if db volume is not running out of space, there is still 1T to spare there...

I can't imagine postgres trying to insert more than 8k when running simple grant...?

edit:

It seems there was similar question asked already (usage privileges on schema):

ERROR: row is too big: size 8168, maximum size 8164

So the solution would be to create one role, say connect_to_my_db and grant connect to that role, and then instead of running GRANT connect to each user do GRANT connect_to_my_db.

Solution

  • You found the solution yourself, let me add an explanation of the cause of the error:

    Each table row is stored in one of the 8KB blocks of the table, so that is its size limit.

    Normal tables have a TOAST table, where long attributes can be stored out-of-line. This allows PostgreSQL to store very long rows.

    Now system catalog tables do not have TOAST tables, so rows are limited to 8KB size.

    The access control list of an object is stored in the object's catalog, so many permissions on a table can exceed the limit.

    Be glad — if you had to manage permissions for thousands of users individually, you'd end up in DBA hell anyway.