Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9063D650EC8 for ; Fri, 27 Jun 2008 18:34:12 -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 61336-09 for ; Fri, 27 Jun 2008 18:33:37 -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.246]) by postgresql.org (Postfix) with ESMTP id 27115650EC4 for ; Fri, 27 Jun 2008 18:33:39 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id d30so151219and.33 for ; Fri, 27 Jun 2008 14:33:39 -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=7VnT/Ii4od+TTmjNqSlPw6YYKPNb79UXiZtmgEZ5PLw=; b=jGYPWXuedIn9igKFIKkxWVvV2eKst73ZpQsDhSoyETPKxwhDXBQ90dGzCz5U5K0B7s 7xHYbC2uJDE06LPPVy19CSbo5uJV2MoPwWt63R6TO2dDafxSbqKay2v0M0RhXrGQXYZ0 c2F1CR97qzWyRvMB07v8Not1IFg/gNlXOnftk= 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=fCb75OBztfUvISkkLJwo1CilF4HD2AXepp7rj/9SxHeAuWZgfnCCLJgCSc6Bfe/fLu gtbnmANCdn1QyWNmIQQoEJC0q1fL7Hfc3CwOO5L42Jsf4yqhbZA6wgkt0HUByAyl5h8x dMJTpomeJTH4zEJOl7G+fH3DQA+PZ3SoTJVR8= Received: by 10.100.152.15 with SMTP id z15mr2383595and.138.1214602418824; Fri, 27 Jun 2008 14:33:38 -0700 (PDT) Received: by 10.101.69.7 with HTTP; Fri, 27 Jun 2008 14:33:38 -0700 (PDT) Message-ID: Date: Fri, 27 Jun 2008 23:33:38 +0200 From: "Domingo Alvarez Duarte" To: lennin.caro@yahoo.com Subject: Re: Extended security/restriction to any role with login access Cc: "Carol Walter" , pgsql-admin@postgresql.org In-Reply-To: <408751.10145.qm@web59512.mail.ac4.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_22131_30797159.1214602418787" References: <408751.10145.qm@web59512.mail.ac4.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.602 tagged_above=0 required=5 tests=AWL=-0.301, HTML_MESSAGE=0.001, NORMAL_HTTP_TO_IP=0.001, URIBL_RHS_DOB=0.901 X-Spam-Level: X-Archive-Number: 200806/254 X-Sequence-Number: 29592 ------=_Part_22131_30797159.1214602418787 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Ok ! I could revoke privileges to postgres.pg_catalog from public but now new users can login but can't see anything, even when I grant access to then on one view I've created on a new database. Can someone give a detailed stepe by step on how to achieve this: - Probably change postgres and template1 to disallow access to public group by default. - After changes new users without superuser privileges or owned objects can't see anything. - Once granted access to a part of a database let's say a view, the granted user can see it. Let's start with fresh default postgresql installation. Make the necessary changes. Create to users with login access only, user1 and user2. Create a new database db1 with owner user1. Right now user1 can login and access his database db1. And user2 can login but can't see anithing. Now user1 create a table table1 in his public schema and a view view1 on that table, he grants select privilege to user2. Now user2 can connect and see only db1.public.view1 That is wat I want to achive, and I think that more people will need something like this too. Thanks again in advance for any help on the matter ! On Fri, Jun 27, 2008 at 9:10 PM, Lennin Caro wrote: > in pgadmin3: > > Menu -> Display > check the option "System Object" > > this show you the catalogs of postgresql "pg_xxxxxxxx" > > change the grants to users here > > --- On *Fri, 6/27/08, Domingo Alvarez Duarte * wrote: > > From: Domingo Alvarez Duarte > Subject: Re: [ADMIN] Extended security/restriction to any role with login > access > To: lennin.caro@yahoo.com > Cc: "Carol Walter" , pgsql-admin@postgresql.org > Date: Friday, June 27, 2008, 6:31 PM > > > How can I change that ? > I mean make the catalog of tables, function and roles private and only > accessible to granted users ? > > Someon mentioned once to make changes in template1, wich changes will be > nneded ? In case this is possible. > > Thanks for any help/sugestion ! > > On Fri, Jun 27, 2008 at 5:12 PM, Lennin Caro > wrote: > >> >> Ok... >> >> the catalog of tables, function and roles are public. Pgadmin use the >> catalog to create the tree of databases, function and roles. The user can >> see this but cant change this. In oracle the catalog of user, tables, >> function are public >> >> --- On *Fri, 6/27/08, Domingo Alvarez Duarte * wrote: >> >> From: Domingo Alvarez Duarte >> Subject: Re: [ADMIN] Extended security/restriction to any role with login >> access >> To: lennin.caro@yahoo.com >> Cc: "Carol Walter" , pgsql-admin@postgresql.org >> Date: Friday, June 27, 2008, 2:35 PM >> >> >> Look this isn't the point I know what can be done with pg_hba.conf >> >> The main point is: >> >> When I create a postgresql user and grant to it only access to part of a >> database (let's say one view). I'm expecting that the server will honor it. >> But right now postgresql server isn't. >> >> This is the reason I'm writing here and tried too hackers list but no one >> seems to understand the importance of this point. >> >> Maybe it's my fault of knowledge but till now no one showed me how to get >> the expected result, people try to tell me how to use firewall, change the >> way of build my application, they don't understand the point. I'll repeat it >> again: >> >> >> When I create a postgresql user and grant to it only access to part of a >> database (let's say one view). I'm expecting that the server will honor it. >> But right now postgresql server isn't. >> >> Actually any user with login access can see all >> databases/roles/functions/table-definitions/triggers. What I thinks isn't >> correct. >> >> I'll apreciate any solution to this problem. >> >> On Fri, Jun 27, 2008 at 3:44 PM, Lennin Caro >> wrote: >> >>> ok, let try this ..... >>> >>> open the pg_hba.conf and check the line >>> >>> host all all 127.0.0.1/32 trust >>> >>> and change to >>> >>> host all all 127.0.0.1/32 password >>> >>> >>> >>> --- On *Thu, 6/26/08, Domingo Alvarez Duarte *wrote: >>> >>> From: Domingo Alvarez Duarte >>> Subject: Re: [ADMIN] Extended security/restriction to any role with login >>> access >>> To: lennin.caro@yahoo.com >>> Cc: "Carol Walter" , pgsql-admin@postgresql.org >>> Date: Thursday, June 26, 2008, 9:45 PM >>> >>> >>> I did the following: >>> -Connect as superuser postgres with pgadmin and create a user -> noaccess >>> CREATE ROLE noaccess LOGIN >>> NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; >>> >>> -Disconnect from the server >>> -Connect to the server with user 'noaccess' through pgadmin3, and I can >>> see all databases/functions/schemas/roles. >>> >>> What am I missing ? >>> >>> On Thu, Jun 26, 2008 at 8:44 PM, Lennin Caro >>> wrote: >>> >>>> hello... >>>> >>>> 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 >>>> >>>> >>>> >>> >>> >> >> > > ------=_Part_22131_30797159.1214602418787 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Ok  !

