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

[imor]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] 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.

[layerzzzio]

I have a similar issue when working with the paddle wrapper.

In my case I installed the extension in the schema "wrappers" as follows:

DROP SCHEMA IF EXISTS wrappers CASCADE;
DROP EXTENSION IF EXISTS wrappers CASCADE;

CREATE SCHEMA IF NOT EXISTS wrappers;
CREATE EXTENSION IF NOT EXISTS wrappers WITH SCHEMA wrappers;

-- the data wrappers is created globally not at the schema level
CREATE FOREIGN DATA WRAPPER wasm_wrapper
  HANDLER wrappers.wasm_fdw_handler
  VALIDATOR wrappers.wasm_fdw_validator;

-- Save your Paddle API key in Vault and retrieve the `key_id`
insert into vault.secrets (name, secret)
values (
  'PADDLE_API_KEY',
  'ADD_API_VALUE'
)
returning key_id;

drop schema if exists paddle_sandbox cascade;
drop schema if exists paddle cascade;

create schema if not exists paddle_sandbox;
create schema if not exists paddle;

drop server if exists paddle_sandbox_server cascade;
drop server if exists paddle_server cascade;

create server paddle_sandbox_server
  foreign data wrapper wasm_wrapper
  options (
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasm',
    fdw_package_name 'supabase:paddle-fdw',
    fdw_package_version '0.1.1',
    fdw_package_checksum 'c5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657',
    api_url 'https://sandbox-api.paddle.com', -- Use https://api.paddle.com for live account
    api_key_id 'VAULT_KEY' -- The Key ID from above.
  );

create server paddle_server
  foreign data wrapper wasm_wrapper
  options (
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasm',
    fdw_package_name 'supabase:paddle-fdw',
    fdw_package_version '0.1.1',
    fdw_package_checksum 'c5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657',
    api_url 'https://api.paddle.com', -- Use https://api.paddle.com for live account
    api_key_id 'VAULT_KEY' -- The Key ID from above.
  );

drop foreign table if exists paddle_sandbox.products;

create foreign table if not exists paddle_sandbox.products (
  id text,
  name text,
  tax_category text,
  status text,
  description text,
  created_at timestamp,
  updated_at timestamp,
  attrs jsonb
)
  server paddle_sandbox_server
  options (
    object 'products',
    rowid_column 'id'
  );

When trying to see the products table from the schema paddle_sandbox.

I see the following yellow warning instead of the table:

Any update on this bug @imor ?

[burmecia]

@layerzzzio , this error happened because the user you're using (postgres) doesn't have permission to write to the stats table wrappers.wrappers_fdw_stats. You may run below SQL to grant the permission:

GRANT SELECT, INSERT, UPDATE ON TABLE wrappers.wrappers_fdw_stats TO postgres;

But unfortunately the dashboard user postgres also doesn't have permission to run this grant SQL. So the solution I can think of now is either:

• using the system default extensions schema, not your customised wrappers schema, to store the stats table
• or using security definer functions described in the docs, so to avoid the complex permission setup

[layerzzzio]

Thank you so much.

This works:

-- install wrappers in extensions
DROP EXTENSION IF EXISTS wrappers CASCADE;

CREATE EXTENSION IF NOT EXISTS wrappers WITH SCHEMA extensions;

-- the data wrappers is created globally not at the schema level
CREATE FOREIGN DATA WRAPPER wasm_wrapper HANDLER extensions.wasm_fdw_handler VALIDATOR extensions.wasm_fdw_validator;

-- Save your Paddle API key in Vault and retrieve the `key_id`
INSERT INTO vault.secrets(name, secret)
  VALUES ('PADDLE_API_KEY', 'ADD_API_VALUE')
RETURNING
  key_id;

INSERT INTO vault.secrets(name, secret)
  VALUES ('PADDLE_SANDBOX_API_KEY', 'ADD_API_VALUE')
RETURNING
  key_id;

DROP SCHEMA IF EXISTS paddle_sandbox CASCADE;

DROP SCHEMA IF EXISTS paddle CASCADE;

CREATE SCHEMA IF NOT EXISTS paddle_sandbox;

CREATE SCHEMA IF NOT EXISTS paddle;

DROP SERVER IF EXISTS paddle_sandbox_server CASCADE;

DROP SERVER IF EXISTS paddle_server CASCADE;

CREATE SERVER paddle_sandbox_server FOREIGN data wrapper wasm_wrapper options(
  fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasm',
  fdw_package_name 'supabase:paddle-fdw',
  fdw_package_version '0.1.1',
  fdw_package_checksum 'c5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657',
  api_url 'https://sandbox-api.paddle.com', -- Use https://api.paddle.com for live account
  api_key_id 'vault_secret_id_paddle_sandbox' -- The Key ID from above.
);

CREATE SERVER paddle_server FOREIGN data wrapper wasm_wrapper options(
  fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasm',
  fdw_package_name 'supabase:paddle-fdw',
  fdw_package_version '0.1.1',
  fdw_package_checksum 'c5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657',
  api_url 'https://api.paddle.com', -- Use https://api.paddle.com for live account
  api_key_id 'vault_secret_id_paddle' -- The Key ID from above.
);

And then fdw any Paddle tables - ex. with products :

DROP FOREIGN TABLE IF EXISTS paddle_sandbox.products;

CREATE FOREIGN TABLE IF NOT EXISTS paddle_sandbox.products(
  id text,
  name text,
  tax_category text,
  status text,
  description text,
  created_at timestamp,
  updated_at timestamp,
  attrs jsonb)
SERVER paddle_sandbox_server options(
  object 'products',
  rowid_column 'id'
);