Re: [SQL] Permissions not working

[Pallav Kalva]

Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> 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)


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org