I could revoke privileges to postgres.pg_catalog from public but now new users can login but can't see anything, even when I grant access to then on one view I've created on a new database.

Can someone give a detailed stepe by step on how to achieve this:

- Probably change postgres and template1 to disallow access to public group by default.
- After changes new users without superuser privileges or owned objects can't see anything.
- Once granted access to a part of a database let's say a view, the granted user can see it.

Let's start with fresh default postgresql installation.
Make the necessary changes.
Create to users with login access only, user1 and user2.
Create a new database db1 with owner user1.
Right now user1 can login and access his database db1. And user2 can login but can't see anithing.
Now user1 create a table table1 in his public schema and a view  view1 on that table, he grants select privilege to user2.
Now user2 can connect and see only  db1.public.view1

That is wat I want to achive, and I think that  more people  will  need something like this too.
 
Thanks again in advance for any help on the matter !
On Fri, Jun 27, 2008 at 9:10 PM, Lennin Caro <lennin.caro@yahoo.com> wrote:
in pgadmin3:

Menu -> Display
check the option "System Object"

this show you the catalogs of postgresql "pg_xxxxxxxx"

change the grants to users  here


--- On Fri, 6/27/08, Domingo Alvarez Duarte <mingodad@gmail.com> wrote:
From: Domingo Alvarez Duarte <mingodad@gmail.com>
Subject: Re: [ADMIN] Extended security/restriction to any role with login access
To: lennin.caro@yahoo.com
Cc: "Carol Walter" <walterc@indiana.edu>, pgsql-admin@postgresql.org
Date: Friday, June 27, 2008, 6:31 PM


