Unexpected Access to Restricted Materialized View

[SamuraiT]

Describe the bug

After setting up a materialized view sensitive_data_view with restricted access, and granting SELECT permission exclusively to the service_role, users without the service_role can unexpectedly access the materialized view. This behavior is unexpected as the intention was to limit access to this sensitive data.

To Reproduce

Steps to reproduce the behavior:

1. Create a materialized view with the command:

   CREATE MATERIALIZED VIEW sensitive_data_view AS SELECT * FROM sensitive_data_table;
   

2. Reset permissions to limit public access with:

   REVOKE ALL ON sensitive_data_view FROM PUBLIC;
   

3. Grant SELECT permission only to service_role with:

   GRANT SELECT ON sensitive_data_view TO service_role;
   

4. Log in as a user without service_role and attempt to access sensitive_data_view.

Expected behavior

The expectation was that only users with the service_role would be able to access the sensitive_data_view. Other users, especially those without explicit permissions, should not have access to the materialized view, ensuring the security of sensitive data.

System information*

• PostgreSQL version: PostgreSQL 15.1 (Ubuntu 15.1-1.pgdg20.04+1)
• Your database's hosting environment: local