BUG #18496: Strange Handling for Circular Views and Permissions in PostgreSQL

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: fuboat(at)outlook(dot)com
Subject: BUG #18496: Strange Handling for Circular Views and Permissions in PostgreSQL
Date: 2024-06-06 07:12:08
Message-ID: 18496-62ecca730bfdfceb@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 18496
Logged by: Jingzhou Fu
Email address: fuboat(at)outlook(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Baran Kurtboğan 2024-06-06 07:20:47 Re: BUG #18494: hstore data type not recognized by Npgsql in PostgreSQL 16.3
Previous Message Bertrand Drouvot 2024-06-06 06:05:11 Re: error "can only drop stats once" brings down database