Message-ID: <4E28BF6E.8090302@quality-telecom.net>
Date: Thu, 21 Jul 2011 19:08:14 -0500
From: "Juan Cuervo (Quality Telecom)" <juanrcuervo@quality-telecom.net>
Organization: Quality Telecom
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:5.0) Gecko/20110624 Thunderbird/5.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: revoked permissions on table still allows users to see table's
	structure
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

I created a new user (user1) and do the following:

'revoke all on all tables in schema public from public;'

After that, user1 cant do select, inserts , etc from database's tables. 
But still is able to see my table's structure:

voicemax=>select * from tasks;
ERROR:  permission denied for relation tasks
voicemax=> delete from tasks where task_id=6;
ERROR:  permission denied for relation tasks
voicemax=> \d tasks
                                         Table "public.tasks"
      Column      |          Type          |                        
Modifiers
-----------------+------------------------+---------------------------------------------------------
  task_id         | bigint                 | not null
  task_name       | character varying(32)  | not null
  description     | character varying(128) |
  enabled         | integer                | default 0
Indexes:
     "tasks_task_name_key" UNIQUE, btree (task_name)

The same behavior occurs when this user is logged from pgAdmin. User can 
see all table's structure, even if have no privileges on database tables.

Is this a normal behavior of the product ?
Is there a way to prevent a user from seeing my table's, procedure's and 
function's code ?

Thanks in advance.

-- 
Juan R. Cuervo Soto
Quality Telecom Ltd
www.quality-telecom.net
PBX : (575) 3693300
CEL : (57)  301-4174865