Thread

1. information schema/aclexplode doesn't know about default privileges

   Peter Eisentraut <peter_e@gmx.net> — 2011-11-27T18:47:21Z

   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?
   
   
   
   

2. Re: information schema/aclexplode doesn't know about default privileges

   Tom Lane <tgl@sss.pgh.pa.us> — 2011-11-27T22:29:50Z

   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
   
   

3. Re: information schema/aclexplode doesn't know about default privileges

   Peter Eisentraut <peter_e@gmx.net> — 2012-01-02T04:43:46Z

   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.
   

4. Re: information schema/aclexplode doesn't know about default privileges

   Jim Nasby <jim@nasby.net> — 2012-01-04T00:06:39Z

   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 as well as info_schema.
   --
   Jim C. Nasby, Database Architect                   jim@nasby.net
   512.569.9461 (cell)                         http://jim.nasby.net
   
   
   
   

5. Re: information schema/aclexplode doesn't know about default privileges

   Peter Eisentraut <peter_e@gmx.net> — 2012-01-09T18:23:59Z

   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.
   

6. Re: information schema/aclexplode doesn't know about default privileges

   Abhijit Menon-Sen <ams@toroid.org> — 2012-01-26T16:10:26Z

   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
   
   

7. Re: information schema/aclexplode doesn't know about default privileges

   Lionel Elie Mamane <lionel@mamane.lu> — 2012-01-31T17:11:00Z

   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