Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5461B650EC8 for ; Fri, 27 Jun 2008 02:44:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51790-01-5 for ; Fri, 27 Jun 2008 02:44:03 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.243]) by postgresql.org (Postfix) with ESMTP id 6B5AC650F0E for ; Fri, 27 Jun 2008 02:43:52 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d30so83744and.33 for ; Thu, 26 Jun 2008 22:43:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=gscRiCu6JpJlBebIIiA5YjJWqLsi3E6L9yD+iLVrct0=; b=RDlMsIUEiBYEBhA1RIUIXzdWJYPoFHi9GAGdyfnCwauvw76LoMh91oyoVm6Um6mXIC dpq7L+YZY5u6QmKBoE7HcddY2/MTa3RJL5AgXNvpXmsEqPrBQmP78svT7VWIoK1Jsudu UvWe7YnSTr8ta/rX0KmTvNpdPhhKqiKb/smi8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=kVExBiuXy0fZ/LZ0IK/Pw5efs9N/vBKEwtY7Tl08ZQboXEWv99DMZWCbqAR0z4HG5r iPLBV36DWZbMnjYZnqszlUgjax2j//WHXDZwPMGMO74rI4ehVNj8rw5m/jGJc2ayUJpS ddQksjBdbu7ApysS5wBBnEbNORZyARBmOjJXg= Received: by 10.100.215.5 with SMTP id n5mr995005ang.99.1214545431425; Thu, 26 Jun 2008 22:43:51 -0700 (PDT) Received: by 10.101.69.7 with HTTP; Thu, 26 Jun 2008 22:43:51 -0700 (PDT) Message-ID: Date: Fri, 27 Jun 2008 07:43:51 +0200 From: "Domingo Alvarez Duarte" To: "Carol Walter" Subject: Re: Extended security/restriction to any role with login access Cc: pgsql-admin@postgresql.org In-Reply-To: <2D85373C-758F-4545-BE65-22A5E5A4B3A8@indiana.edu> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_16930_5553628.1214545431447" References: <2D85373C-758F-4545-BE65-22A5E5A4B3A8@indiana.edu> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200806/235 X-Sequence-Number: 29573 ------=_Part_16930_5553628.1214545431447 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline My users will not need to use pgadmin3, the whole problem is any user with login access to a postgresql server can use pgadmin3 to connect and see all databases/roles/functions/table-definitions/schemas. This is not a problem with pgadmin3 but with postgresql server. Look at it in a general way someone write one application that will connect to postgresql server (intranet or internet) the user of that application will see mor or less data/functionality based on her/his credentials (username/password) that are stored on postgresql and assigned grant access to the correspondig part of the database. 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. 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. On Thu, Jun 26, 2008 at 7:34 PM, Carol Walter wrote: > 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. > > Carol > > > 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 ! >> > > ------=_Part_16930_5553628.1214545431447 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline My users will not need to use pgadmin3, the whole problem is any user with login access to a postgresql server can use pgadmin3 to connect and see all databases/roles/functions
/table-definitions/schemas. This is not a problem with pgadmin3 but with postgresql server.

Look at it in a general way someone write one application that will connect to postgresql server (intranet or internet) the user of that application will see mor or less data/functionality based on her/his credentials (username/password) that are stored on postgresql and assigned grant access to the correspondig part of the database.


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.

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.



On Thu, Jun 26, 2008 at 7:34 PM, Carol Walter <walterc@indiana.edu> wrote:
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.

Carol


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 !


------=_Part_16930_5553628.1214545431447--