Supabase•8mo ago

How to enforce required community_id filter parameter in PostgREST/Supabase view?

I have a PostgreSQL view exposed through Supabase/PostgREST that shows approved collections from different communities. For security reasons, I need to ensure users always filter by community_id when querying this view. If no community_id filter is provided, it should return an empty array instead of all collections OR show an error with a hint to provide the community_id query parameter?

CREATE VIEW api.collections AS   
SELECT     
  q.id,
  q.community_id,
  q.description,
  -- other fields...
FROM api.all_collections q

CREATE VIEW api.collections AS   
SELECT     
  q.id,
  q.community_id,
  q.description,
  -- other fields...
FROM api.all_collections q

8 Replies

garyaustin•8mo ago

Normally you would need to use an rpc call and block the tables with RLS. Then a security definer function would return the data while using a parameter you can check. Even if you get the view to work I would think the table could still be viewed directly.

KingsleyOP•8mo ago

Thank you, I believe that should solve it. I'm curious, is it possible to tweak rpc functions urls e.g. /rest/v1/rpc/collections >> /rest/v1/collections

garyaustin•8mo ago

no You will likely want to return a setof collections which means using SQL for the function creation and not the Function UI.

KingsleyOP•8mo ago

I see, thanks for clarifying that. is it possible to get the best of both worlds such that I'm able to use SQL function to enforce the community_id value and still supports the Postgrest Filtering, Pagination & Sorting capabilities/format - Looks like this would require implementing filtering, sorting and pagination manually.

garyaustin•8mo ago

If you use rpc and return setof you can still use all the REST API filters on the rpc call. It looks just like a table.

KingsleyOP•8mo ago

Thank you, that worked! I get the error "Could not find the function api.get_collections without parameters in the schema cache" when I try to send a request to /rest/v1/rpc/get_collections, which I believe is expected, is it possible to customize the returned error? I have a parameter check logic in the code but that doesn't seem to catch that

CREATE OR REPLACE FUNCTION api.get_collections(community_id text)
  RETURNS SETOF api.get_collections_return_type
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$
BEGIN
    IF community_id IS NULL THEN
        RETURN;
    END IF;

    RETURN QUERY
...

CREATE OR REPLACE FUNCTION api.get_collections(community_id text)
  RETURNS SETOF api.get_collections_return_type
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$
BEGIN
    IF community_id IS NULL THEN
        RETURN;
    END IF;

    RETURN QUERY
...

garyaustin•8mo ago

There is a way to set the return error....https://docs.postgrest.org/en/v12/references/errors.html#raise-errors-with-http-status-codes

PostgREST 12.2

Errors

PostgREST error messages follow the PostgreSQL error structure. It includes MESSAGE, DETAIL, HINT, ERRCODE and will add an HTTP status code to the response. Errors from PostgreSQL: PostgREST will forward errors coming from PostgreSQL. For instance, on a failed constraint: HTTP Status Codes: Postg...

KingsleyOP•8mo ago

Thank you, that was helpful. I ended up with two separate functions - one without parameters and the other with the community_id parameter; not sure if there's a better way of doing this 😃

CREATE OR REPLACE FUNCTION api.get_collections()
  RETURNS SETOF api.get_collections_return_type
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$
BEGIN
    RAISE EXCEPTION 'community_id not found.'
    USING DETAIL = 'community_id is required to fetch collections.',
          HINT = 'you need to provide the community_id as query parameter in the url';
END;
$$;

CREATE OR REPLACE FUNCTION api.get_collections(community_id text)
  RETURNS SETOF api.get_collections_return_type
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$
BEGIN
    IF community_id IS NULL THEN
        RAISE EXCEPTION 'community_id not found.'
        USING DETAIL = 'community_id is required to fetch collections.',
              HINT = 'you need to provide community_id as query parameter in the url';
    END IF;

    RETURN QUERY
...
...

CREATE OR REPLACE FUNCTION api.get_collections()
  RETURNS SETOF api.get_collections_return_type
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$
BEGIN
    RAISE EXCEPTION 'community_id not found.'
    USING DETAIL = 'community_id is required to fetch collections.',
          HINT = 'you need to provide the community_id as query parameter in the url';
END;
$$;

CREATE OR REPLACE FUNCTION api.get_collections(community_id text)
  RETURNS SETOF api.get_collections_return_type
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$
BEGIN
    IF community_id IS NULL THEN
        RAISE EXCEPTION 'community_id not found.'
        USING DETAIL = 'community_id is required to fetch collections.',
              HINT = 'you need to provide community_id as query parameter in the url';
    END IF;

    RETURN QUERY
...
...

I think I found a better way, had to do with making the parameter optional by setting a default

CREATE OR REPLACE FUNCTION api.get_collections(community_id text default null)
  RETURNS SETOF api.get_collections_return_type
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$

CREATE OR REPLACE FUNCTION api.get_collections(community_id text default null)
  RETURNS SETOF api.get_collections_return_type
  LANGUAGE plpgsql
  SECURITY DEFINER
AS $$

Gaming

Programming

How to enforce required community_id filter parameter in PostgREST/Supabase view?

Did you find this page helpful?