Re: about seperate users in PostgreSQL

On 06/14/2012 08:31 PM, Egidijus wrote:
> Hello,
> How it’s possible to make that seperate users can see and control only
> their own databases when they connected to PostgreSQL server? Standard
> PostgreSQL configuration allows users to see databases of other users.
> In my PostgreSQL server all users have their own logins. I’d like
> that users can connect only to their own databases (see only them).
> How it’s possible to make that in PostgreSQL?
>
At the moment, the only way to to that is to run a cluster (ie: a
postmaster) per-user. This is very inefficient.

A Google search and/or search of these archives for "postgresql
multi-tenant" will provide some more info on different approaches to
multi-tenant clusters. Right now none of the approaches really offer
ideal isolation, Pg just isn't made for that and nobody's stepped up
with the code, time, or funding to build full isolated multi-tenancy.

In addition to visibility of databases, users and some other objects in
global tables, you'll find that you can't use built-in replication to
replicate just one database or one user's databases, and you can't
separate WAL activity, bgwriter activity, etc for different users so one
user's load can adversely affect other users.

While most people's use of Pg doesn't suffer for these limitations, I
suspect you'll need to know about them if you're trying to offer
multi-tenant hosting.

--
Craig Ringer