Обсуждение: Rules for 6.4 finished
Bruce,
I'll send the patch itself directly to you. It's a bigger one
and I don't want to waste bandwidth on the list. Would you
please apply that one and forget the two others I posted
recently? The first rules patch (that is already applied) is
O.K.
This is the final state of the rule system for 6.4 after the
patch is applied:
Rewrite rules on relation level work fine now.
Event qualifications on insert/update/delete rules work
fine now.
I added the new keyword OLD to reference the CURRENT
tuple. CURRENT will be removed in 6.5.
Update rules can reference NEW and OLD in the rule
qualification and the actions.
Insert/update/delete rules on views can be established to
let them behave like real tables.
For insert/update/delete rules multiple actions are
supported now. The actions can also be surrounded by
parantheses to make psql happy. Multiple actions are
required if update to a view requires updates to multiple
tables.
Regular users are permitted to create/drop rules on
tables they have RULE permissions for
(DefineQueryRewrite() is now able to get around the
access restrictions on pg_rewrite). This enables view
creation for regular users too. This required an extra
boolean parameter to pg_parse_and_plan() that tells to
set skipAcl on all rangetable entries of the resulting
queries. There is a new function
pg_exec_query_acl_override() that could be used by
backend utilities to use this facility.
All rule actions (not only views) inherit the permissions
of the event relations owner. Sample: User A creates
tables T1 and T2, creates rules that log
INSERT/UPDATE/DELETE on T1 in T2 (like in the regression
tests for rules I created) and grants ALL but RULE on T1
to user B. User B can now fully access T1 and the
logging happens in T2. But user B cannot access T2 at
all, only the rule actions can. And due to missing RULE
permissions on T1, user B cannot disable logging.
Rules on the attribute level are disabled (they don't
work properly and since regular users are now permitted
to create rules I decided to disable them).
Rules on select must have exactly one action that is a
select (so select rules must be a view definition).
UPDATE NEW/OLD rules are disabled (still broken, but
triggers can do it).
There are two new system views (pg_rule and pg_view) that
show the definition of the rules or views so the db admin
can see what the users do. They use two new functions
pg_get_ruledef() and pg_get_viewdef() that are builtins.
The functions pg_get_ruledef() and pg_get_viewdef() could
be used to implement rule and view support in pg_dump.
PostgreSQL is now the only database system I know, that
has rewrite rules on the query level. All others (where I
found a rule statement at all) use stored database
procedures or the like (triggers as we call them) for
active rules (as some call them).
Future of the rule system:
The now disabled parts of the rule system (attribute
level, multiple actions on select and update new stuff)
require a complete new rewrite handler from scratch. The
old one is too badly wired up.
After 6.4 I'll start to work on a new rewrite handler,
that fully supports the attribute level rules, multiple
actions on select and update new. This will be available
for 6.5 so we get full rewrite rule capabilities.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
> This is the final state of the rule system for 6.4 after the
> patch is applied:
This is all neat stuff Jan. Thanks for working on it...
- Tom
>
> > This is the final state of the rule system for 6.4 after the
> > patch is applied:
>
> This is all neat stuff Jan. Thanks for working on it...
>
> - Tom
After playing around now with all that for a while I think
the only thing missing are some more attributes in the new
views. Currently the views are:
view pg_rule (
rulename name, -- name of the rule in pg_rewrite
definition text) -- SQL statement that defines the rule
view pg_view (
viewname name, -- name of the view in pg_class
definition text) -- SELECT statement that defines the view
For pg_rule it would be nice to show up the event relations
name and it's owners name. So one can select only the rules
belonging to a specific table or all the rules one user had
created. (this is already possible by joining with
pg_rewrite, pg_class and pg_user, but having the attributes
in pg_rule is easier)
For pg_view again the owners name would be nice.
Adding them would only require little changes to initdb.sh,
so I left them out until a discussion showed up all the
attributes that should be there.
Another topic is if we should create some more system views
at initdb time. I would find views telling ownership and
other information readable instead of Oid's very useful. As
for pg_rule and pg_view it would be possible to create a view
that describes the definition of an index instead of some
cryptic numbers. And another one for real tables where
indices and views are omitted would also be useful.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
> Bruce, > > I'll send the patch itself directly to you. It's a bigger one > and I don't want to waste bandwidth on the list. Would you > please apply that one and forget the two others I posted > recently? The first rules patch (that is already applied) is > O.K. > > This is the final state of the rule system for 6.4 after the > patch is applied: > > Rewrite rules on relation level work fine now. > > Event qualifications on insert/update/delete rules work > fine now. > > I added the new keyword OLD to reference the CURRENT > tuple. CURRENT will be removed in 6.5. > > Update rules can reference NEW and OLD in the rule > qualification and the actions. > > Insert/update/delete rules on views can be established to > let them behave like real tables. > > For insert/update/delete rules multiple actions are > supported now. The actions can also be surrounded by > parantheses to make psql happy. Multiple actions are > required if update to a view requires updates to multiple > tables. > > Regular users are permitted to create/drop rules on > tables they have RULE permissions for > (DefineQueryRewrite() is now able to get around the > access restrictions on pg_rewrite). This enables view > creation for regular users too. This required an extra > boolean parameter to pg_parse_and_plan() that tells to > set skipAcl on all rangetable entries of the resulting > queries. There is a new function > pg_exec_query_acl_override() that could be used by > backend utilities to use this facility. > > All rule actions (not only views) inherit the permissions > of the event relations owner. Sample: User A creates > tables T1 and T2, creates rules that log > INSERT/UPDATE/DELETE on T1 in T2 (like in the regression > tests for rules I created) and grants ALL but RULE on T1 > to user B. User B can now fully access T1 and the > logging happens in T2. But user B cannot access T2 at > all, only the rule actions can. And due to missing RULE > permissions on T1, user B cannot disable logging. > > Rules on the attribute level are disabled (they don't > work properly and since regular users are now permitted > to create rules I decided to disable them). > > Rules on select must have exactly one action that is a > select (so select rules must be a view definition). > > UPDATE NEW/OLD rules are disabled (still broken, but > triggers can do it). > > There are two new system views (pg_rule and pg_view) that > show the definition of the rules or views so the db admin > can see what the users do. They use two new functions > pg_get_ruledef() and pg_get_viewdef() that are builtins. > > The functions pg_get_ruledef() and pg_get_viewdef() could > be used to implement rule and view support in pg_dump. > > PostgreSQL is now the only database system I know, that > has rewrite rules on the query level. All others (where I > found a rule statement at all) use stored database > procedures or the like (triggers as we call them) for > active rules (as some call them). > > Future of the rule system: > > The now disabled parts of the rule system (attribute > level, multiple actions on select and update new stuff) > require a complete new rewrite handler from scratch. The > old one is too badly wired up. > > After 6.4 I'll start to work on a new rewrite handler, > that fully supports the attribute level rules, multiple > actions on select and update new. This will be available > for 6.5 so we get full rewrite rule capabilities. I appreciate you taking care of this for us. Thanks. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> > > > > This is the final state of the rule system for 6.4 after the > > > patch is applied: > > > > This is all neat stuff Jan. Thanks for working on it... > > > > - Tom > > After playing around now with all that for a while I think > the only thing missing are some more attributes in the new > views. Currently the views are: > > view pg_rule ( > rulename name, -- name of the rule in pg_rewrite > definition text) -- SQL statement that defines the rule > > view pg_view ( > viewname name, -- name of the view in pg_class > definition text) -- SELECT statement that defines the view > > For pg_rule it would be nice to show up the event relations > name and it's owners name. So one can select only the rules > belonging to a specific table or all the rules one user had > created. (this is already possible by joining with > pg_rewrite, pg_class and pg_user, but having the attributes > in pg_rule is easier) > > For pg_view again the owners name would be nice. > > Adding them would only require little changes to initdb.sh, > so I left them out until a discussion showed up all the > attributes that should be there. > > Another topic is if we should create some more system views > at initdb time. I would find views telling ownership and > other information readable instead of Oid's very useful. As > for pg_rule and pg_view it would be possible to create a view > that describes the definition of an index instead of some > cryptic numbers. And another one for real tables where > indices and views are omitted would also be useful. Yes, these are good ideas. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
Patch applied. > Bruce, > > I'll send the patch itself directly to you. It's a bigger one > and I don't want to waste bandwidth on the list. Would you > please apply that one and forget the two others I posted > recently? The first rules patch (that is already applied) is > O.K. > > This is the final state of the rule system for 6.4 after the > patch is applied: > > Rewrite rules on relation level work fine now. > > Event qualifications on insert/update/delete rules work > fine now. > > I added the new keyword OLD to reference the CURRENT > tuple. CURRENT will be removed in 6.5. > > Update rules can reference NEW and OLD in the rule > qualification and the actions. > > Insert/update/delete rules on views can be established to > let them behave like real tables. > > For insert/update/delete rules multiple actions are > supported now. The actions can also be surrounded by > parantheses to make psql happy. Multiple actions are > required if update to a view requires updates to multiple > tables. > > Regular users are permitted to create/drop rules on > tables they have RULE permissions for > (DefineQueryRewrite() is now able to get around the > access restrictions on pg_rewrite). This enables view > creation for regular users too. This required an extra > boolean parameter to pg_parse_and_plan() that tells to > set skipAcl on all rangetable entries of the resulting > queries. There is a new function > pg_exec_query_acl_override() that could be used by > backend utilities to use this facility. > > All rule actions (not only views) inherit the permissions > of the event relations owner. Sample: User A creates > tables T1 and T2, creates rules that log > INSERT/UPDATE/DELETE on T1 in T2 (like in the regression > tests for rules I created) and grants ALL but RULE on T1 > to user B. User B can now fully access T1 and the > logging happens in T2. But user B cannot access T2 at > all, only the rule actions can. And due to missing RULE > permissions on T1, user B cannot disable logging. > > Rules on the attribute level are disabled (they don't > work properly and since regular users are now permitted > to create rules I decided to disable them). > > Rules on select must have exactly one action that is a > select (so select rules must be a view definition). > > UPDATE NEW/OLD rules are disabled (still broken, but > triggers can do it). > > There are two new system views (pg_rule and pg_view) that > show the definition of the rules or views so the db admin > can see what the users do. They use two new functions > pg_get_ruledef() and pg_get_viewdef() that are builtins. > > The functions pg_get_ruledef() and pg_get_viewdef() could > be used to implement rule and view support in pg_dump. > > PostgreSQL is now the only database system I know, that > has rewrite rules on the query level. All others (where I > found a rule statement at all) use stored database > procedures or the like (triggers as we call them) for > active rules (as some call them). > > Future of the rule system: > > The now disabled parts of the rule system (attribute > level, multiple actions on select and update new stuff) > require a complete new rewrite handler from scratch. The > old one is too badly wired up. > > After 6.4 I'll start to work on a new rewrite handler, > that fully supports the attribute level rules, multiple > actions on select and update new. This will be available > for 6.5 so we get full rewrite rule capabilities. > -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> > Another topic is if we should create some more system views
> > at initdb time. I would find views telling ownership and
> > other information readable instead of Oid's very useful. As
> > for pg_rule and pg_view it would be possible to create a view
> > that describes the definition of an index instead of some
> > cryptic numbers. And another one for real tables where
> > indices and views are omitted would also be useful.
>
> Yes, these are good ideas.
>
> --
> Bruce Momjian | 830 Blythe Avenue
I'm running into some naming problems while doing so. Having
pg_table, pg_view etc. as views lets a users assume pg_index
would be one too where to get some information. But pg_index
already exists.
Should I name all of them pgv_... ?
Other databases have many views starting with DBA or SYS on
the other hand. For now I'll start naming them pgv_..., we
could rename them before applying the patch.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
> I'm running into some naming problems while doing so. Having
> pg_table, pg_view etc. as views lets a users assume pg_index
> would be one too where to get some information. But pg_index
> already exists.
>
> Should I name all of them pgv_... ?
>
> Other databases have many views starting with DBA or SYS on
> the other hand. For now I'll start naming them pgv_..., we
> could rename them before applying the patch.
I recall that there are some places in the code (maybe only in the
client-side drivers?) which check explicitly for a "pg_%" pattern to
decide if a table or resource is a system table.
How about "pg_index_v", for example?
- Tom
This is true. It would be cleaner, though, if we could check for an attribute in pg_class. I do not recall one for that purpose. Thomas G. Lockhart wrote: > > I'm running into some naming problems while doing so. Having > > pg_table, pg_view etc. as views lets a users assume pg_index > > would be one too where to get some information. But pg_index > > already exists. > > > > Should I name all of them pgv_... ? > > > > Other databases have many views starting with DBA or SYS on > > the other hand. For now I'll start naming them pgv_..., we > > could rename them before applying the patch. > > I recall that there are some places in the code (maybe only in the > client-side drivers?) which check explicitly for a "pg_%" pattern to > decide if a table or resource is a system table. > > How about "pg_index_v", for example? > > - Tom
> > > Another topic is if we should create some more system views > > > at initdb time. I would find views telling ownership and > > > other information readable instead of Oid's very useful. As > > > for pg_rule and pg_view it would be possible to create a view > > > that describes the definition of an index instead of some > > > cryptic numbers. And another one for real tables where > > > indices and views are omitted would also be useful. > > > > Yes, these are good ideas. > > > > -- > > Bruce Momjian | 830 Blythe Avenue > > I'm running into some naming problems while doing so. Having > pg_table, pg_view etc. as views lets a users assume pg_index > would be one too where to get some information. But pg_index > already exists. > > Should I name all of them pgv_... ? > > Other databases have many views starting with DBA or SYS on > the other hand. For now I'll start naming them pgv_..., we > could rename them before applying the patch. Perhaps pg_view_*. pgv_ is bad because the system only protects 'pg_' from modification by the user, and psql does not dump out those table names. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> This is true. It would be cleaner, though, if we could check for an > attribute in pg_class. I do not recall one for that purpose. I don't think there is one. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)