How to get whether user has ALL permissions on table?

Started by dipti shahalmost 16 years ago8 messagesgeneral

shahdipti1980@gmail.com

almost 16 years ago

Hi, I have granted ALL permissions on 'techtable' to 'user1'.

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=arwdDxt/postgres}
(1 row)

Could anyone please tell me if there is any function or command in
PostGreSql which returns True if given user has ALL permissions on given
table? Is there any alternative way to do this. I have table and user names
and want to know whether user has ALL permissions on table or not.

Thanks,
Ditpi

andreas.kretschmer@schollglas.com

almost 16 years ago

In reply to: dipti shah (#1)

Re: How to get whether user has ALL permissions on table?

In response to dipti shah :

Hi, I have granted ALL permissions on 'techtable' to 'user1'.
ï¿½
ï¿½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=arwdDxt/postgres}
(1 row)
ï¿½
Could anyone please tell me if there is any function or command in PostGreSql
which returns True if given user has ALL permissions on given table? Is there

Sure, read
http://www.postgresql.org/docs/8.4/interactive/functions-info.html

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99

shahdipti1980@gmail.com

almost 16 years ago

In reply to: A. Kretschmer (#2)

Re: How to get whether user has ALL permissions on table?

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"

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=arwdDxt/postgres}
(1 row)

Do I have to run this command as below which includes all permissions
explicitly? Did I miss anything?

techdb=# SELECT has_table_privilege('user1', 'techtable', 'SELECT, INSERT,
UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER');
has_table_privilege
---------------------
t
(1 row)
Thanks,
Dipti

On Thu, Apr 15, 2010 at 1:27 PM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:

Show quoted text

In response to dipti shah :

Hi, I have granted ALL permissions on 'techtable' to 'user1'.

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=arwdDxt/postgres}

(1 row)

Could anyone please tell me if there is any function or command in

PostGreSql

which returns True if given user has ALL permissions on given table? Is

there

Sure, read
http://www.postgresql.org/docs/8.4/interactive/functions-info.html

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

andreas.kretschmer@schollglas.com

almost 16 years ago

In reply to: dipti shah (#3)

Re: How to get whether user has ALL permissions on table?

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

shahdipti1980@gmail.com

almost 16 years ago

In reply to: A. Kretschmer (#4)

Re: How to get whether user has ALL permissions on table?

Okay. Thanks.

Dipti.

On Thu, Apr 15, 2010 at 3:20 PM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:

Show quoted text

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

shahdipti1980@gmail.com

almost 16 years ago

In reply to: dipti shah (#5)

Re: How to get whether user has ALL permissions on table?

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 <shahdipti1980@gmail.com> wrote:

Show quoted text

Okay. Thanks.

Dipti.

On Thu, Apr 15, 2010 at 3:20 PM, A. Kretschmer <
andreas.kretschmer@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

shahdipti1980@gmail.com

almost 16 years ago

In reply to: dipti shah (#6)

Re: How to get whether user has ALL permissions on table?

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 <shahdipti1980@gmail.com> wrote:

Show quoted text

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 <shahdipti1980@gmail.com>wrote:

Okay. Thanks.

Dipti.

On Thu, Apr 15, 2010 at 3:20 PM, A. Kretschmer <
andreas.kretschmer@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

shahdipti1980@gmail.com

almost 16 years ago

In reply to: dipti shah (#7)

Re: How to get whether user has ALL permissions on table?

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 <shahdipti1980@gmail.com> wrote:

Show quoted text

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 <shahdipti1980@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 <shahdipti1980@gmail.com>wrote:

Okay. Thanks.

Dipti.

On Thu, Apr 15, 2010 at 3:20 PM, A. Kretschmer <
andreas.kretschmer@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