Thread: Thousands of users using one schema -> ERROR: row is too big
Hi all, I'm working on a database that will have a very large number of users, and I'm running in to a problem: when I grant morethan about 2500 users access to a schema, I get my_db=# grant usage on schema my_schema to some_user; ERROR: row is too big: size 8168, maximum size 8164 This of course makes access control tricky on high user-count setups. On IRC, linuxpoet and andres suggested that the problem is that the nspacl column in pg_catalog.pg_namespace grows too large.A suggested fix by linuxpoet adds a toast table to pg_namespace. A potentially dangerous work-around suggested by andresis to alter the pg_namespace table while temporarily having allow_system_table_mods on. That seems to have made thesymptom go away for me, but I'm not sure of what consequences the change had. Spontaneously, it seems to me that ACL entriescould be stored as rows in a table instead of as elements in an array, but I'm definitely not qualified to commenton PostgreSQL implementation issues. Do you agree with linuxpoet's fix? If so, when do you think it is reasonable to include it? Best Regards Magnus Reftel
In response to Magnus Reftel <magnus.reftel@gmail.com>: > > I'm working on a database that will have a very large number of users, and I'm running in to a problem: when I grant morethan about 2500 users access to a schema, I get > > my_db=# grant usage on schema my_schema to some_user; > ERROR: row is too big: size 8168, maximum size 8164 > > This of course makes access control tricky on high user-count setups. > > On IRC, linuxpoet and andres suggested that the problem is that the nspacl column in pg_catalog.pg_namespace grows toolarge. A suggested fix by linuxpoet adds a toast table to pg_namespace. A potentially dangerous work-around suggestedby andres is to alter the pg_namespace table while temporarily having allow_system_table_mods on. That seems tohave made the symptom go away for me, but I'm not sure of what consequences the change had. Spontaneously, it seems tome that ACL entries could be stored as rows in a table instead of as elements in an array, but I'm definitely not qualifiedto comment on PostgreSQL implementation issues. > > Do you agree with linuxpoet's fix? If so, when do you think it is reasonable to include it? I would think that a better solution would be to follow best practices and create roles and put users in those roles, so you don't have to have so many grants on objects. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
On Mar 1, 2011, at 21:57 , Bill Moran wrote: > In response to Magnus Reftel <magnus.reftel@gmail.com>: >> >> I'm working on a database that will have a very large number of users, and I'm running in to a problem: when I grant morethan about 2500 users access to a schema, I get >> >> my_db=# grant usage on schema my_schema to some_user; >> ERROR: row is too big: size 8168, maximum size 8164 >> >> This of course makes access control tricky on high user-count setups. > I would think that a better solution would be to follow best practices and > create roles and put users in those roles, so you don't have to have so > many grants on objects. Right, that grant was actually useless, since the users already have usage rights to the schema via a role. Problem solved. Sorry for the noise! Thanks! Magnus Reftel
On Tue, 2011-03-01 at 15:57 -0500, Bill Moran wrote: > In response to Magnus Reftel <magnus.reftel@gmail.com>: > > > > I'm working on a database that will have a very large number of users, and I'm running in to a problem: when I grantmore than about 2500 users access to a schema, I get > > > > my_db=# grant usage on schema my_schema to some_user; > > ERROR: row is too big: size 8168, maximum size 8164 > > > > This of course makes access control tricky on high user-count setups. > > > > On IRC, linuxpoet and andres suggested that the problem is that the nspacl column in pg_catalog.pg_namespace grows toolarge. A suggested fix by linuxpoet adds a toast table to pg_namespace. A potentially dangerous work-around suggestedby andres is to alter the pg_namespace table while temporarily having allow_system_table_mods on. That seems tohave made the symptom go away for me, but I'm not sure of what consequences the change had. Spontaneously, it seems tome that ACL entries could be stored as rows in a table instead of as elements in an array, but I'm definitely not qualifiedto comment on PostgreSQL implementation issues. > > > > Do you agree with linuxpoet's fix? If so, when do you think it is reasonable to include it? > > I would think that a better solution would be to follow best practices and > create roles and put users in those roles, so you don't have to have so > many grants on objects. Well, yes and no. There is no technical reason (that I know of) that we don't toast those tables. It would be good for him to follow best practices but considering he did run into the bug/oversight and it does appear to be arbitrary, there is no reason to not fix it. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
Bill Moran <wmoran@potentialtech.com> writes:
> In response to Magnus Reftel <magnus.reftel@gmail.com>:
>> On IRC, linuxpoet and andres suggested that the problem is that the
> nspacl column in pg_catalog.pg_namespace grows too large. A suggested
> fix by linuxpoet adds a toast table to pg_namespace.
> I would think that a better solution would be to follow best practices and
> create roles and put users in those roles, so you don't have to have so
> many grants on objects.
Yeah. You could probably get around it with the add-a-toast-table hack,
but I think performance would be a lot worse.
regards, tom lane