Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1A8D2650E91 for ; Fri, 27 Jun 2008 16:10:28 -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 25286-09 for ; Fri, 27 Jun 2008 16:10:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from n1d.bullet.mail.ac4.yahoo.com (n1d.bullet.mail.ac4.yahoo.com [76.13.13.85]) by postgresql.org (Postfix) with SMTP id 7DCC6650E7F for ; Fri, 27 Jun 2008 16:10:13 -0300 (ADT) Received: from [76.13.13.25] by n1.bullet.mail.ac4.yahoo.com with NNFMP; 27 Jun 2008 19:10:11 -0000 Received: from [76.13.10.183] by t4.bullet.mail.ac4.yahoo.com with NNFMP; 27 Jun 2008 19:10:11 -0000 Received: from [127.0.0.1] by omp124.mail.ac4.yahoo.com with NNFMP; 27 Jun 2008 19:10:11 -0000 X-Yahoo-Newman-Property: ymail-5 X-Yahoo-Newman-Id: 509578.15019.bm@omp124.mail.ac4.yahoo.com Received: (qmail 12329 invoked by uid 60001); 27 Jun 2008 19:10:11 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Received:X-Mailer:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Message-ID; b=E5IdCN1WZbOEI7af7FJDCxupdnOnkDrh+gvAkIowyLK/QEasMihm13/U0UR7sweVKP0MVNSY7n0r93gtBPiQyFhbITDMvReTzKMEQoA1JsJC09A2NLZ1dBYVaNxUXiP+JOqOUdNJtl8RKU3ZwVk15T+o/ArZdMoMTthn+HxLmOA=; Received: from [209.88.228.11] by web59512.mail.ac4.yahoo.com via HTTP; Fri, 27 Jun 2008 12:10:11 PDT X-Mailer: YahooMailWebService/0.7.199 Date: Fri, 27 Jun 2008 12:10:11 -0700 (PDT) From: Lennin Caro Reply-To: lennin.caro@yahoo.com Subject: Re: Extended security/restriction to any role with login access To: Domingo Alvarez Duarte Cc: Carol Walter , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-918300945-1214593811=:10145" Message-ID: <408751.10145.qm@web59512.mail.ac4.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.802 tagged_above=0 required=5 tests=AWL=-0.289, HTML_MESSAGE=0.001, NORMAL_HTTP_TO_IP=0.001, RCVD_IN_BL_SPAMCOP_NET=2.188, URIBL_RHS_DOB=0.901 X-Spam-Level: ** X-Archive-Number: 200806/232 X-Sequence-Number: 29570 --0-918300945-1214593811=:10145 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable in pgadmin3: Menu -> Display check the option "System Object" this show you the catalogs of postgresql "pg_xxxxxxxx" change the grants to users=A0 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 a= ccess 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 acce= ssible to granted users ? Someon mentioned once to make changes in template1, wich changes will be nn= eded ? 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 catal= og to create the tree of databases, function and roles. The user can see th= is but cant change this. In oracle the catalog of user, tables, function ar= e 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 a= ccess 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 databa= se (let's say one view). I'm expecting that the server will honor it. But r= ight now postgresql server isn't. This is the reason I'm writing here and tried too hackers list but no one s= eems to understand the importance of this point. Maybe it's my fault of knowledge but till now no one showed me how to get t= he expected result, people try to tell me how to use firewall, change the w= ay 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/t= able-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=20 host=A0=A0=A0 all=A0=A0=A0=A0=A0=A0=A0=A0 all=A0=A0=A0=A0=A0=A0=A0=A0 127.0= ..0.1/32=A0=A0=A0=A0=A0=A0=A0=A0=A0 trust and change to=20 host=A0=A0=A0 all=A0=A0=A0=A0=A0=A0=A0=A0 all=A0=A0=A0=A0=A0=A0=A0=A0 127.0= ..0.1/32=A0=A0=A0=A0=A0=A0=A0=A0=A0 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 a= ccess 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 =A0 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 dat= abases you cant see the struct of this. check waht user use pgadmin3 for connect to databases=20 create groups and add privileges to the group later add the users to the gr= oup =20 =20 =0A=0A=0A --0-918300945-1214593811=:10145 Content-Type: text/html; charset=us-ascii
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








--0-918300945-1214593811=:10145--