Thread: [RFC] grants vs. inherited tables

[RFC] grants vs. inherited tables

From
Marko Kreen
Date:
Hello,

I tried to generalize a function that creates partitions
for a table and found out it's impossible to do it for grants.

Basically, what I want is a child table that takes it's grants
from parent table.  IMHO quite reasonable request.  But I don't
see a way to do it in pl/pgsql.  (Writing parser in plpgsql
for aclitemout() output does not count.)

The form for the create statement is:
 CREATE TABLE part (   [pre-9.0]    LIKE parent INCLUDING INDEXES INCLUDING CONSTRAINTS   [9.0+]       LIKE parent
INCLUDINGALL    -- skips grants ) INHERITS (parent);
 

Unless I'm missing something obvious, there is no way to take grants
from parent table.

My suggestions:

1) Have 'GRANTS' option for LIKE.  Seems obvious.

2) Include 'GRANTS' option in 'ALL'.  Also obvious.

3) Have a way to format aclitem into something  that can used to create GRANT statement easily.  Eg:
    pg_get_privilege_info(         IN priv aclitem,  OUT rolename text,  OUT privlist text[],  OUT
privlist_with_grant_optiontext[]);
 
  This allows doing complex introspection in pl/pgsql  and also helps tools that want to re-create table structure  in
otherdatabases.
 

Although 1)+2) and 3) seem like alternatives, I suggest doing all of them,
thus improving GRANT usage across the board.

Comments?

-- 
marko

NB: this mail is about designing and accepting TODO-items.
I might do them myself sometime, but I don't mind if anyone
implements them before me..



Re: [RFC] grants vs. inherited tables

From
Alvaro Herrera
Date:
Excerpts from Marko Kreen's message of jue dic 29 15:04:49 -0300 2011:

> 3) Have a way to format aclitem into something
>    that can used to create GRANT statement easily.  Eg:
>
>      pg_get_privilege_info(
>           IN priv aclitem,
>       OUT rolename text,
>       OUT privlist text[],
>       OUT privlist_with_grant_option text[]);
>
>    This allows doing complex introspection in pl/pgsql
>    and also helps tools that want to re-create table structure
>    in other databases.

aclexplode?

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: [RFC] grants vs. inherited tables

From
Marko Kreen
Date:
On Thu, Dec 29, 2011 at 03:12:50PM -0300, Alvaro Herrera wrote:
> Excerpts from Marko Kreen's message of jue dic 29 15:04:49 -0300 2011:
> > 3) Have a way to format aclitem into something
> >    that can used to create GRANT statement easily.  Eg:
> > 
> >      pg_get_privilege_info(
> >           IN priv aclitem,
> >       OUT rolename text,
> >       OUT privlist text[],
> >       OUT privlist_with_grant_option text[]);
> > 
> >    This allows doing complex introspection in pl/pgsql
> >    and also helps tools that want to re-create table structure
> >    in other databases.
> 
> aclexplode?

I guess that decides the name.  :)

-- 
marko



Re: [RFC] grants vs. inherited tables

From
Alvaro Herrera
Date:
Excerpts from Marko Kreen's message of jue dic 29 15:22:49 -0300 2011:
>
> On Thu, Dec 29, 2011 at 03:12:50PM -0300, Alvaro Herrera wrote:
> > Excerpts from Marko Kreen's message of jue dic 29 15:04:49 -0300 2011:
> > > 3) Have a way to format aclitem into something
> > >    that can used to create GRANT statement easily.  Eg:
> > >
> > >      pg_get_privilege_info(
> > >           IN priv aclitem,
> > >       OUT rolename text,
> > >       OUT privlist text[],
> > >       OUT privlist_with_grant_option text[]);
> > >
> > >    This allows doing complex introspection in pl/pgsql
> > >    and also helps tools that want to re-create table structure
> > >    in other databases.
> >
> > aclexplode?
>
> I guess that decides the name.  :)

I have the (hopefully wrong) impression that you're missing the fact
that it already exists, at least in 9.0.

I have a backported version of it we wrote for a customer, in case
you're interested on using it in previous releases.  Not that it's all
that difficult to write ...

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: [RFC] grants vs. inherited tables

