permission denied for table wrappers_fdw_stats error when wrappers is created in the extensions schema

[imor]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

permission denied for table wrappers_fdw_stats error when wrappers is created in the extensions schema. We recently also updated the docs to suggest creating the wrappers extension in the extensions schema to fix an RLS warning (ticket id 2081530023).

To Reproduce

Steps to reproduce:

1. Run create extension if not exists wrappers with schema extensions; from dashboard.
2. Run \dp wrappers_fdw_stats from psql and notice the permissions:

postgres=# \dp wrappers_fdw_stats
                                                Access privileges
   Schema   |        Name        | Type  |           Access privileges            | Column privileges | Policies 
------------+--------------------+-------+----------------------------------------+-------------------+----------
 extensions | wrappers_fdw_stats | table | postgres=a*r*w*d*D*x*t*/supabase_admin+|                   | 
            |                    |       | supabase_admin=arwdDxt/supabase_admin  |                   | 
(1 row)

3. Create a foreign data table and run a select query from anon role and observe the error permission denied for table wrappers_fdw_stats.

Expected behavior

There should be no error when running a select query on a foreign table.

Screenshots

N/A

System information

• wrappers version 0.1.19

Additional context

The permissions are missing only when the wrappers extension is created in the extensions schema. To confirm this:

1. Run drop extension wrappers if exists cascade; to drop the extension if it is present.
2. Run create extension if not exists wrappers;
3. Run \dp wrappers_fdw_stats from psql and notice the permissions:

postgres=# \dp wrappers_fdw_stats
                                             Access privileges
 Schema |        Name        | Type  |           Access privileges           | Column privileges | Policies 
--------+--------------------+-------+---------------------------------------+-------------------+----------
 public | wrappers_fdw_stats | table | postgres=arwdDxt/supabase_admin      +|                   | 
        |                    |       | supabase_admin=arwdDxt/supabase_admin+|                   | 
        |                    |       | anon=arwdDxt/supabase_admin          +|                   | 
        |                    |       | authenticated=arwdDxt/supabase_admin +|                   | 
        |                    |       | service_role=arwdDxt/supabase_admin   |                   | 
(1 row)

Notice additional permissions when the extension is created in the public schema. See ticket 2117241996 for details.

[kocobee]

Hitting this as well. Is there a workaround? Is putting wrappers in the public schema safe?

[imor]

@ryan-managai since this is not yet fixed, reach out to support to get it fixed in your project.

[soedirgo]

This has to do with the default privileges in each schema (\ddp public|extensions on psql to check).

If wrappers_fdw_stats is intended to be exposed via Data APIs, the preferred way is via a view, e.g.:

create view public.wrappers_fdw_stats as select * from extensions.wrappers_fdw_stats;
revoke all on public.wrappers_fdw_stats from anon, authenticated;

[prvind-panday]

The permissions issue was resolved by explicitly granting privileges to the necessary roles. The following SQL command was used to correct the permissions:

GRANT ALL PRIVILEGES ON TABLE wrappers_fdw_stats TO anon, authenticated, service_role;

This command ensures that the anon, authenticated, and service_role roles have full access to the wrappers_fdw_stats table, thus resolving the permission denied error.

Recommendation:
For those implementing the wrappers extension in the extensions schema, it is important to ensure that the necessary table permissions are set correctly to avoid similar issues. The above grant statement can be adapted based on the specific roles and privileges used in your environment.

I've shared the resolution to the permission issue that worked in my case, but I am seeking feedback on the safety and best practices regarding this approach. If there are any concerns or alternative recommendations about granting such privileges, especially related to security implications, please share your insights. I aim to ensure that the solution not only resolves the functional problem but also adheres to best security practices.