3/03/2020

How to expose all public stored function in PostgREST/SubZero

DO $$
    DECLARE
        schema_name INFORMATION_SCHEMA.routines.routine_schema%TYPE = 'api';
        fns CURSOR FOR
            select routine_name from INFORMATION_SCHEMA.routines WHERE routine_schema = schema_name;
    BEGIN
        FOR fn_record IN fns LOOP
                EXECUTE 'grant execute on function ' || schema_name || '.' || fn_record.routine_name || ' to anonymous;';
            END LOOP;
    END$$;

Will expose all store functions from the api public schema in the generated swagger/openapi specification from PostgREST/SubZero. Of course, ensure that all underneath private tables have row-level-security enabled to stay secure.

How to expose all public views in PostgREST/SubZero

DO $$
    DECLARE
        schema_name INFORMATION_SCHEMA.views.table_schema%TYPE = 'api';
        views CURSOR FOR select table_name from INFORMATION_SCHEMA.views WHERE table_schema = schema_name;
    BEGIN
        FOR view_record IN views LOOP
                EXECUTE 'grant select, insert, update, delete on ' || schema_name || '.' || view_record.table_name || ' to anonymous;';
            END LOOP;
    END$$;

Will expose all views from the api public schema in the generated swagger/openapi specification from PostgREST/SubZero. Of course, ensure that all underneath private tables have row-level-security enabled.

« »
 
 
Made with on a hot august night from an airplane the 19th of March 2017.