Re: Querying a policy

Jeff,

* Jean-Francois Bernier (jean(dot)francois(dot)bernier(at)boreal-is(dot)com) wrote:
> We are evaluating migrating our software RLS to Postgres by using policies.

Neat!

> Having a "FOR UPDATE POLICY" on a table, I was wondering if there is a way to know, before trying an Update and getting an error, if the current row can be updated ?

Unfortunately, not as easily as it seems you would like, currently,
though perhaps we could change that..

> The goal is to show or hide the edit button in my software forms or lists.

Right, makes sense.

> I know that this query can return the CHECK condition of my POLICY:
> SELECT pg_get_expr(polwithcheck, polrelid, true) FROM pg_policy;

Yup.

> But is there a simpler way to get the ids the current user can read and the ones that he can update?

Well, have you considered using the expression from the above query to
add a column to your SELECT query that results in a column that
indicates if the row is updatable or not..? That is, construct your
query by doing:

SELECT pg_get_expr(polwithcheck, polrelid, true) FROM pg_policy ; into a
variable in your application, then:

"SELECT * , " . variable . " from ..."

The same could be done through a view, potentially, or perhaps with a
plpgsql function, but I'm guessing that negates some of the
"cleanliness" that you get with RLS and base tables.

I certainly like the idea in general. I will caution that, to be fair,
just because the WITH CHECK clause says a given row can be modified at
SELECT time doesn't guarantee that the same row will be updatable in
some later transaction, as it depends on just what the policy is.

In any case, very cool to hear about people working to use RLS! Would
love to chat further about your use-case and see what we can do to make
RLS easier to use.

Thanks!

Stephen