It all started with a help request from a someone on IRC: he has about 2500 roles, and all of them have been granted USAGE privileges on a schema. He went on to create a bunch more and grant the same privilege, but it failed:

CREATE ROLE some_user_2501;
GRANT USAGE on schema genetics to some_user_2501;

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

Oops.

Now, let this be said: this is wrong design. It causes slowness, due to the way those privileges are stored. A much better way to go about this is to create a single role, grant the privileges to that role, and the grant that role to all other roles. So:

CREATE ROLE genetic_reader;
GRANT USAGE ON schema genetics TO genetic_reader;
GRANT genetic_reader TO some_user_0001, some_user_0002, ...;

That said, there is always people who want to do things their own way, and so this answer isn't enough. They want to know how to make their original GRANT statement work. Here's how.

The reason it fails, as the message says, is that the row is too big. Supposedly, we have solved this problem in Postgres by using TOAST tables: when attributes get too large, they are compressed and sent to chunked storage to the toast table. This works fine ... as long as there is a toast table to start with. Turns out that not all system catalogs have one.

This is the list of system catalogs with ACL columns in them, and the OID of their toast table. If the OID is zero, it means it has no toast table and thus they will cause failures in case someone tries to grant privileges to umpteen users.

select relname, reltoastrelid
  from pg_class
 where oid in (
        select attrelid
          from pg_attribute
         where (attname like '%acl' and atttypid = 'aclitem[]'::regtype) or
               (attname like '%options' and atttypid = 'text[]'::regtype))
       and relkind = 'r';

(13 rows)

(I have also added "options" columns, because those could also cause problems, though the number of possible options is limited, so this is unlikely to cause any problems in practice.)

Notice that of those, only pg_proc and pg_database catalogs have toast tables. Having it for pg_proc is understandable: that's the catalog where function source code is stored, and that tends to get large, frequently. But pg_database? The only explanation is that someone got bit by the limitation on granting CONNECT privileges to a large number of roles. So it follows that all the remaining system catalogs should be modified in this way, too.

To work around this limitation, you can manually create a TOAST table to the system catalog. To do this, you need to start the server in a special mode that lets you modify the system catalogs:

postgres -O

Connect to it, and do something like this:

ALTER TABLE pg_namespace ADD COLUMN foo text;
ALTER TABLE pg_namespace DROP COLUMN foo;

Then stop the server and restart it normally. Now pg_namespace, the system catalog where schema permissions are stored, has a TOAST table and you can issue all those thousands of GRANT .. ON SCHEMA you've always wished (yeah, right).

While this is not recommended, I wonder if we should go ahead and fix the problem by having the system automatically create toast tables on those system catalogs.

What do you think?