Thread: Implications of having large number of users
Hi there, Please help me to make a decision on how to manage users. For some reason it is easier in the project I'm working on to split data by schemes and assign them to Postgres' users (I mean those created with CREATE USER) rather than support 'owner' fields referring to a global users table. The question is what could be the consequences of having a large number of them (tens of thousands)? Context: - it is a web app - thousands of concurrent requests from different users - amount of user's data in the db is relatively small Concerns: - how big is the performance/memory penalty on switching users in the same connection (connections are reused of course)? - will it hurt the cache? - are prepared statements kept per user or per connection? - is the query planner global or somehow tied to users? I'd be glad to hear any opinions/suggestions. Best regards, Mike
Mike Ivanov wrote: > Please help me to make a decision on how to manage users. > > For some reason it is easier in the project I'm working on to split data > by schemes and assign them to Postgres' users (I mean those created with > CREATE USER) rather than support 'owner' fields referring to a global > users table. You know that (unlike in Oracle) user and schema is not coupled in PostgreSQL, right? So you can have one user owning tables in various schemata and many users owning tables in one schema. > The question is what could be the consequences of having a large number > of them (tens of thousands)? It shouldn't be a problem. The only critical number is the number of concurrent connections at a given time. > Context: > > - it is a web app > - thousands of concurrent requests from different users > - amount of user's data in the db is relatively small > > Concerns: > > - how big is the performance/memory penalty on switching users in the > same connection (connections are reused of course)? > - will it hurt the cache? > - are prepared statements kept per user or per connection? > - is the query planner global or somehow tied to users? > > I'd be glad to hear any opinions/suggestions. You cannot keep the connection and change users. A change of database user always means a new connection and a new backend process. Yours, Laurenz Albe
On Jun 24, 2009, at 4:32 AM, "Albe Laurenz" <laurenz.albe@wien.gv.at> wrote: > Mike Ivanov wrote: >> Please help me to make a decision on how to manage users. >> >> For some reason it is easier in the project I'm working on to split >> data >> by schemes and assign them to Postgres' users (I mean those created >> with >> CREATE USER) rather than support 'owner' fields referring to a global >> users table. > > You know that (unlike in Oracle) user and schema is not coupled in > PostgreSQL, right? So you can have one user owning tables in various > schemata > and many users owning tables in one schema. > >> The question is what could be the consequences of having a large >> number >> of them (tens of thousands)? > > It shouldn't be a problem. > The only critical number is the number of concurrent connections > at a given time. > >> Context: >> >> - it is a web app >> - thousands of concurrent requests from different users >> - amount of user's data in the db is relatively small >> >> Concerns: >> >> - how big is the performance/memory penalty on switching users in the >> same connection (connections are reused of course)? >> - will it hurt the cache? >> - are prepared statements kept per user or per connection? >> - is the query planner global or somehow tied to users? >> >> I'd be glad to hear any opinions/suggestions. A bunch of small tables might possibly take up more space than a smaller number of larger tables, increasing memory requirements... > You cannot keep the connection and change users. > A change of database user always means a new connection and a new > backend > process. I don't think this is true. You can use SET SESSION AUTHORIZATION, right? ...Robert
Robert Haas wrote: > > You cannot keep the connection and change users. > > A change of database user always means a new connection and a new > > backend process. > > I don't think this is true. You can use SET SESSION AUTHORIZATION, > right? You are right, I overlooked that. It is restricted to superusers though. Yours, Laurenz Albe
"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> Robert Haas wrote:
>> I don't think this is true. You can use SET SESSION AUTHORIZATION,
>> right?
> You are right, I overlooked that.
> It is restricted to superusers though.
That sort of thing is only workable if you have trustworthy client code
that controls what queries the users can issue. If someone can send raw
SQL commands then he just needs to do RESET SESSION AUTHORIZATION to
become superuser.
regards, tom lane
On Wed, Jun 24, 2009 at 9:52 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > "Albe Laurenz" <laurenz.albe@wien.gv.at> writes: >> Robert Haas wrote: >>> I don't think this is true. You can use SET SESSION AUTHORIZATION, >>> right? > >> You are right, I overlooked that. >> It is restricted to superusers though. > > That sort of thing is only workable if you have trustworthy client code > that controls what queries the users can issue. If someone can send raw > SQL commands then he just needs to do RESET SESSION AUTHORIZATION to > become superuser. Good point, although since the OP said it was a webapp, they probably have control over that. ...Robert
> I'd be glad to hear any opinions/suggestions. Many thanks to everyone who responded! Mike