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?
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 <shahdipti1980@xxxxxxxxx> 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,DiptiOn Fri, Apr 16, 2010 at 4:32 PM, dipti shah <shahdipti1980@xxxxxxxxx> 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';techtable | {postgres=arwdDxt/postgres,=ar/postgres,user1=ar/postgres}
relname | relacl
--------------+--------------------------------------------------------------
(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,DiptiOn Thu, Apr 15, 2010 at 4:16 PM, dipti shah <shahdipti1980@xxxxxxxxx> wrote:
Okay. Thanks.Dipti.On Thu, Apr 15, 2010 at 3:20 PM, A. Kretschmer <andreas.kretschmer@xxxxxxxxxxxxxx> 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 permissionsRight, you have to name all privileges.
> explicitly? Did I miss anything?
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
