Re: [RFC] grants vs. inherited tables

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 INCLUDING ALL -- 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_option text[]);

This allows doing complex introspection in pl/pgsql
and also helps tools that want to re-create table structure
in other databases.

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..

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Marko Kreen <markokr(at)gmail(dot)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

On Mon, Jan 02, 2012 at 12:31:13PM +0100, Dimitri Fontaine wrote:
> Marko Kreen <markokr(at)gmail(dot)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

On Fri, Dec 30, 2011 at 4:25 AM, Marko Kreen <markokr(at)gmail(dot)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