Postgres Pro
Downloads
Home   >   mailing lists

Thread: relacl parsing method?

relacl parsing method?

From
Josip Rodin
Date:
Hi,

I want to find out whether a user has a select privilege on a particular
database. This is what I see when it does:

# select relacl from pg_class where relname = 'mydbtable';
                                  relacl
--------------------------------------------------------------------------
 {mydbname=arwdxt/mydbname,mydbuser=r/mydbname}
(1 row)

Is this the best way to parse that easily from within PostgreSQL:

# select 1 from pg_class where relname = 'mydbtable' and relacl ~ 'mydbuser=r/mydbname';
 ?column?
----------
        1
(1 row)

# select 1 from pg_class where relname = 'mydbtable' and relacl ~ 'mydbuser=w/mydbname';
 ?column?
----------
(0 rows)

Where is this documented? I tried searching for 'relacl' and 'aclitem' in
the docs, but didn't come up with much.

I did find a Perl module at http://search.cpan.org/~dwheeler/Pg-Priv-0.10/
that seems to extract relacl and parse it on its own, which sounds like a
kludge.

(Please Cc: replies, I'm not subscribed. TIA.)

--
     2. That which causes joy or happiness.

Re: relacl parsing method?

From
Josip Rodin
Date:
On Tue, Feb 16, 2010 at 12:22:29PM +0100, joy wrote:
> I want to find out whether a user has a select privilege on a particular
> database. This is what I see when it does:
>
> # select relacl from pg_class where relname = 'mydbtable';
>                                   relacl
> --------------------------------------------------------------------------
>  {mydbname=arwdxt/mydbname,mydbuser=r/mydbname}
> (1 row)
>
> Is this the best way to parse that easily from within PostgreSQL:
>
> # select 1 from pg_class where relname = 'mydbtable' and relacl ~ 'mydbuser=r/mydbname';
>  ?column?
> ----------
>         1
> (1 row)
>
> # select 1 from pg_class where relname = 'mydbtable' and relacl ~ 'mydbuser=w/mydbname';
>  ?column?
> ----------
> (0 rows)

Hmm, sorry, it looks like the string after the slash (/) is grantor, rather
than database name. If I omit it, then it warns about defaulting grantor to
user ID 10. Is there any way to check for any grantor?

--
     2. That which causes joy or happiness.

Re: relacl parsing method?

From
Tom Lane
Date:
Josip Rodin <joy@entuzijast.net> writes:
> I want to find out whether a user has a select privilege on a particular
> database. This is what I see when it does:

Consider using has_table_privilege() instead of reading the ACL for
yourself.

            regards, tom lane

Re: relacl parsing method?

From
Alvaro Herrera
Date:
Josip Rodin wrote:
> Hi,
>
> I want to find out whether a user has a select privilege on a particular
> database.


You're probably better off using the has_foo_privilege family of
functions, e.g., has_table_privilege().

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.