Re: about seperate users in PostgreSQL

Craig Ringer <ringerc@xxxxxxxxxxxxx> · Fri, 15 Jun 2012 09:12:11 +0800



    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