Aggregate/Window functions restriction in Postgres

问题:

I've been successfully able to use a dense_rank() over (order by...) which AFAIK is a window function - in postgres' row level security policy conditions.

However, the documentation states

Any SQL conditional expression (returning boolean). The conditional expression cannot contain any aggregate or window functions

(emphasis is mine).

Can someone explain this restriction and give an example where it applies?

Thanks.

回答1:

Basically, it tells you that each row is independent in regard of row-level security.

Consider the table below:

+---------------------+----------------+
| field1              | field2         |
+---------------------+----------------+
| value1              | 1              |
| value1              | 2              |
| value1              | 3              |
| value2              | 4              |
+---------------------+----------------+

There are several (permissive) policies:

1. field1 = 'value1'
2. field1 = 'value2'
3. SUM(field2)> 10 (forbidden but let us imagine for now that you could define it)

You were granted policies #2 and 3 so you can see and update the last record only.
... Until you execute UPDATE table SET value2 = 11.

This is really bad in terms of:

• Security. You can "grant yourself" access to records, as a user (not as an admin).
• Maintenance. Records would keep appearing/disappearing randomly in such database.
• Performance. Such policy would have a very big cost to evaluate.

Interstingly, you can define policies as MyField IN (SELECT MyOtherField FROM MyOtherTable), in which case it all relies on what you defined on MyOtherTable (it is intended to be used with FK/PK).