Thread: information schema/aclexplode doesn't know about default privileges

Try this:
   create function foo(int) returns int as $$ select $1 $$ language sql;
   select * from information_schema.routine_privileges;

This ought to show EXECUTE privilege on the new function, but it
doesn't, because proacl is null, and nothing in the information schema
handles that specially.

I've pondered some ways to fix that.  One would be to add a variant of
aclexplode() that takes a parameter telling which catalog the acl datum
came from, and aclexplode() could then substitute the data received
acldefault() for null values.  The other way would be to handle this
entirely in the information schema SQL (either using some coalesce calls
or perhaps a UNION).  But that would mean duplicating the knowledge of
acldefault() in a second remote place.  So I'm thinking that handling it
in aclexplode() would be better.

Comments?

Peter Eisentraut <peter_e@gmx.net> writes:
> This ought to show EXECUTE privilege on the new function, but it
> doesn't, because proacl is null, and nothing in the information schema
> handles that specially.

> I've pondered some ways to fix that.  One would be to add a variant of
> aclexplode() that takes a parameter telling which catalog the acl datum
> came from, and aclexplode() could then substitute the data received
> acldefault() for null values.  The other way would be to handle this
> entirely in the information schema SQL (either using some coalesce calls
> or perhaps a UNION).  But that would mean duplicating the knowledge of
> acldefault() in a second remote place.  So I'm thinking that handling it
> in aclexplode() would be better.

+1.  It would be a really bad idea for the acldefault() logic to be
duplicated someplace else, especially in SQL code where grepping for the
relevant macros wouldn't even find it.
        regards, tom lane

On sön, 2011-11-27 at 17:29 -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > This ought to show EXECUTE privilege on the new function, but it
> > doesn't, because proacl is null, and nothing in the information schema
> > handles that specially.
>
> > I've pondered some ways to fix that.  One would be to add a variant of
> > aclexplode() that takes a parameter telling which catalog the acl datum
> > came from, and aclexplode() could then substitute the data received
> > acldefault() for null values.  The other way would be to handle this
> > entirely in the information schema SQL (either using some coalesce calls
> > or perhaps a UNION).  But that would mean duplicating the knowledge of
> > acldefault() in a second remote place.  So I'm thinking that handling it
> > in aclexplode() would be better.
>
> +1.  It would be a really bad idea for the acldefault() logic to be
> duplicated someplace else, especially in SQL code where grepping for the
> relevant macros wouldn't even find it.

I figured the best and most flexible way to address this is to export
acldefault() as an SQL function and replace

    aclexplode(proacl)

with

    aclexplode(coalesce(proacl, acldefault('f', proowner)))

where 'f' here is something that is mapped to ACL_OBJECT_FUNCTION
internally.  AFAICT, there is no existing way to map an SQL-accessible
quantity to the ACL_OBJECT_* symbols, so I'll just have to make
something up.

WIP patch is attached.  If there are no objections to this approach,
I'll finish it up.

On Jan 1, 2012, at 10:43 PM, Peter Eisentraut wrote:
> I figured the best and most flexible way to address this is to export
> acldefault() as an SQL function and replace
>
>    aclexplode(proacl)
>
> with
>
>    aclexplode(coalesce(proacl, acldefault('f', proowner)))

It would be nice to provide a convenience function that does the coalesce for you. End users sometimes need this stuff
aswell as info_schema. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

On mån, 2012-01-02 at 06:43 +0200, Peter Eisentraut wrote:
> I figured the best and most flexible way to address this is to export
> acldefault() as an SQL function and replace
>
>     aclexplode(proacl)
>
> with
>
>     aclexplode(coalesce(proacl, acldefault('f', proowner)))
>
> where 'f' here is something that is mapped to ACL_OBJECT_FUNCTION
> internally.  AFAICT, there is no existing way to map an SQL-accessible
> quantity to the ACL_OBJECT_* symbols, so I'll just have to make
> something up.

Nobody had a better idea, so here is the final patch.  I adjusted the
regression tests a bit to avoid bloat from the now-visible owner
privileges.

At 2012-01-09 20:23:59 +0200, peter_e@gmx.net wrote:
>
> Nobody had a better idea, so here is the final patch.  I adjusted the
> regression tests a bit to avoid bloat from the now-visible owner
> privileges.

Patch applies, builds, and passes tests (and does report EXECUTE
privileges on a newly-created function). Code looks fine.

-- ams

On Mon, Jan 09, 2012 at 08:23:59PM +0200, Peter Eisentraut wrote:
> On mån, 2012-01-02 at 06:43 +0200, Peter Eisentraut wrote:
>> I figured the best and most flexible way to address this is to export
>> acldefault() as an SQL function and replace

>>     aclexplode(proacl)

>> with

>>     aclexplode(coalesce(proacl, acldefault('f', proowner)))

> Nobody had a better idea, so here is the final patch.

Thanks! This is important for the LibreOffice-PostgreSQL integration,
since LibreOffice uses the privilege information to determine whether
to let the user edit/insert data in the UI or not. It is thus
important for this information to be correct.

I currently work around that with a UNION, assuming that the default
acl is "owner has all rights".

-- 
Lionel