On an INSERT with a SELECT, use RETURNING (Instead of a separate SELECT call)

[zlotnika]

Description

I want insert({...}).select('id') to require INSERT, but not require SELECT policy. I want to have a user that can INSERT a record and some corresponding stuff with it. I don't want them to be able to, in general, SELECT a record, but I need to get back at least the id of the thing that was INSERTed so that I can add join things.

Suggested solution

If RETURNING is used, it should allow insert({...}).select('id') to work without having SELECT permissions on the INSERTed row.

Alternative

The workaround is to create an id in the client, but I don't like that as much as relying on the DB for such things.

Additional context

No response

Validations

• Follow our Code of Conduct
• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that request the same feature to avoid creating a duplicate.

[steve-chavez]

I want insert({...}).select('id') to require INSERT, but not require SELECT policy.

Can you show an example on SQL where RETURNING works without a SELECT privilege?

IIRC, the SELECT privilege (as in GRANT SELECT) is required (whether on the whole table or just on as single column) on RETURNING as well. So insert({...}).select('id') would also require it.

[mandarini]

@zlotnika please provide more information as requested above, so that we can see if we need to move forward with a fix!