Re: Permissions not working - Mailing list pgsql-sql

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 relationsSchema |       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           | NOpostgres | PUBLIC         | usps          | public       | 
citystate_alias | SELECT         | NO           | NOusps     | ea_development | usps          | public       | 
citystate_alias | SELECT         | NO           | NOpostgres | usps           | usps          | public       | 
citystate_alias | DELETE         | NO           | NOpostgres | usps           | usps          | public       | 
citystate_alias | INSERT         | NO           | NOpostgres | usps           | usps          | public       | 
citystate_alias | UPDATE         | NO           | NOpostgres | usps           | usps          | public       | 
citystate_alias | REFERENCES     | NO           | NOpostgres | usps           | usps          | public       | 
citystate_alias | RULE           | NO           | NOpostgres | usps           | usps          | public       | 
citystate_alias | TRIGGER        | NO           | NO
(9 rows)