From: Bob Lunney <bob_lunney@yahoo.com>
To: Juan Cuervo \(Quality Telecom\) <juanrcuervo@quality-telecom.net>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: revoked permissions on table still allows users to see table's structure
Date: Fri, 22 Jul 2011 07:14:09 -0700 (PDT)
Message-ID: <1311344049.24170.YahooMailNeo@web39701.mail.mud.yahoo.com> (raw)
In-Reply-To: <4E297A07.9090907@quality-telecom.net>
References: <4E28BF6E.8090302@quality-telecom.net>
<CAOR=d=1Zoo6K55wfkBpb_rx2Jb2ox_Lp5WBN1dGmYMLD3x=ADg@mail.gmail.com>
<4E297A07.9090907@quality-telecom.net>
Juan,
That is what schemas, permissions and search paths are for. You create multiple schemas, put the tables in the appropriate ones, grant usage permissions to those users that need access to the schemas and set the search path to search the schemas for objects. Below is the test case. It helps if you reset the psql prompt to display the current user:
\set PROMPT1 '%m:%>:%n:%/:%R%x%# '
As the database owner:
create schema seethat;
create schema seewhat;
create user al_low;
create user dee_ny;
grant usage on schema seethat to al_low, dee_ny;
grant usage on schema seewhat to al_low;
set search_path to seethat, seewhat, public;
create table seethat.open(open_id int);
create table seewhat.closed(closed_id int);
set session authorization al_low;
\d
set session authorization dee_ny;
\d
Hope that helps!
Bob Lunney
----- Original Message -----
From: Juan Cuervo (Quality Telecom) <juanrcuervo@quality-telecom.net>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-admin@postgresql.org
Sent: Friday, July 22, 2011 8:24 AM
Subject: Re: [ADMIN] revoked permissions on table still allows users to see table's structure
Hi Scott
Thanks for your answer.
It should be a way to prevent this from normal users who only need
access to a set of tables, a view or even a store procedure. (Maybe a
VIEW_SCHEMA privilege of roles?). View a table's structure should only
be allowed to users who has at least one privilege on the table.
It doesnt make much sense to me that every user with access to the
database , would be able to see the whole database design.
Do you know if this is common in other RDBMS ?
Regards,
Juan R. Cuervo Soto
Quality Telecom Ltd
www.quality-telecom.net
PBX : (575) 3693300
CEL : (57) 301-4174865
El 21/07/2011 08:48 p.m., Scott Marlowe escribió:
> On Thu, Jul 21, 2011 at 6:08 PM, Juan Cuervo (Quality Telecom)
> <juanrcuervo@quality-telecom.net> wrote:
>> Hi All
>>
>> I'm new to the list, but have a few years as postgres user. I want to share
>> what I consider a rare behavior of postgresql regarding database object's
>> premissions:
>>
>> I have noticed that there is no way (at least no one I know) to prevent a
>> user from seeing the table's structures in a database.
>>
>> Is this a normal behavior of the product ?
> Yep. Completely normal.
>
>> Is there a way to prevent a user from seeing my table's, procedure's and
>> function's code ?
> Don't let them connect to the db? That's all I can think of.
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bob_lunney@yahoo.com, juanrcuervo@quality-telecom.net
Subject: Re: revoked permissions on table still allows users to see table's structure
In-Reply-To: <1311344049.24170.YahooMailNeo@web39701.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox