Function to grant all in Redshift

grant_all.sql

CREATE OR REPLACE PROCEDURE grant_all_permissions(target_user TEXT)
AS $$
DECLARE
    schema_record RECORD;
BEGIN
    IF target_user IS NULL OR target_user = '' THEN
        RAISE EXCEPTION 'username cannot be null or empty';
    END IF;

    FOR schema_record IN 
        SELECT nspname 
        FROM pg_catalog.pg_namespace 
        WHERE nspname NOT LIKE 'pg_%' 
        AND nspname not in ('information_schema', 'catalog_history')
    LOOP
        EXECUTE 'GRANT USAGE ON SCHEMA ' || quote_ident(schema_record.nspname) || ' TO ' || quote_ident(target_user);
        
        EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || quote_ident(schema_record.nspname) || ' TO ' || quote_ident(target_user);
        
        EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA ' || quote_ident(schema_record.nspname) 
                || ' GRANT SELECT ON TABLES TO ' || quote_ident(target_user);
                
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CALL grant_all_permissions('user_datahub');