Version: Doltgres 1.3.1 (docker image dolthub/doltgresql:1.3.1). The release notes for 1.3.2 and 1.3.3 mention no change here.
What happens. A role that holds only table privileges cannot run any common table expression, recursive or not. The engine checks privilege on the CTE's own name as if it were a table:
CREATE TABLE edges (src text, dst text, project_id text);
CREATE ROLE reader LOGIN PASSWORD 'x';
GRANT SELECT ON edges TO reader;
-- as reader:
WITH traversal AS (SELECT src, dst FROM edges) SELECT * FROM traversal;
-- ERROR: permission denied for table traversal (SQLSTATE XX000)
The same statement run as a superuser returns the rows. WITH RECURSIVE fails the same way for reader.
Workaround attempt. Naming the CTE after a table the role is granted (WITH edges AS (...)) passes the privilege check. But then every reference to edges inside the CTE body binds to the CTE itself, so a recursive term cannot read the real table. That fails with 42703 table "..." does not have column "src", for a superuser too.
Expected (PostgreSQL behaviour). PostgreSQL checks privileges on the relations a query reads, and a CTE's name is query-local, not a catalog object (https://www.postgresql.org/docs/current/queries-with.html). The statement above should succeed for reader, because it reads only edges, which reader may select.
Related. #3327, fixed in #3351, was the same pattern for built-in routines: a privilege check on a name that is not the granted object. That fix covered routines only.
Why it matters to us. We run application reads under a role holding only table privileges, and want recursive graph traversal (WITH RECURSIVE) under that role. Recursion itself works well: we measured a depth-bounded walk, deduplication with UNION, and the cte_max_recursion_depth cap stopping an unbounded walk in about 60 ms.
Version: Doltgres 1.3.1 (docker image
dolthub/doltgresql:1.3.1). The release notes for 1.3.2 and 1.3.3 mention no change here.What happens. A role that holds only table privileges cannot run any common table expression, recursive or not. The engine checks privilege on the CTE's own name as if it were a table:
The same statement run as a superuser returns the rows.
WITH RECURSIVEfails the same way forreader.Workaround attempt. Naming the CTE after a table the role is granted (
WITH edges AS (...)) passes the privilege check. But then every reference toedgesinside the CTE body binds to the CTE itself, so a recursive term cannot read the real table. That fails with42703 table "..." does not have column "src", for a superuser too.Expected (PostgreSQL behaviour). PostgreSQL checks privileges on the relations a query reads, and a CTE's name is query-local, not a catalog object (https://www.postgresql.org/docs/current/queries-with.html). The statement above should succeed for
reader, because it reads onlyedges, whichreadermay select.Related. #3327, fixed in #3351, was the same pattern for built-in routines: a privilege check on a name that is not the granted object. That fix covered routines only.
Why it matters to us. We run application reads under a role holding only table privileges, and want recursive graph traversal (
WITH RECURSIVE) under that role. Recursion itself works well: we measured a depth-bounded walk, deduplication withUNION, and thecte_max_recursion_depthcap stopping an unbounded walk in about 60 ms.