How can I change that ?
I mean make the catalog of tables, function and roles private and only accessible to granted users ?

Someon mentioned once to make changes in template1, wich changes will be nneded ? In case this is possible.

Thanks for any help/sugestion !

On Fri, Jun 27, 2008 at 5:12 PM, Lennin Caro <lennin.caro@yahoo.com> wrote:

Ok...

the catalog of tables, function and roles are public. Pgadmin use the catalog to create the tree of databases, function and roles. The user can see this but cant change this. In oracle the catalog of user, tables, function are public


--- On Fri, 6/27/08, Domingo Alvarez Duarte <mingodad@gmail.com> wrote:
From: Domingo Alvarez Duarte <mingodad@gmail.com>
Subject: Re: [ADMIN] Extended security/restriction to any role with login access
To: lennin.caro@yahoo.com
Cc: "Carol Walter" <walterc@indiana.edu>, pgsql-admin@postgresql.org
Date: Friday, June 27, 2008, 2:35 PM


Look this isn't the point I know what can be done with pg_hba.conf

The main point is:

When I create a postgresql user and grant to it only access to part of a database (let's say one view). I'm expecting that the server will honor it. But right now postgresql server isn't.

This is the reason I'm writing here and tried too hackers list but no one seems to understand the importance of this point.

Maybe it's my fault of knowledge but till now no one showed me how to get the expected result, people try to tell me how to use firewall, change the way of build my application, they don't understand the point. I'll repeat it again:


When I create a postgresql user and grant to it only access to part of a database (let's say one view). I'm expecting that the server will honor it. But right now postgresql server isn't.

Actually any user with login access can see all databases/roles/functions/table-definitions/triggers. What I thinks isn't correct.

I'll apreciate any solution to this problem.

On Fri, Jun 27, 2008 at 3:44 PM, Lennin Caro <lennin.caro@yahoo.com> wrote:
ok, let try this .....

open the pg_hba.conf and check the line

host    all         all         127.0.0.1/32          trust

and change to

host    all         all         127.0.0.1/32          password



--- On Thu, 6/26/08, Domingo Alvarez Duarte <mingodad@gmail.com> wrote:
From: Domingo Alvarez Duarte <mingodad@gmail.com>

Subject: Re: [ADMIN] Extended security/restriction to any role with login access
To: lennin.caro@yahoo.com
Cc: "Carol Walter" <walterc@indiana.edu>, pgsql-admin@postgresql.org
Date: Thursday, June 26, 2008, 9:45 PM


I did the following:
-Connect as superuser postgres with pgadmin and create a user -> noaccess
CREATE ROLE noaccess LOGIN
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

-Disconnect from the server
-Connect to the server with user 'noaccess' through pgadmin3, and I can see all databases/functions/schemas/roles.

What am I missing ?

On Thu, Jun 26, 2008 at 8:44 PM, Lennin Caro <lennin.caro@yahoo.com> wrote:
hello...

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









------=_Part_22131_30797159.1214602418787--