Okay I got to know from http://www.postgresql.org/docs/8.4/interactive/functions-info.html that the has_table_privilege returns true if any of the listed privilege is held. Then how can I find whether user has all the specified permissions or not?
>From http://www.postgresql.org/docs/8.4/interactive/functions-info.html has_table_privilege checks whether a user can access a table in a particular way. The user can be specified by name or by OID (pg_authid.oid), or if the argument is omitted current_user is assumed. The table can be specified by name or by OID. (Thus, there are actually six variants of has_table_privilege, which can be distinguished by the number and types of their arguments.) When specifying by name, the name can be schema-qualified if necessary. The desired access privilege type is specified by a text string, which must evaluate to one of the values SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, or TRIGGER. Optionally, WITH GRANT OPTION can be added to a privilege type to test whether the privilege is held with grant option. Also, multiple privilege types can be listed separated by commas,* in which case the result will be true if any of the listed privileges is held.* (Case of the privilege string is not significant, and extra whitespace is allowed between but not within privilege names.) Could anyone please help me out. Thanks, Dipti On Fri, Apr 16, 2010 at 4:34 PM, dipti shah <shahdipti1...@gmail.com> wrote: > It is strange. If I remove both SELECT and INSERT then works fine but if > either of is there then it doesn't work. > techdb=> SELECT has_table_privilege('user1', 'techdb.techtable', 'UPDATE, > DELETE, TRUNCATE, REFERENCES, TRIGGER'); > > has_table_privilege > --------------------- > t > (1 row) > techdb=> > > Regards, > Dipti > On Fri, Apr 16, 2010 at 4:32 PM, dipti shah <shahdipti1...@gmail.com>wrote: > >> Hey Kretschemer, the has_table_privilege function returns true in >> following situation as well which is wrong. >> >> techdb=> select pc.relname, pc.relacl from pg_class pc, pg_namespace pn >> where pc.relnamespace=pn.oid and pn.nspname='techdb' and >> pc.relname='techtable'; >> relname | relacl >> >> --------------+-------------------------------------------------------------- >> techtable | {postgres=arwdDxt/postgres,=ar/postgres,user1=ar/postgres} >> (1 row) >> techdb=> SELECT has_table_privilege('user1', 'techdb.techtable', 'SELECT, >> UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'); >> >> has_table_privilege >> --------------------- >> t >> (1 row) >> techdb=> >> Note that user1 has only insert and select permissions on techtable but >> still has_table returns true for all permissions. Am I missing anything? >> >> Thanks, >> Dipti >> >> On Thu, Apr 15, 2010 at 4:16 PM, dipti shah <shahdipti1...@gmail.com>wrote: >> >>> Okay. Thanks. >>> >>> Dipti. >>> >>> On Thu, Apr 15, 2010 at 3:20 PM, A. Kretschmer < >>> andreas.kretsch...@schollglas.com> wrote: >>> >>>> In response to dipti shah : >>>> > Thanks Kretschmer but I have seen those function. The below query >>>> returns error >>>> > but you could see that 'user1' has ALL permissions on table >>>> 'techtable'. >>>> > >>>> > techdb=# SELECT has_table_privilege('user1', 'techtable', 'ALL'); >>>> > ERROR: unrecognized privilege type: "ALL" >>>> > >>>> > Do I have to run this command as below which includes all permissions >>>> > explicitly? Did I miss anything? >>>> >>>> Right, you have to name all privileges. >>>> >>>> The desired access privilege type is specified by a text string, which >>>> must evaluate to one of the values SELECT, INSERT, UPDATE, DELETE, >>>> TRUNCATE, REFERENCES, or TRIGGER. >>>> >>>> Andreas >>>> -- >>>> Andreas Kretschmer >>>> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) >>>> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 >>>> >>>> -- >>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-general >>>> >>> >>> >> >
