Is returning minimal secure?

Hey there, have been wondered if using {returning: "minimal"} on update() query on client side is secure enough, I have a table which shouldn't be read by users (selects are handled by a database view) but still when updated returns the original row, and I wanted to make sure that no user can mess with my client side code and see privileged data.

garyaustin•9/10/22, 7:41 PM

I don't think you can update if you can't read the table.

IftachOP•9/10/22, 7:43 PM

It does work, you cannot use select() for example but can still update()

garyaustin•9/10/22, 7:45 PM

Typically an UPDATE command also needs to read data from columns in the relation being updated (e.g., in a WHERE clause or a RETURNING clause, or in an expression on the right hand side of the SET clause). In this case, SELECT rights are also required on the relation being updated, and the appropriate SELECT or ALL policies will be applied in addition to the UPDATE policies. Thus the user must have access to the row(s) being updated through a SELECT or ALL policy in addition to being granted permission to update the row(s) via an UPDATE or ALL policy.

Typically an UPDATE command also needs to read data from columns in the relation being updated (e.g., in a WHERE clause or a RETURNING clause, or in an expression on the right hand side of the SET clause). In this case, SELECT rights are also required on the relation being updated, and the appropriate SELECT or ALL policies will be applied in addition to the UPDATE policies. Thus the user must have access to the row(s) being updated through a SELECT or ALL policy in addition to being granted permission to update the row(s) via an UPDATE or ALL policy.

hmmm

IftachOP•9/10/22, 7:47 PM

I guess you're right, doing some experiments but seems like it's really work that way

IftachOP•9/10/22, 7:47 PM

but if it does what are my options for hiding sensitive columns?

garyaustin•9/10/22, 7:50 PM

Postgres has column security with grants, but a bit painful to use as you have to take the grant away for the table, then add back each column you want to read. Using a view probably makes that a little more interesting as privledge is on the view owner.

IftachOP•9/10/22, 7:52 PM

I guess that Supabase does not natively supports column security grants, am I right?

garyaustin•9/10/22, 7:53 PM

What does natively mean?

IftachOP•9/10/22, 7:54 PM

The same way that it supports RLS, I can still use it but I'm on my own

garyaustin•9/10/22, 7:54 PM

You have to do it in SQL yes, but that is typical once you get into views and advanced functions.

IftachOP•9/10/22, 7:55 PM

Before each of the following update queries I also run several triggers and while doing so I'm returning new at the end of the function, maybe I can leverage this to return another row with no sensitive information revealed?

garyaustin•9/10/22, 7:57 PM

I honestly don't know how the return value is affected in trigger functions. You are mainly manipulating the table row to be updated/inserted.

IftachOP•9/10/22, 7:59 PM

I will give it a shot, it may be the solution I need

IftachOP•9/10/22, 8:00 PM

Thank you very much!

Typically an UPDATE command also needs to read data from columns in the relation being updated (e.g., in a WHERE clause or a RETURNING clause, or in an expression on the right hand side of the SET clause). In this case, SELECT rights are also required on the relation being updated, and the appropriate SELECT or ALL policies will be applied in addition to the UPDATE policies. Thus the user must have access to the row(s) being updated through a SELECT or ALL policy in addition to being granted permission to update the row(s) via an UPDATE or ALL policy.

Is returning minimal secure?

Similar Threads

Similar Threads

Similar Threads