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 pk_threads PRIMARY KEY (id),
  CONSTRAINT fk_threads_forum FOREIGN KEY ("forum")
      REFERENCES impl.fora (id) ON DELETE CASCADE
);

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:

CREATE OR REPLACE RULE posts_insert AS
ON INSERT TO kroll_user.threads
DO INSTEAD
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
DO ALSO
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
DO INSTEAD NOTHING;

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
WHERE (
  select locked from impl.threads
  where threads.id=(select thread from impl.posts where id = OLD.id)
)<>1
DO ALSO
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))
  end;'
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.