Professional Web Applications Themes

Allowing update of column only from trigger - PostgreSQL / PGSQL

I have a table like this: create table objects ( id serial primary key, name varchar not null, parent integer references objects(id) default 1 not null, uri varchar not null ) without oids; The uri column is a denormalization for performance, storing a "path" to the object in the hierarchy, consisting of a sequence of names. # select id, name, parent, uri from objects; id | name | parent | uri ----+------+--------+---------- 1 | | 1 | / 2 | foo | 1 | /foo 3 | bar | 2 | /foo/bar (3 rows) The uri is calculated by a ...

  1. #1

    Default Allowing update of column only from trigger

    I have a table like this:

    create table objects (
    id serial primary key,
    name varchar not null,
    parent integer references objects(id) default 1 not null,
    uri varchar not null
    ) without oids;

    The uri column is a denormalization for performance, storing a "path" to
    the object in the hierarchy, consisting of a sequence of names.

    # select id, name, parent, uri from objects;

    id | name | parent | uri
    ----+------+--------+----------
    1 | | 1 | /
    2 | foo | 1 | /foo
    3 | bar | 2 | /foo/bar
    (3 rows)

    The uri is calculated by a trigger before update on objects.

    The original version of the trigger function would re-calculate the uri
    for an object and its immediate children if the name, parent, or uri
    changed. It would apply the uri change to the children, which would
    cascade down the hierarchy. This generally worked, but (1) I was having
    data visibility voodoo, and (2) it was calculating every child's uri
    twice, which would be inefficient for large hierarchies.

    So I changed the trigger function so that it would only fire if name or
    parent had changed. I created a recursive helper function that changes
    the uri for all descendants of an object, if the object's name or parent
    has changed. There is no cascade of changes (the trigger fires for all
    the descendants, of course, but doesn't do anything). Works great, is
    more efficient, and I can manage the max_stack_size to fit the size of
    the hierarchy.

    The PROBLEM with this is that anyone can now

    # update objects set uri='/ha/ha/your/ed' where id=2;

    I want the trigger function and its helper alone to be able to update
    the uri.

    What is the best way to do this? Should I put the uri column in a
    separate table, and play with permissions? (Yuck.) Do I need to bite the
    bullet, go back to cascading triggers, and work out the data visibility
    voodoo and the efficiency issue? Or is there a better way that I haven't
    thought of?

    Thanks,

    Shawn Harrison
    --
    ________________
    [email]harrisontbc.net[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Shawn Harrison Guest

  2. #2

    Default Re: Allowing update of column only from trigger


    First you should use a ltree type for the uri field :
    - you write it foo.bar instead of /foo/bar
    - there are operators on ltree types to express "is parent of", "is
    children of"
    - these operators are indexed

    Check the readme :
    [url]http://www.sai.msu.su/~megera/postgres/gist/ltree/[/url]

    If you have this type of path, I guess you'll often make tree traversal
    operations, and that you'll find the ltree operators extremely useful.
    You can update it with a trigger just like before.

    Now about your update problem, when you rename foo.bar into foo.crum.bar
    you could, in a single update, replace all foo.bar by foo.crum.bar in all
    your table with the ltree operators and special functions.

    And for your checks, you can add a CHECK on the url field to be sure it's
    equal to the url of the parent + the name of the current row. It'll make
    one more SELECT request, though.
    > I have a table like this:
    >
    > create table objects (
    > id serial primary key,
    > name varchar not null,
    > parent integer references objects(id) default 1 not null,
    > uri varchar not null
    > ) without oids;
    >
    > The uri column is a denormalization for performance, storing a "path" to
    > the object in the hierarchy, consisting of a sequence of names.
    >
    > # select id, name, parent, uri from objects;
    >
    > id | name | parent | uri
    > ----+------+--------+----------
    > 1 | | 1 | /
    > 2 | foo | 1 | /foo
    > 3 | bar | 2 | /foo/bar
    > (3 rows)
    >
    > The uri is calculated by a trigger before update on objects.
    >
    > The original version of the trigger function would re-calculate the uri
    > for an object and its immediate children if the name, parent, or uri
    > changed. It would apply the uri change to the children, which would
    > cascade down the hierarchy. This generally worked, but (1) I was having
    > data visibility voodoo, and (2) it was calculating every child's uri
    > twice, which would be inefficient for large hierarchies.
    >
    > So I changed the trigger function so that it would only fire if name or
    > parent had changed. I created a recursive helper function that changes
    > the uri for all descendants of an object, if the object's name or parent
    > has changed. There is no cascade of changes (the trigger fires for all
    > the descendants, of course, but doesn't do anything). Works great, is
    > more efficient, and I can manage the max_stack_size to fit the size of
    > the hierarchy.
    >
    > The PROBLEM with this is that anyone can now
    >
    > # update objects set uri='/ha/ha/your/ed' where id=2;
    >
    > I want the trigger function and its helper alone to be able to update
    > the uri.
    >
    > What is the best way to do this? Should I put the uri column in a
    > separate table, and play with permissions? (Yuck.) Do I need to bite the
    > bullet, go back to cascading triggers, and work out the data visibility
    > voodoo and the efficiency issue? Or is there a better way that I haven't
    > thought of?
    >
    > Thanks,
    >
    > Shawn Harrison


    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    PFC Guest

  3. #3

    Default Re: Allowing update of column only from trigger

    isn't it possible to restrict UPDATE by access rights based on the DB's
    user?

    Create table with owner set to the administrator of the database (NOT
    PostgreSQL SERVER!!!) and grant only the needed rights (or none of them)
    to the user from which the usual processing of the database will be
    performed. Then, create a trigger function with SECURITY DEFINER set and
    own it by the owner of the database (or other user, who's granted to
    UPDATE the table). So, if no one else is granted UPDATE on the table,
    the only UPDATE-modifiers of the table will be the owner and the trigger
    function's owner (if differs from owner).

    Best regards,
    Andrey V. Semyonov

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faq[/url]

    Andrey V. Semyonov Guest

  4. #4

    Default Re: Allowing update of column only from trigger

    PFC wrote [01/28/05 7:08 PM]:
    >
    > First you should use a ltree type for the uri field :
    Yes, it would be very good if I could use ltree rather than rolling my
    own, but ltree doesn't meet my use requirements.
    > - you write it foo.bar instead of /foo/bar
    That is the problem: The point is to make look-up based on uri or
    filesystem path very efficient.
    > - there are operators on ltree types to express "is parent of", "is
    > children of"
    > - these operators are indexed
    I have written similar pure-SQL functions for my parent/uri system,
    though without the syntax shortcuts of the ltree operators.
    > [...]
    > Now about your update problem, when you rename foo.bar into
    > foo.crum.bar you could, in a single update, replace all foo.bar by
    > foo.crum.bar in all your table with the ltree operators and special
    > functions.
    That's an approach I hadn't considered. Hmmm, I'd have to read the ltree
    C sources to see how it does this....
    > And for your checks, you can add a CHECK on the url field to be sure
    > it's equal to the url of the parent + the name of the current row.
    > It'll make one more SELECT request, though.
    Yes, I'd like to avoid extra selects as much as possible.

    Thank you very much for your interaction -- very helpful.
    --
    ________________
    [email]harrisontbc.net[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faq[/url]

    Shawn Harrison Guest

  5. #5

    Default Re: Allowing update of column only from trigger

    Andrey V. Semyonov wrote [01/29/05 12:45 PM]:
    > isn't it possible to restrict UPDATE by access rights based on the DB's
    > user?
    >
    > Create table with owner set to the administrator of the database (NOT
    > PostgreSQL SERVER!!!) and grant only the needed rights (or none of them)
    > to the user from which the usual processing of the database will be
    > performed. Then, create a trigger function with SECURITY DEFINER set and
    > own it by the owner of the database (or other user, who's granted to
    > UPDATE the table). So, if no one else is granted UPDATE on the table,
    > the only UPDATE-modifiers of the table will be the owner and the trigger
    > function's owner (if differs from owner).
    Thank you for explaining this. I haven't done much with rights within
    the database, but it seems you have explained how to do exactly what I
    had been considering as the "rights"-oriented solution to my problem.
    > Best regards,
    > Andrey V. Semyonov
    Take care,
    Shawn Harrison
    --
    ________________
    [email]harrisontbc.net[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Shawn Harrison Guest

  6. #6

    Default Re: Allowing update of column only from trigger

    Shawn Harrison wrote [01/28/05 3:53 PM]:
    > I have a table like this:
    >
    > create table objects (
    > id serial primary key,
    > name varchar not null,
    > parent integer references objects(id) default 1 not null,
    > uri varchar not null
    > ) without oids;
    >
    > The uri column is a denormalization for performance, storing a "path" to
    > the object in the hierarchy, consisting of a sequence of names.
    > [...]
    > I want the trigger function and its helper alone to be able to update
    > the uri.
    >
    > What is the best way to do this? [...]
    Thank you, PFC and Andrey V. Semyonov, for your help in characterizing
    the problem and its solution. Here's what I did.

    "version 1" is the speedy, recursive version of the trigger function
    that I wrote last week, which has the problem of letting uri be updated
    directly.

    My "version 2" solution was to (1) add auto-update when the uri had been
    changed in addition to the parent and name, (2) make the trigger fire
    _after_ update (to overcome data visibility problems), and (3) to add a
    SQL update statement to the trigger rather than relying on changes to
    new.uri (since changing new.uri won't work in a trigger after update).
    This worked. But was very inefficient, because it was calculating the
    same thing multiple times for every child object. To wit:

    - loading a 300-item hierarchy (inserting all objects and updating parents):
    version 1: 1.8 seconds
    version 2: 7.9 seconds
    - Updating the name of the root object in the hierarchy (which requires
    updating the uri of 300 objects)
    version 1: 0.4 seconds
    version 2: 4.5 seconds

    One can see how unscalable version 2 would be.

    My "version 3" solution was to keep my "fast" version 1 trigger code,
    but to change the data model a bit: The table is now named
    "objects_data". I then created a view named "objects" which is just

    create view objects as (select * from objects_data);

    To protect uri, I created a rules on insert and update to objects that
    doesn't pass to objects_data the changed uri value, like this:

    create or replace rule objects__update as on update to objects
    do instead (
    update objects_data set
    name = new.name,
    typename = new.typename,
    parent = new.parent,
    where id = new.id
    );

    The trigger function to update the uri then operates directly on the
    objects_data table.

    This solution provides as much security as I need -- to protect against
    stupidity, mainly. If I wanted more security, I could change the
    security on the objects_data table, as Andrey suggested.

    So now all of my test cases pass, and its speedy to boot. :-)

    Thanks, guys,
    Shawn Harrison
    --
    Peace and joy,

    Shawn Harrison
    Tyndale House Publishers
    _______________
    [email]sahtyndale.com[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Shawn Harrison Guest

  7. #7

    Default Re: Allowing update of column only from trigger

    Shawn Harrison wrote [01/31/05 12:56 PM]:
    > Shawn Harrison wrote [01/28/05 3:53 PM]:
    > create or replace rule objects__update as on update to objects
    > do instead (
    > update objects_data set
    > name = new.name,
    > typename = new.typename,
    > parent = new.parent,
    ^^^^
    This is a simplified version of the rule; the real one didn't have this
    syntax error.
    > where id = new.id
    > );
    > ...
    --
    ________________
    [email]harrisontbc.net[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    Shawn Harrison Guest

Similar Threads

  1. Replies: 1
    Last Post: August 16th, 10:16 PM
  2. Before update or delete trigger to insert ?
    By Cindy Gold in forum IBM DB2
    Replies: 2
    Last Post: August 12th, 01:38 PM
  3. Update trigger problem
    By Stephen F Zelonis in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 01:52 PM
  4. Have trigger supply value for NOT NULL column on insert
    By Ian Boyd in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 02:59 PM
  5. Trigger to update another data source
    By Mandar Naik[MSFT] in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 09:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139