PostgreSQL GRANT ALL on mutliple tables using LIKE I have more than 100 tables named public.test_*

How can I easily GRANT ALL access to the user test to all that tables at once?

I tried with:

GRANT ALL ON TABLE public.test_* TO test;


But it is not working ...
GRANT doesn't take wildcards in table identifiers.

You can use ON ALL TABLES IN SCHEMA, but that requires a single schema name.

If you want to do things with wildcard pattern table names you will need to use PL/PgSQL's EXECUTE format(...) in a DO block to loop over the information_schema.tables view. See many related answers here on DBA.se and Stack Overflow for dynamic DDL in PL/PgSQL.

Untested rough example to give you the idea:

DO
$$
DECLARE
    t record;
BEGIN
    FOR t IN 
    SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name LIKE 'test\_%'
    LOOP
        EXECUTE format('GRANT ALL ON TABLE %I.%I TO test;', table_schema, table_name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;


For what %I means see the reference for the format function. If you don't have a format function your PostgreSQL is obsolete and you should probably plan an upgrade; you can use quote_ident and string concatenation in the mean time.