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.

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

Now that we can create users and log them in, let's try to let them read some data. The actual tables are locked away in the impl schema to which regular users have no access. Selected parts of the tables can be made available though views. As we saw in the introduction, a table containing private messages between users can be exposed as a view that selects only the messages to or from the current user like so:

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

So what's this SELECT id FROM kroll_user.current_app_user? current_app_user simply a view containing the appuser/dbuser mapping for the current user:

CREATE VIEW kroll_user.current_app_user AS
(SELECT appuser as id FROM mapping.users WHERE dbuser=current_user);

current_user is a special SQL-standardized function that is called without the usual parentheses and returns the login name of the current data base user as a string. Or, that's what Postgresql returns. There seems to be some disagreement on whether it should be the login name or the current security context, with Postgresql, DB2, and Firebird in the first camp and MS-SQL, MySQL and Oracle in the second. As long as we don't call it from a privileged stored procedure, that should not matter, however.

If we would like to filter out columns instead of rows, then this can be done by selecting null instead of the data for the column. Row-based and column-based filtering can also be combined. For the users table, I want to hide the email address of all users, except the logged-in user's own address. UNION to the rescue:

CREATE VIEW kroll_user.users AS (
select id, "name", null as email, moderator from impl.users
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;

Setting up read-only views of tables is pretty much straight-forward: decide what cells users should be allowed to see in each table, come up with SELECT statements that pick those, and make them into views.

Sequences are a bit trickier. Postgresql defines a sequnce to be "special single-row table" that can be used only via the functions nextval, currval, and setval. Unfortunately, Postgresql only allows currval to be run on actual sequences, and not on views of sequences. Since the sequences are data and thus stored in the impl schema to which users don't have access, we have to make a little workaround. Instead of virtualizing the sequence, we can virtualize the accessor functions:

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
when CAST($1 as text)=''threads_id_seq''
then kroll_user.currval_threads_id_seq()
else pg_catalog.currval(CAST($1 as text))
language sql;

This creates a new function called currval in the user schema (which is the first schema in the users' search path) that checks if the requested sequence is the one we want to virtualize (threads_id_seq). If it is, then we use a privileged access function for that specific sequence (which is a database object that we can explicitly grant access to). If it isn't, then we delegate to the built-in currval (which in Postgesql is stored in the pg_catalog schema). The delegation isn't strictly necessary if users don't have access to non-virtualized sequences, but it ensures we got correct error reporting at least.

That's about all there is to reading and filtering data, which is the majority of what most web apps do, and most of the code is plain old standard SQL. Next up will be writing data, which will lead us in to heavy vendor-specific terrain.


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

When you log your users in all the way to the database, user management of course becomes a bit more involved than when a user is just a row in a table. Users need database accounts, which require passwords. Passwords need to be changeable, and old user accounts should be disabled. We'll look at these things one by one. The SQL here is a bit outside what I'd normally write (it uses stored procedures, for instance), but bear with me - after this post it will mostly be downhill.

When users log in, the web app does not check the password, but instead creates a connection to the database using the user's database username and the password that the user gave to the web app. If the database accepts it, then apparently the user is authentic, and the web app can allow it to attempt any authorized action. But what user name should the web app use when logging the user in? We probably don't want to use the application user name, since users may want to change user names, the web app may want to allow users to have user names that contain characters that the database does not permit in its user names etc. One solution is to use a mapping table that maps application user identities to the corresponding database user names, and do a translation when logging in. I put my mapping in a separate schema from the normal tables (which I keep in impl), since it's not strictly part of the application data, but only relates to the authentication mechanism.


CREATE TABLE mapping.users
dbuser text primary key,
appuser int unique

But how can we look things up in the database before we log the user in? We don't have the One Big Application User any more, but we can have specialized user accounts with fixed passwords known by the application as long as they are locked down to specific operations.

CREATE USER kroll_user_login WITH PASSWORD 'asdf';
CREATE SCHEMA kroll_login;
GRANT USAGE ON SCHEMA kroll_login to kroll_user_login;
ALTER USER kroll_user_login SET SEARCH_PATH TO kroll_login;

kroll_login.get_dbuser_by_appuser (app_username text)
RETURNS text AS $$
db_username text;
db_username := (
select dbuser from mapping.users
where appuser=(select id from impl.users where "name"=$1)
return db_username;
$$ language plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION kroll_login.get_dbuser_by_appuser(text)
TO kroll_user_login;

If an attacker would be able to seize control of a connection logged in as kroll_user_login, then the attacker can look up database user names all day long, but damage is limited to that.

The application code for logging the user in thus becomes:

  1. Log in as kroll_user_login.
  2. Run SELECT * FROM get_dbuser_by_appuser(?) with the username provided by the user.
  3. Log in with the returned user name and the password that the user gave.
  4. Create a session for the user, and store the connection as a session attribute.

So what about enrollment? We'd need to come up with a name for the database user, create it, create an application user, and set up the mapping between them. This can be done similarly to logging users in: a stored procedure and a special database user to run it:

CREATE USER kroll_user_signup WITH PASSWORD 'asdf';
CREATE SCHEMA kroll_signup;
TO kroll_user_signup;
ALTER USER kroll_user_signup SET SEARCH_PATH TO kroll_signup;

CREATE SEQUENCE kroll_signup.create_user_seq;
GRANT SELECT, UPDATE on kroll_signup.create_user_seq TO kroll_user_signup;

kroll_signup.create_user (app_username text, password text, email text)
RETURNS void AS $$
db_username text;
db_username := 'kroll_user_' || nextval('create_user_seq');
execute 'CREATE USER ' || db_username ||
' WITH PASSWORD ' || quote_literal(password) ||
' IN ROLE kroll_role_user';
execute 'GRANT USAGE ON SCHEMA kroll_user TO ' || db_username;
execute 'ALTER USER ' || db_username ||
' SET SEARCH_PATH TO kroll_user';
INSERT INTO impl.users ("name", email)
VALUES (app_username, quote_literal(email));
INSERT INTO mapping.users (dbuser, appuser)
VALUES (db_username, (select id from impl.users where "name"=app_username));
$$ language plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION kroll_signup.create_user(text,text,text)
TO kroll_user_signup;

Calling kroll_signup.create_user creates a database user named kroll_user_n that has access only to the kroll_user schema. A row is inserted into the application's users table, and a row mapping between the two identities is added to the mapping.users table.

The kroll_user_signup user is created without the CREATEROLE option, since it is not this user that creates the new user accounts, but the stored procedure, which is running with SECURITY DEFINER. That is: when running the procedure, the permissions are those of the administrator who ran the setup scripts.

As you can see from the code above, all users share a single schema. We are thus not doing much in the database per user in addition to what the application needs when implemented using a single shared login. Database users are on a per-database level though, so prefixing them with the application name is probably a good idea if you want to share a single database instance between many applications.

Changing passwords is a bit different. The usual protocol is that the user specifies the current password, and the new password twice. If the provided current password matches the login password and the two new passwords are the same, then the login password is changed to the new password. The application can compare the two new passwords, but it cannot directly compare the current password with the login password, since login passwords are now only handled by the database. This can be solved by having the application create a new connection to the database using the user's database user name and the provided password. If that succeeds, then the password must have been correct, and we can update the login password to the new password. In Java, this would look something like the following (error handling omitted):

Connection normal_connection=Util.getConnection(request);
PreparedStatement ps=normal_connection.prepareStatement(
"select current_user"
ResultSet rs=ps.executeQuery();
String db_username=rs.getString(1);
Connection special_connection=createConnection(username, old_password);
PreparedStatement ps=special_connection.prepareStatement(
"ALTER ROLE " + db_username + " WITH PASSWORD '" + new_password + "'"

Decommissioning, finally, is a complex topic on the application side. What information about the user should be removed? Should anything be removed? For the forum I wrote, I decided to just keep all the application data. The only thing that is removed is the ability log in, that is: the database user and the mapping. Again, a stored procedure and a dedicated database user are used:

CREATE SCHEMA kroll_remove;
GRANT USAGE ON SCHEMA kroll_remove TO kroll_user_remove;
ALTER USER kroll_user_remove SET SEARCH_PATH TO kroll_remove;

kroll_remove.remove_db_user (app_username integer)
RETURNS void AS $$
db_username text;
select dbuser into db_username from mapping.users where appuser=$1;
delete from mapping.users where appuser=$1;
execute 'REVOKE USAGE ON SCHEMA kroll_user FROM ' || db_username;
execute 'DROP USER ' || db_username;
$$ language plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION kroll_remove.remove_db_user(integer)
TO kroll_user_remove;

This was how user administration can be done when the application users are also database users. Next up will be reading and filtering application data.

layout animation in android XML

I am currently working on an app that lists issues for projects hosted at code.google.com. I really should get to grips with the google-api-java-client since the 'old' gdata client does not support Android but that is for another blog entry.

Instead of actually communicating with the server I have been spending time on 9-patch images, styles and animations. See the thing is I installed this cool app from Android Market that lists gigs at local club Debaser. It had a really fine ListView where the entries flies in from the right. Really nifty and I already knew there is a tween animation package in Android (I mean they have Romain Guy working there).

So I wrote a small translation animation like this


This file goes into res/anim/rowanimation.xml

So then I added the following to my ListView in the layout XML file android:layoutAnimation="@anim/rowanimation" which of course didn't work. Now the sad thing is that the android documentation has a lot of information on how to define animations in XML and lots of code samples for how to use it from java but I couldn't find how to use animations from XML-declarations.

The error I found in log cat said Unknown layout animation name: translate so I dug into the android source for AnimationUtils and sure enough I could find that it is actually looking for something called layoutAnimation.

I'll spare you the suspense of searching the internets for a solution and just tell you that what you need to do. You need to define two animation files. You should do something like this:

<?xml version="1.0" encoding="utf-8"?>
<layoutAnimation xmlns:android="http://schemas.android.com/apk/res/android"

This file goes into res/anim/listanimation.xml and as you can see it references the previous animation file. The ListView should point to the android:layoutAnimation="@anim/listanimation"

Then things look cool when the list shows. The ListView will apply the rowanimation for each row, it will wait 10% of the total animation time before applying it to the next row so the effect is staggered down the rows. It looks really neat.


the proper use of 'final' in java

The proper use of the final keyword in java is always.

I set out to write a detailed explanation of why you always should use final but then I found this summary, enough said...


The difference between web and handsets

Having recently started to revive my interest in OpenGL-hacking (has it been 10 years already!) I've been reading up on what has been going on.
A lot it seems, several major versions of OpenGL, shader language, programmable pipelines. My head is still rotating around the Y-axis.
Now the really fun thing is that Android supports OpenGL ES, which handily combines my two interests at the present. Android and Open GL is fun, and it works, you might even say it zooms. But still, there is an uneasy feeling, it has to do with quality control.
The title of this blog posting is in regards to the difference in roll-out speed for a web application and the software in a consumer device.
Thing is, google has made several slip-ups with both the Nexus One and Android.
My N1 has a digitizer that goes haywire sometimes, I loose WiFi connectivity all the time. Oh and then there is another touch screen issue that I really love, the synaptics multi-touch. On occasion (not hard to reproduce) the Nexus One digitizer confuses X1 and X2.
Android 2.2 introduced the Open GL ES 2.0 (and there was much rejoicing) but they messed up royally and forgot to handle VBOs. At least they admitted to it which I think is really, really nice.
But you see where I'm going with this. My HTC Hero recently got updated to Android 2.1 and will probably never be upgraded to 2.2. Phone manufacturers never want to upgrade a phone unless the bad-will is seriously affecting their brand name. There is no money in upgrading a sold phone, simple as that.
Google on the other hand has built their fame and fortune on web-applications. Those can be upgraded several times a day. Release early, release often actually works there.
But that mind-set will not work so good when releasing consumer devices. At least not in the price range of a Nexus One, EVO or HTC Desire.
Anyway, I still think that Android is a success story, if you asked me a year ago I would have said that there is no way google can take a share on the phone market. So I hope that they come up with a good way to release patches to android that the handset manufacturers will bless and prove me wrong again.

Post Scriptum: If the VBO fix for GLES 2.0 only appears in Android 3.0 (Gingerbread) we are all royally hosed, the specs for that version does not match even the fanciest phones right now so it will never come to the android phone you hold in your hand right now.