Thread: Maximum realistic number of database user accounts?
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? Thanks, -g -- Greg Fausak greg@thursday.com
On Feb 14, 2008, at 11:16 AM, Greg Fausak 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? > > Thanks, > -g You really expect your users to remember 10 different logins? No offense, but that's crazy talk and a user management nightmare. What you should probably be doing is: 1. Give each user 1 login role (a user account) (probably with INHERIT privileges). 2. Create non-login roles (groups) for each set of privileges that user can have and make your grants/revokes here. 3. Grant membership to your user roles in which ever group roles are appropriate for them. 3a. If you've given the login roles INHERIT privileges then they'll automatically inherit the privileges of whatever group roles they now have membership in. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Thu, Feb 14, 2008 at 5:16 PM, Greg Fausak <lgfausak@gmail.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@[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
"Greg Fausak" <lgfausak@gmail.com> writes:
> Can I create a database with 1 million login roles and expect performance to
> be good? 10 million?
Probably not, as (to my knowledge) no one has ever done any performance
testing in that domain. I doubt anyone's worked with more than a few
thousand roles. I can definitely say that granting permissions on the
same object to many thousands of roles separately would be a Bad Idea.
Feel free to try it, but I'd *strongly* advise doing some performance
testing before you commit to such a design. And think about how you can
use groups to reduce redundancy in the permissions data.
regards, tom lane
inline.. On Thu, Feb 14, 2008 at 1:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Greg Fausak" <lgfausak@gmail.com> writes: > > Can I create a database with 1 million login roles and expect performance to > > be good? 10 million? > > Probably not, as (to my knowledge) no one has ever done any performance > testing in that domain. I doubt anyone's worked with more than a few > thousand roles. I can definitely say that granting permissions on the > same object to many thousands of roles separately would be a Bad Idea. > I'm definitely granting permissions to (group) roles, then the roles are altered to add classes of users. For example, a 'enduser' role and 'admin' role control the access privileges ia views, and the individual login roles are granted either 'enduser' or 'admin' (the group is altered to include the users). So, your statement that granting permissions on the same object, does that apply to roles themselves? The bulk of my permissions boil down to granting a role to a role. We have good performance now in the 20K user range. I was just curious what would happen when this gets bumped up a couple orders. -g > Feel free to try it, but I'd *strongly* advise doing some performance > testing before you commit to such a design. And think about how you can > use groups to reduce redundancy in the permissions data. > > regards, tom lane > -- Greg Fausak greg@thursday.com
"Greg Fausak" <lgfausak@gmail.com> writes:
> So, your statement that granting permissions on the same object, does
> that apply to roles themselves?
> The bulk of my permissions boil down to granting a role to a role.
Well, there are not *known* bottlenecks in that, but again it's not
something that's been stress-tested AFAIK. I'd still recommend setting
up a test installation and experimenting before you find out the hard
way.
regards, tom lane