Message-ID: <40929453.10501@deg.cc>
Date: Fri, 30 Apr 2004 14:00:51 -0400
From: Pallav Kalva <pkalva@deg.cc>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040308
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "scott.marlowe" <scott.marlowe@ihs.com>, pgsql-sql@postgresql.org
Subject: Re: Permissions not working
References: <Pine.LNX.4.33.0404291522430.10983-100000@css120.ihs.com>
	<40917768.8040107@deg.cc> <17807.1083280047@sss.pgh.pa.us>
	<40925695.8060908@deg.cc> <26201.1083341979@sss.pgh.pa.us>
In-Reply-To: <26201.1083341979@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

>Pallav Kalva <pkalva@deg.cc> writes:
>  
>
>>usps=> \z citystate_alias
>>                                                      Access privileges 
>>for database "usps"
>> Schema |      Table      
>>|                                                   Access privileges
>>--------+-----------------+-----------------------------------------------------------------------------------------------------------------------
>> public | citystate_alias | 
>>{postgres=a*r*w*d*R*x*t*/postgres,=r/postgres,usps=arwdRxt/postgres,"group 
>>100=r/usps","group ea_development=r/usps"}
>>(1 row)
>>    
>>
>
>It looks to me like (a) this table is owned by postgres not usps, and
>(b) postgres has granted SELECT permission to PUBLIC (that's what the
>"=r/postgres" part means).  The usps user isn't going to be able to
>revoke that because he doesn't own the table.
>
>It does seem like you've found a bug of some kind though: the above
>shows that user usps does not have GRANT OPTION rights of any kind
>(there are no stars in his privilege list).  So how was he able to grant
>SELECT rights to those two groups?  Do you have the exact sequence of
>GRANT and REVOKE operations that were performed on this table?  What
>PG version is this, exactly?
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
>  
>
I am using Postgres 7.4.2 version. I dont have exact sequence of GRANT 
and REVOKE
commands. and as i told earlier I created the database first and then 
dumped it from the backups.
The table infact is owned by 'usps' user here is the output from \dt for 
that table

usps=> \dt
             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+-------
 public | citystate_alias  | table | usps

Also here is the privileges information from information_schema tables. 
Is there a way to REVOKE these
privileges ?

usps=> select * from information_schema.table_privileges where 
table_name = 'citystate_alias';
 grantor  |    grantee     | table_catalog | table_schema |   
table_name    | privilege_type | is_grantable | with_hierarchy
----------+----------------+---------------+--------------+-----------------+----------------+--------------+----------------
 postgres | usps           | usps          | public       | 
citystate_alias | SELECT         | NO           | NO
 postgres | PUBLIC         | usps          | public       | 
citystate_alias | SELECT         | NO           | NO
 usps     | ea_development | usps          | public       | 
citystate_alias | SELECT         | NO           | NO
 postgres | usps           | usps          | public       | 
citystate_alias | DELETE         | NO           | NO
 postgres | usps           | usps          | public       | 
citystate_alias | INSERT         | NO           | NO
 postgres | usps           | usps          | public       | 
citystate_alias | UPDATE         | NO           | NO
 postgres | usps           | usps          | public       | 
citystate_alias | REFERENCES     | NO           | NO
 postgres | usps           | usps          | public       | 
citystate_alias | RULE           | NO           | NO
 postgres | usps           | usps          | public       | 
citystate_alias | TRIGGER        | NO           | NO
(9 rows)