Skip site navigation (1) Skip section navigation (2)

Re: Extended security/restriction to any role with login access

From: Lennin Caro <lennin(dot)caro(at)yahoo(dot)com>
To: Domingo Alvarez Duarte <mingodad(at)gmail(dot)com>, Carol Walter <walterc(at)indiana(dot)edu>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Extended security/restriction to any role with login access
Date: 2008-06-26 18:44:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin

you can restric acces from all the databases in your cluster. When you use pgadmin3 this show all the databases but if you dont have access to the databases you cant see the struct of this.

check waht user use pgadmin3 for connect to databases 

create groups and add privileges to the group later add the users to the group

--- On Thu, 6/26/08, Carol Walter <walterc(at)indiana(dot)edu> wrote:
From: Carol Walter <walterc(at)indiana(dot)edu>
Subject: Re: [ADMIN] Extended security/restriction to any role with login access
To: "Domingo Alvarez Duarte" <mingodad(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Date: Thursday, June 26, 2008, 5:34 PM

Hello, Domingo,

My question is why do your users need access to pgadmin3?
I have not used pgadmin3; we use phpPgAdmin.  I can restrict access  
to that by putting it behind .htaccess.  That is only users with a  
user name in .htacess can run phpPgAdmin.  In the case of pgadmin3,  
shouldn't you be able to restrict access to it by setting privs at  
the operating system level?  With phpPgAdmin, I can also restrict it  
so a user can only see the databases the s/he owns.  Postgres owns my  
databases so I can't do it this way, but it could be done.


On Jun 26, 2008, at 1:04 PM, Domingo Alvarez Duarte wrote:

> Hello !
> I'm trying to use postgresql in an application that by design will  
> give access to users to a subset of the database.
> For example for customers access to products_view (wich will only  
> show public offers), orders (only their own orders).
> I'll provide an application as user interface for the data.
> For that I'll give for each of then a role in the database that  
> will belong to a group role customers_group.
> The customers_group only has access to the views/functions that  
> I'll specify.
> Till here no problem postgresql do that pretty well.
> My concern is once I give login access to any user, even without  
> grant him/her any access to any database, he/she can using an  
> application like pgadmin3 view all databases/roles/functions/table- 
> definitions on my server. And that was not my intention.
> Removing all from public doesn't work : revoke all on schema public  
> from public;
> What I think would be the server behavior when I create a role with  
> login access an say that I only grant access to one view like this:
> create role oneuser login;
> grant select on somedatabase.someview to oneuser;
> In that case when the user login the only thing he/she sees is the  
> view database.someview, even when they use pgadmin3 to connect.
> Actually he/she can see with pgadmin3 : all databases, all roles  
> and it's right access, all tables on every database (no access to  
> data), all functions, all triggers, all table definitions.
> The above isn't the intention to a user with a restrict view of the  
> database.
> Can I achieve it actually, if not how hard could be to implement  
> that in the official release ?
> Thanks in advance for any feedback/ideas !

In response to


pgsql-admin by date

Next:From: Domingo Alvarez DuarteDate: 2008-06-26 21:45:19
Subject: Re: Extended security/restriction to any role with login access
Previous:From: Simon RiggsDate: 2008-06-26 18:37:30
Subject: Re: Warm standby server

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group