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.
ERROR: cannot insert into column "leaky_data" of view "problem_table" DETAIL: View columns that are not columns of their base relation are not updatable.
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.
So let’s say this is our leaking table:
CREATE TABLE problem_table(id int, leaky_data text);
And now we want to replace all values in leaky_data column with empty strings. So, we need to write something like this:
BEGIN; ALTER TABLE problem_table RENAME TO problem_table_real; CREATE VIEW problem_table AS SELECT id, ''::text AS leaky_data FROM problem_table_real; CREATE RULE problem_table_ins AS ON INSERT TO problem_table DO INSTEAD INSERT INTO problem_table_real VALUES ( NEW.id, NEW.leaky_data ); CREATE RULE problem_table_upd AS ON UPDATE TO problem_table DO INSTEAD UPDATE problem_table_real SET id = NEW.id, leaky_data = NEW.leaky_data WHERE id = NEW.id; COMMIT;
Now indeed it does what we want:
INSERT INTO problem_table(id, leaky_data) VALUES (1, 'secret'); SELECT * FROM problem_table; id │ leaky_data ────┼──────────── 1 │ (1 row)
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!