1

I have multiple tables that I would like users to be able to update through the rest api, and many (if not all) have columns with sensible defaults.

The web app itself can be designed to hide these columns, but I want to allow direct access to the api as well so that others can make use of the data however they see fit.

Unfortunately, this means they can set the defaulted columns explicitly (set timestamp columns to 1972, or set id columns to arbitrary values).

What mechanisms are available to restrict this on the backend (Postgres 9.4)?

John O
  • 4,863
  • 8
  • 45
  • 78

3 Answers3

2

You should do this at API level.

If anybody issues a malformed request (e.g. they want to overwrite an ID or a timestamp), answer with a proper status code (perhaps 400), amended with a meaningful message, for instance "Hey you tried to update , which is read only."

If you would really insist to handle it at db level, here they suggest that:

The easiest way is to create BEFORE UPDATE trigger that will compare OLD and NEW row and RAISE EXCEPTION if the change to the row is forbidden.

Community
  • 1
  • 1
moonwave99
  • 21,957
  • 3
  • 43
  • 64
  • I don't insist at handling it at the db level, but I can reuse the same code for all tables in the api code. I've parameterized the queries themselves, and I check any table or column name passed by the user to make sure its valid. If I do it in that code, then I need a new page of code for every table just to manage which columns are allowed to be updated or not. The db seemed like it might be more elegant. – John O Jul 02 '15 at 15:11
2

I've had some luck experimenting with Postgres' column-level grants. It's important in a development environment to make sure that your database users isn't a superuser (if it is, create a second superuser, then revoke it from the dev account with alter role).

Then, commands similar to these can be run on a table:

revoke all on schema.table from dev_user;
grant select, delete, references on schema.table to dev_user;
grant update (col1, col2) on schema.table to dev_user;
grant insert (col1, col2) on schema.table to dev_user;

Some caveats:

  1. Remember to grant "references" as well if another table will fkey to it.
  2. Remember to give col1 and col2 (and any other) sane defaults, because the API will be unable to change those in any way.
  3. DO NOT FORGET TO CREATE A SECOND SUPERUSER ACCOUNT BEFORE REVOKING SUPERUSER STATUS FROM THE DEV ACCOUNT. It is possible to recover this, but a big pain in the ass.

Also, if you're keeping these grant/revocations in the same file as the create table statement, the following form might be of use:

do $$begin execute 'grant select, delete, references on schema.table to ' || current_user; end$$;

This way the statements will translate correctly to production, which may not use the same username as in development.

John O
  • 4,863
  • 8
  • 45
  • 78
1

PostgreSQL since version 9.3 supports updatable views, so instead of exposing actual table you can expose a view with a limited subset of columns:

CREATE TABLE foo (id SERIAL, name VARCHAR, protected NUMERIC DEFAULT 0);
CREATE VIEW foo_v AS SELECT name FROM foo;

Now you can do things like:

INSERT INTO foo_v VALUES ('foobar');
UPDATE foo_v SET name = 'foo' WHERE name = 'foobar';

If you need more you can use INSTEAD INSERT/UPDATE RULE or INSTEAD OF INSERT TRIGGER.

zero323
  • 322,348
  • 103
  • 959
  • 935