The following bug has been logged on the website:
Bug reference: 18496
Logged by: Jingzhou Fu
Email address: fuboat@outlook.com
PostgreSQL version: 17beta1
Operating system: Ubuntu 20.04, docker image postgres:17beta1
Description:
When a user without SELECT permissions tries to perform a select operation
on a circular view, PostgreSQL will report an error saying "infinite
recursion detected in rules for relation v2" instead of "permission denied
for view v2". Is this a bug or an unexpected behavior? The statements to
reproduce are:
-- connect with the superuser 'postgres'
CREATE VIEW v2 as SELECT 1;
CREATE VIEW v1 as SELECT * FROM v2;
CREATE OR REPLACE VIEW v2 AS SELECT * FROM v1;
SELECT * FROM v2;
CREATE USER user_name WITH PASSWORD 'password';
SET SESSION AUTHORIZATION user_name;
SELECT * FROM v2;
-- Output: ERROR: infinite recursion detected in rules for relation "v2"
-- Maybe Expected? ERROR: permission denied for view v2
Regardless, a circular view is an invalid view and should not appear in
normal scenarios, so reporting this error message does not have any negative
impact. However, should this error take precedence over the SELECT
permission error, and could some important error messages potentially be
leaked in this way to users without permissions?
Here is the full result on PostgreSQL 17beta1:
postgres=# create view v2 as select 1;
postgres=# create view v1 as select * from v2;
postgres=# CREATE OR REPLACE VIEW v2 AS select * from v1;
postgres=# select * from v2;
ERROR: infinite recursion detected in rules for relation "v2"
postgres=# CREATE USER user_name WITH PASSWORD 'password';
postgres=# SET SESSION AUTHORIZATION user_name;
postgres=> select * from v2;
ERROR: infinite recursion detected in rules for relation "v2"
postgres=> select * from v1;
ERROR: infinite recursion detected in rules for relation "v1"
postgres=> SET SESSION AUTHORIZATION postgres;
postgres=# create or replace view v2 as select 1;
postgres=# SET SESSION AUTHORIZATION user_name;
postgres=> select * from v1;
ERROR: permission denied for view v1
postgres=> select * from v2;
ERROR: permission denied for view v2
Thank you!
Best wishes,
Jingzhou Fu