Обсуждение: about seperate users in PostgreSQL

Egidijus <egidijus.p@splius.lt> 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?

Please read http://wiki.postgresql.org/wiki/Shared_Database_Hosting
(the author is a colleague )

AFAIK you can't avoid that users can see other databases.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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