patternsqlModerate

PostgreSQL GRANT ALL on mutliple tables using LIKE

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

postgresqltablesallgrantlikemutlipleusing

Problem

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 ...

Solution

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;', t.table_schema, t.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.

Code Snippets

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;', t.table_schema, t.table_name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Context

StackExchange Database Administrators Q#104172, answer score: 11

Revisions (0)

No revisions yet.