Thread: Implications of having large number of users

Implications of having large number of users

From
Mike Ivanov
Date:
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






Re: Implications of having large number of users

From
"Albe Laurenz"
Date:
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

Re: Implications of having large number of users

From
Robert Haas
Date:
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

Re: Implications of having large number of users

From
"Albe Laurenz"
Date:
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

Re: Implications of having large number of users

From
Tom Lane
Date:
"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

Re: Implications of having large number of users

From
Robert Haas
Date:
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

Re: Implications of having large number of users

From
Mike Ivanov
Date:
> I'd be glad to hear any opinions/suggestions.

Many thanks to everyone who responded!

Mike