Re: Permissions not working

Tom Lane wrote:

>Pallav Kalva <pkalva(at)deg(dot)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)