0

I'm wondering what the best strategy is for using insert-only permissions to a postgres db with Peewee. I'd like this in order to be certain that a specific user can't read any data back out of the database.

I granted INSERT permissions to my table, 'test', in postgres. But I've run into the problem that when I try to save new rows with something like:

thing =  Test(value=1)
thing.save()

The sql actually contains a RETURNING clause that needs more permissions (namely, SELECT) than just insert:

INSERT INTO "test" ("value") VALUES (1) RETURNING "test"."id"

Seems like the same sql is generated when I try to use query = test.insert(value=1)' query.execute() as well.

From looking around, it seems like you need either grant SELECT privileges, or use a more exotic feature like "row level security" in Postgres. Is there any way to go about this with peewee out of the box? Or another suggestion of how to add new rows with truly write-only permissions?

apollo901
  • 25
  • 6

1 Answers1

1

You can omit the returning clause by explicitly writing your INSERT query and supplying a blank RETURNING. Peewee uses RETURNING whenever possible so that the auto-generated PK can be recovered in a single operation, but it is possible to disable it:

# Empty call to returning will disable the RETURNING clause:
iq = Test.insert(value=1).returning()
iq.execute()

You can also override this for all INSERT operations by setting the returning_clause attribute on the DB to False:

db = PostgresqlDatabase(...)
db.returning_clause = False

This is not an officially supported approach, though, and may have unintended side-effects or weird behavior - caveat emptor.

coleifer
  • 24,887
  • 6
  • 60
  • 75