


CREATE VIEW kroll_user.messages AS
FROM impl.messages
WHERE "to"=(SELECT id FROM kroll_user.current_app_user)
OR "from"=(SELECT id FROM kroll_user.current_app_user);


CREATE VIEW kroll_user.current_app_user AS



CREATE VIEW kroll_user.users AS (
where id <> kroll_user.get_current_app_user()
select id, "name", email, moderator from impl.users
where id = kroll_user.get_current_app_user()
grant select on kroll_user.users to kroll_role_user;



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

create function kroll_user.currval(unknown) returns bigint as
'select case
then kroll_user.currval_threads_id_seq()
else pg_catalog.currval(CAST($1 as text))
language sql;




  1. This also has the added benefit of moving more complex queries from the application into the database in the form of views. Hopefully leading to that developers that know SQL can focus on writing that and let the rest of us less knowledgable coders write other stuff.
    So not only does it lead to reduced risk of leaking data it also increases division of labour.

  2. Well, yes and no. Yes, views can simplify things a bit, and they have a more flexible interface than stored procedures. I think it's important to keep views used for convenience separate from views used for implementing virtual private databases though. That can be done by having a virtualization layer directly on top of the tables like how I describe in these posts, and then put a convenience layer on top.

    When I first started writing my example app, I conflated the two and had things like an inbox view that contained all messages sent to the currently logged in user. That kind of worked, but I redid it the current way when I realized that I could achieve a schema that looks identical to the table schema, since that both makes the virtualization easier to audit, and makes it possible to retrofit this scheme to existing applications.

  3. For sure, I was thinking of the virtual database; used this way the database implements the authentication in SQL/views. The usual alternative is that the SQL lives in the application with the WHERE clause embedded in application code.
    But as you say, virtualization and convinience views have different goals and a good "layering" or division of labour makes for a cleaner implementation.

  4. Right. Both can be seen as moving parts of the WHERE clauses to the database.

    Since I'm pitching this a s defence in depth, I still keep the WHERE clauses in the app. That way, if there's some aspect that gets forgotten when looking at it from a table perspective, then maybe it will be remembered when looking at it from a query perspective.

    But convenience views would be good. SQL queries are a bit like having to say something in a single breath. I have some giant queries in the app (e.g. the thread list, which includes parts of both the first and the latest posts to each thread) that could have benefitted from being able to breathe a bit in between saying things.