From
Marko Kreen
Date:
On Thu, Dec 29, 2011 at 11:11:22PM -0300, Alvaro Herrera wrote:
> 
> Excerpts from Marko Kreen's message of jue dic 29 15:22:49 -0300 2011:
> > 
> > On Thu, Dec 29, 2011 at 03:12:50PM -0300, Alvaro Herrera wrote:
> > > Excerpts from Marko Kreen's message of jue dic 29 15:04:49 -0300 2011:
> > > > 3) Have a way to format aclitem into something
> > > >    that can used to create GRANT statement easily.  Eg:
> > > > 
> > > >      pg_get_privilege_info(
> > > >           IN priv aclitem,
> > > >       OUT rolename text,
> > > >       OUT privlist text[],
> > > >       OUT privlist_with_grant_option text[]);
> > > > 
> > > >    This allows doing complex introspection in pl/pgsql
> > > >    and also helps tools that want to re-create table structure
> > > >    in other databases.
> > > 
> > > aclexplode?
> > 
> > I guess that decides the name.  :)
> 
> I have the (hopefully wrong) impression that you're missing the fact
> that it already exists, at least in 9.0.

You are right, I missed it.  For quite obvious reason:
 $ grep -ri aclexplode doc/ $

Is there a good reason why it's undocumented?  Internal/unstable API?
I better avoid it then.  But I would like to have this or similar
function as part of public API.

Although this hints also to an obvious area that I shouldn't
have missed - the grants can be seen from information_schema...

I guess the 3) is covered then.

-- 
marko



Re: [RFC] grants vs. inherited tables

From
Dimitri Fontaine
Date:
Marko Kreen <markokr@gmail.com> writes:
> I tried to generalize a function that creates partitions
> for a table and found out it's impossible to do it for grants.
>
> Basically, what I want is a child table that takes it's grants
> from parent table.  IMHO quite reasonable request.  But I don't
> see a way to do it in pl/pgsql.  (Writing parser in plpgsql
> for aclitemout() output does not count.)

We solved that manually in https://github.com/slardiere/PartMgr, maybe
you will find it useful for pre-9.2 releases. See function
partition.grant() and partition.setgrant() in part_api.sql.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: [RFC] grants vs. inherited tables

From
Marko Kreen
Date:
On Mon, Jan 02, 2012 at 12:31:13PM +0100, Dimitri Fontaine wrote:
> Marko Kreen <markokr@gmail.com> writes:
> > I tried to generalize a function that creates partitions
> > for a table and found out it's impossible to do it for grants.
> >
> > Basically, what I want is a child table that takes it's grants
> > from parent table.  IMHO quite reasonable request.  But I don't
> > see a way to do it in pl/pgsql.  (Writing parser in plpgsql
> > for aclitemout() output does not count.)
> 
> We solved that manually in https://github.com/slardiere/PartMgr, maybe
> you will find it useful for pre-9.2 releases. See function
> partition.grant() and partition.setgrant() in part_api.sql.

Thanks, thats interesting.  Here is my current state:
 https://github.com/markokr/skytools/blob/master/sql/dispatch/create_partition.sql

which uses info-schema for grants, which seems nicer than
parsing, but still not as nice as "including all".

-- 
marko



Re: [RFC] grants vs. inherited tables

From
Robert Haas
Date:
On Fri, Dec 30, 2011 at 4:25 AM, Marko Kreen <markokr@gmail.com> wrote:
>> I have the (hopefully wrong) impression that you're missing the fact
>> that it already exists, at least in 9.0.
>
> You are right, I missed it.  For quite obvious reason:
>
>  $ grep -ri aclexplode doc/
>  $
>
> Is there a good reason why it's undocumented?  Internal/unstable API?
> I better avoid it then.  But I would like to have this or similar
> function as part of public API.

I don't see any real reason why we couldn't document this one.  It
returns OIDs, but that's the name of the game if you're doing anything
non-trivial with PostgreSQL system catalogs.  Off-hand I'm not quite
sure which section of the documentation would be appropriate, though.
It looks like the functions we provide are mostly documented in
chapter 9, Functions and Operators.  Section 9.23 on "System
Information Functions" seems like it's probably the closest fit...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company