DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=qfNtLOZD8fnSRVabz52Mz6XokFeHXsIG0ZDl7ST9Jfh8aNhx5fkhjeIs3xbQiShrPRT6pcB+zL8QX1mSZrwQfXjVjONeq6FkN0AO6YqQicXyck9blfA/G4vaUsonOOHJl+VfGj3rVPBoHh4yc54GT9qHwibAwUjZL/9H2riGugw=;
References: <4E28BF6E.8090302@quality-telecom.net>
	<CAOR=d=1Zoo6K55wfkBpb_rx2Jb2ox_Lp5WBN1dGmYMLD3x=ADg@mail.gmail.com>
	<4E297A07.9090907@quality-telecom.net>
Message-ID: <1311344049.24170.YahooMailNeo@web39701.mail.mud.yahoo.com>
Date: Fri, 22 Jul 2011 07:14:09 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Reply-To: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: revoked permissions on table still allows users to see table's
	structure
To: "Juan Cuervo \(Quality Telecom\)" <juanrcuervo@quality-telecom.net>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <4E297A07.9090907@quality-telecom.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Juan,=0A=0AThat is what schemas, permissions and search paths are for. =A0Y=
ou create multiple schemas, put the tables in the appropriate ones, grant u=
sage permissions to those users that need access to the schemas and set the=
 search path to search the schemas for objects. =A0Below is the test case. =
=A0It helps if you reset the psql prompt to display the current user:=0A=0A=
\set PROMPT1 '%m:%>:%n:%/:%R%x%# '=0A=0AAs the database owner:=0A=0Acreate =
schema seethat;=0Acreate schema seewhat;=0Acreate user al_low;=0Acreate use=
r dee_ny;=0Agrant usage on schema seethat to al_low, dee_ny;=0Agrant usage =
on schema seewhat to al_low;=0Aset search_path to seethat, seewhat, public;=
=0A=0Acreate table seethat.open(open_id int);=0Acreate table seewhat.closed=
(closed_id int);=0A=0A=0Aset session authorization al_low;=0A=0A\d=0A=0A=0A=
set session authorization dee_ny;=0A=0A\d=0A=0A=0AHope that helps!=0A=0ABob=
 Lunney=0A=0A----- Original Message -----=0AFrom: Juan Cuervo (Quality Tele=
com) <juanrcuervo@quality-telecom.net>=0ATo: Scott Marlowe <scott.marlowe@g=
mail.com>=0ACc: pgsql-admin@postgresql.org=0ASent: Friday, July 22, 2011 8:=
24 AM=0ASubject: Re: [ADMIN] revoked permissions on table still allows user=
s to see table's structure=0A=0AHi Scott=0A=0AThanks for your answer.=0A=0A=
It should be a way to prevent this from normal users who only need =0Aacces=
s to a set of tables, a view or even a store procedure. (Maybe a =0AVIEW_SC=
HEMA privilege of roles?). View a table's structure should only =0Abe allow=
ed to users who has at least one privilege on the table.=0A=0AIt doesnt mak=
e much sense to me that every user with access to the =0Adatabase , would b=
e able to see the whole database design.=0A=0ADo you know if this is common=
 in other RDBMS ?=0A=0ARegards,=0A=0AJuan R. Cuervo Soto=0AQuality Telecom =
Ltd=0Awww.quality-telecom.net=0APBX : (575) 3693300=0ACEL : (57)=A0 301-417=
4865=0A=0A=0AEl 21/07/2011 08:48 p.m., Scott Marlowe escribi=F3:=0A> On Thu=
, Jul 21, 2011 at 6:08 PM, Juan Cuervo (Quality Telecom)=0A> <juanrcuervo@q=
uality-telecom.net>=A0 wrote:=0A>> Hi All=0A>>=0A>> I'm new to the list, bu=
t have a few years as postgres user. I want to share=0A>> what I consider a=
 rare behavior of postgresql regarding database object's=0A>> premissions:=
=0A>>=0A>> I have noticed that there is no way (at least no one I know) to =
prevent a=0A>> user from seeing the table's structures in a database.=0A>>=
=0A>> Is this a normal behavior of the product ?=0A> Yep.=A0 Completely nor=
mal.=0A>=0A>> Is there a way to prevent a user from seeing my table's, proc=
edure's and=0A>> function's code ?=0A> Don't let them connect to the db?=A0=
 That's all I can think of.=0A>=0A=0A-- =0ASent via pgsql-admin mailing lis=
t (pgsql-admin@postgresql.org)=0ATo make changes to your subscription:=0Aht=
tp://www.postgresql.org/mailpref/pgsql-admin=0A