INSERT-only API requires SELECT rights

[savv]

Environment

• PostgreSQL version: 13.4
• PostgREST version: 7.0.1
• Operating system: MacOS 11.5

Description of issue

In tutorial 0, granting insert only, instead of insert + select, does not make it possible to insert into api.todos:

grant insert on api.todos to web_anon;
grant usage, select on sequence api.todos_id_seq to todo_user;

I get: {"hint":null,"details":null,"code":"42501","message":"permission denied for table todos"}.

Running the following fixes the problem: grant select on api.todos to web_anon;; I would have expected that this is not necessary. The web_anon user is able to insert things into the DB without select rights.

[gautam1168]

I may be wrong about this, but I don't think this is a bug. Basically, in order to create an entry a function is called that must also perform some read operations in the process of replying to the HTTP request. In particular the writeQuery function in App.hs starts by making a readRequest which I think is ultimately trying to read from the same table where its going to write.

writeQuery :: QualifiedIdentifier -> Bool -> [Text] -> RequestContext -> DbHandler WriteQueryResult
writeQuery identifier@QualifiedIdentifier{..} isInsert pkCols context@RequestContext{..} = do
  readReq <- readRequest identifier context
  ----
  Statements.createWriteStatement
        (QueryBuilder.readRequestToQuery readReq)
  ----

So, at least from the code it looks like having read permission is expected. I may be wrong though... so you can wait for a maintainer to answer this more properly.

[steve-chavez]

@savv v8.0.0 no longer has this issue:

POST requests for insertions no longer include a Location header in the response by default and behave the same way as having a Prefer: return=minimal header in the request. This prevents permissions errors when having a write-only table.

From: https://postgrest.org/en/v8.0/releases/v8.0.0.html#changed

In v7.0.1, you can use Prefer: return=minimal to avoid this error.

[savv]

I thought it might have been something like that. Thank you for letting me know; closing.