Security matters - hiding a table column - restrict column access

By Kaarel Moppel - Restricting column access - Something from the security realm this time - normally this side of the “database world” doesn't get too much attention and "love" because it's difficult/tedious and requires some deep understanding of how the system works...but ignoring security as we know can catch up with you badly.

Anyways, I'm not telling a real horror story here, but some weeks ago when a friend who’s a “casual DBA” (install, start/stop, backup/restore) asked for help with a situation, where a column was “leaking” data. I had to think a bit longer than usual to find an appropriate place where this issue could be fixed. The solution I suggested in the end included making use of the Rule System of PostgreSQL. I hadn’t seen rules being used in years myself (one should generally try to avoid having some stuff happening behind the scenes) and had to refresh on them a bit myself, but this was the correct use case for applying them and thought I’d share it as well then.

The security leak

So, the application they had was a bought “black box” webapp that did some quite trivial CRUD on some data forms and all was fine - until they noticed that in some form, some column with address data was visible to all users of the system, instead of a specific login role. They couldn’t immediately change the code and were looking for a quick fix to hide the data in that column altogether for all users on the DB side (they could do changes there), until the app gets properly fixed. So what means/tricks are available for such kind of stuff? Not too many and they usually have implications...but let’s have a look at them.

Available options on restricting column access

• Column-level privileges

When data is accessed by different login roles, normally the best way to limit access on single columns would be to use the “column privileges” feature - just do a “REVOKE SELECT” followed by “GRANT SELECT (col1, col2,...)”, leaving out the “secret” columns from the list. With our app it wouldn’t work as only one backend role was used, and all the SELECTs on that table would start to throw errors. Additionally, on other forms showing the column, SQL needs to be adjusted. A no go.

• Rename the column, create a new (nullable) column with the same name

This would be the easiest trick (very easily rollbackable) for a temporary fix...but would only work when no new data is being added - in our case though new customer registrations were still coming in from the web. A no go.

• A view instead of a table

Renaming the old table and creating a simple view with the same name, including all the wanted columns (and adding appropriate GRANTs, for example copying the existing ones with "pg_dump -s -t problem_table | grep ^GRANT") is relatively easy. We could replace our “secret” column’s value for example with an empty string and all would be fine…until we again get some INSERT-s or UPDATE-s. Then we would see an error like the below one, although simple DML in itself is allowed on views by default in Postgres.

So how do you get around this INSERT/UPDATE problem?

• A view instead of a table with Rules!

To overcome the errors on adding/changing data, we need to re-route the data into the “base table” of our view. This can be easily done with rules and it would be a good fit for this use case. Read on for a sample.

The solution

So let’s say this is our leaking table:

And now we want to replace all values in leaky_data column with empty strings. So, we need to write something like this:

Now indeed it does what we want:

NB! When the application is using constructs like “UPDATE … RETURNING *”, it’s also possible to take care of the loophole there in the rule definition, by adding the “RETURNING …, ‘’::text AS leaky_data” part. Hope it helps!

Find out the latest on security matters in PostgreSQL by reading our security blog spot.