Thread: [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
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..
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
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@commandprompt.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@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
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
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