Re: Maximum realistic number of database user accounts?

On Thu, Feb 14, 2008 at 5:16 PM, Greg Fausak <lgfausak(at)gmail(dot)com> wrote:
> Howdy,
>
> I find that user accounts are very good for
> helping me protect application access to the database.
> That is, instead of giving a user 1 account, I may give hem
> 10, and each of those accounts are restricted in the database
> in different ways. Anyway, I'm wondering what the maximum number of
> user accounts can
> be in a postgres database?
>
> Can I create a database with 1 million login roles and expect performance to
> be good? 10 million?

Well, consider the underlying table, pg_authid...

slonyregress1(at)[local]:7000=# \d pg_authid
Table "pg_catalog.pg_authid"
Column | Type | Modifiers
---------------+--------------------------+-----------
rolname | name | not null
rolsuper | boolean | not null
rolinherit | boolean | not null
rolcreaterole | boolean | not null
rolcreatedb | boolean | not null
rolcatupdate | boolean | not null
rolcanlogin | boolean | not null
rolconnlimit | integer | not null
rolpassword | text |
rolvaliduntil | timestamp with time zone |
rolconfig | text[] |
Indexes:
"pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
"pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Triggers:
pg_sync_pg_authid AFTER INSERT OR DELETE OR UPDATE ON pg_authid
FOR EACH STATEMENT EXECUTE PROCEDURE flatfile_update_trigger()
Tablespace: "pg_global"

It is indexed on oid and rolname, which should allow reasonable
efficiency of name/oid-based access to users and roles.

If that's the only place where sizing of pg_authid grows, then "things
ought to work."

Now, how you manage permissions will have an *enormous* amount to do
with how things will turn out.

I think you'd need to create some "security roles," to express the
limited number of different sorts of security configuration, and
associate permissions to tables via those "security roles." You'd
then grant accesses to the "tens of thousands of users" via those
security roles, which keeps the number of direct associations between
users and tables down. THAT could, otherwise, grow precipitously
quickly!

If you have tens of thousands of users associated with a particular
security role, I could see there being some possible bottlenecks
there.

This feels like it's worth modelling to see extra edges. It should be
easy enough to simulate, via scripting up the creation of an enormous
number of users.

--
http://linuxfinances.info/info/linuxdistributions.html
"The definition of insanity is doing the same thing over and over and
expecting different results." -- assortedly attributed to Albert
Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling