Thanks Tom. You know I thought about this approach a little more. I
don't think there's a simple answer to this security problem short of
placing a proxy server application between the clients and the
database. The problem with giving database role accounts to each and
every user is that the users now have uncontrolled access to the
database. That's not good either because I want to do fine grained
access control within my application.
The more I think about it, the more I think a proxy app is necessary.
It seems like a lot of work just for security issues, but basically most
web based database apps use this model, with the web application acting
as a proxy between the database and the client.
Thanks.
Tom Lane wrote:
> Mark Walker <furface@omnicode.com> writes:
>
>> I'm wondering if there's a manageable limit on the number of users in
>> postgresql. I could have conceivably thousands of different users for
>> any particular app and multiple apps in the same server.
>>
>
> Numbers in the low thousands are probably workable, as long as you
> aren't adding and removing users at a great rate. I think that the
> main bottleneck would be the "flat file" that's used to tell the
> postmaster about the set of valid users --- every time a user is
> added/dropped/changed, that file gets rewritten and then re-parsed
> by the postmaster. So you could eat a lot of overhead if you change
> users every few seconds or something like that.
>
> I'd suggest doing some testing to see if there are any unexpected
> bottlenecks, but I don't see a reason to reject the idea out of hand.
> (If you find any slownesses, report 'em, and we might be able to fix
> 'em.)
>
> regards, tom lane
>
>