Defence-in-depth for data-base backed websites: Writing

Writing though views seems to be quite different in different database servers. In Postgresql, it's done using something they call rules. Rules rewrite the queries early on, before they are sent off to the planner. Views are implemented as rules, so we actually already used them in the previous post, even though it didn't show.

If we have a table for forum threads like this:

CREATE TABLE impl.threads
id serial not null,
forum integer not null,
locked integer not null default 0,
sticky integer not null default 0,
CONSTRAINT fk_threads_forum FOREIGN KEY ("forum")

and we want users to be able to start new threads, but not to be able to specify the locked or sticky values, we can create a rule on the view like this:

ON INSERT TO kroll_user.threads
INSERT INTO impl.threads (forum, locked, sticky) VALUES (NEW.forum, 0, 0);

For the private messages view, we can set the sender to the current user, regardless of what the user tried:

create or replace rule messages_insert as
on insert to kroll_user.messages do instead
insert into impl.messages ("to", "from", posted, subject, text)
values (
NEW."to", kroll_user.get_current_app_user(), NEW.posted,
NEW.subject, NEW.text

Conditionals get a bit trickier. Posts should only be permitted to threads that are not locked. An insert to a locked thread should be ignored. Due to a limitation in Postgresql, it's impossible for it to figure out that if it shouldn't do anything then it should do nothing, so we have to explicitly tell it:

CREATE VIEW kroll_user.posts as select * from impl.posts;

grant select, insert, update on kroll_user.posts to kroll_role_user;

create or replace rule posts_insert as
on insert to kroll_user.posts
WHERE (select locked from impl.threads where threads.id=NEW.thread)<>1
insert into impl.posts (thread, author, subject, body, created)
values (
NEW.thread, kroll_user.get_current_app_user(), NEW.subject,
NEW.body, now()

create or replace rule posts_insert_default as
on insert to kroll_user.posts

Postgresql runs all rules that have matching WHERE clauses (and if there is no WHERE, then the rule always matches), so for a post to a locked thread, it (like the goggles) does NOTHING, while for a post to an unlocked thread, it both inserts the row and does NOTHING.

Updates are handled almost identically:

create or replace rule posts_update as
ON UPDATE TO kroll_user.posts
select locked from impl.threads
where threads.id=(select thread from impl.posts where id = OLD.id)
UPDATE impl.posts
SET subject=NEW.subject, body=NEW.body, edited=now()
WHERE id=NEW.id and author=kroll_user.get_current_app_user();

Writing to sequences can be done in a manner similar to how we did reading: by virtualizing the accessor functions:

create function kroll_user.nextval_threads_id_seq() returns bigint as
'select pg_catalog.nextval(''impl.threads_id_seq'');'
language sql security definer;

/* ... */

create function kroll_user.nextval(unknown) returns bigint as
'select case
when CAST($1 as text)=''messages_id_seq''
then kroll_user.nextval_messages_id_seq()
when CAST($1 as text)=''posts_id_seq''
then kroll_user.nextval_posts_id_seq()
when CAST($1 as text)=''threads_id_seq''
then kroll_user.nextval_threads_id_seq()
else pg_catalog.nextval(CAST($1 as text))
language sql;

As stated in the beginning, this is how things work in Postgresql, and other database servers will require things to be done in different ways. For Oracle, MS-SQL, and DB2, the views are updatable automatically, without any extra rules. In some cases (like with the locked and sticky columns), you may have to use triggers to make certain parts non-writeable.

Next post will move back into vendor-neutral theory again. I'll discuss role-based access control and how the database can get involved in enforcing application roles.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.