Re: SQL Property Graph Queries (SQL/PGQ)

On Wed, Aug 28, 2024 at 3:48 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> Patches 0001 - 0006 are same as the previous set.
> 0007 - fixes all the problems you reported till now and also the one I
> found. The commit message describes the fixes in detail.

Here's an updated patchset based on the latest HEAD.

0001
So far I was keeping the 0001 patch the same as the one Peter posted
at [1]. But it has accumulated few conflicts and compilation failures
over the months. Thus that patch by itself is not of much use. In this
patch set 0001 is that patch + fixes for conflicts, compilation
failures and perltidy adjustments accumulated over the months. This
allows that patch to be applied and tested independently. Commit
message describes the fixes.

0002 - adds support for WHERE clause in graph pattern

0003 - adds full support for cyclic path patterns

0004 - fixes all the bugs reported by Ajay and also the one found by
me. Commit message describes all the issues

0005
Tests privileges with GRAPH_TABLE. Table level privileges are being
honoured by previous patches by creating approprate RTEPermissionInfos
for the underlying tables when rewriting GRAPH_TABLE entry. But those
patches do not honour column privileges. This patch adds the columns
used in the GRAPH_TABLE to RTEPermissionInfos::selectedCols to honour
column level privileges. Below are the access rules for property graph
and the tables underlying GRAPH_TABLE entry.

1. A user needs SELECT privilege to access a property graph in the query.
2. The property graph itself acts similar to a security invoker view
in the sense that the current_user should have privileges to access
the tables and columns, underlying the graph pattern. For example, a
property graph g1 contains graph element tables e1 and e2. Assume two
users u1 and u2 with privileges to access e1 and e2 respectively.
Let's also assume that they have privileges to access the property
graph g1. u1, as the current user, would be able to run query
involving g1 successfully as long as it does *not* require access to
e2. Similarly u2 would be able to run a query involving g1
successfully as long as it does *not* require access to e1.
3. If a property graph is referenced in a security definer view,
access to the property graph is governed by the privileges of the
owner of the view but the access to the base relations underlying the
graph pattern are governed by the privileges of the user executing the
query. This is similar to how access to the base relations underlying
a security invoker view referenced from a security definer view is
handled. access
4. We have not implemented security definer property graphs since
SQL/PGQ standard does not mention those.

Per SQL/PGQ standard, a property graph can contain any relation as
long as it has SELECT privilege on that relation ("Access rules"
subsection of section 11.19 <property graph definition>". Current
implementation of property graph DDL does not allow a table owned by a
user other than the owner property graph to be added to the property
graph. I haven't changed that in this patch. But in case we implement
what the standard suggests, it will become much easier to add queries
to test privileges and access vs property graph containing views. So I
have left those test aside for now.

0006 - is a temporary fix to provide collation to the operator joining
edges to their adjacent tables. It is required for some tests added in
0007.

0007
Adds queries to test RLS against GRAPH_TABLE. rowsecurity.sql has an
extensive set of tests for RLS. The commits add new queries equivalent
to existing ones but using GRAPH_TABLE construct. The tests covering
simple tables, inheritance and partitioned tables establish that the
RLS is applied even when the tables are accessed through GRAPH_TABLE.
That is good enough to assume that the RLS policies will be applied
and handled correctly when interacting with other features like
prepared statement invalidation, security barrier views, CTE, INSERT
... SELECT, COPY, joins, non-target relations etc. This assumption is
based on the current implementation which rewrites GRAPH_TABLE RTE
well before the RLS policies are added. In case the implementation
changes in future, these extensive set of new queries will provide the
extensive coverage. We might decide to reduce the number of new
queries later. But for now, I have kept all those queries in this
patch. Hence a separate patch.

Work in 0005 and 0006 together is intended to make sure a bad actor
doesn't exploit property graphs to access data which is not available
to them otherwise.

Next I will work on finishing 0006 and implement the complete code to
construct quals joining edges to their adjacent tables.

[1] https://www.postgresql.org/message-id/a855795d-e697-4fa5-8698-d20122126567%40eisentraut.org

--
Best Wishes,
Ashutosh Bapat