PostgreSQL GRANT ALL on mutliple tables using LIKE

Question

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

Answer 1

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.

Answer 2

What @Craig already explained.
Plus, since GRANT can grant privileges on multiple objects at once, you can use a single statement without looping:

DO
$$
BEGIN
EXECUTE (
   SELECT 'GRANT ALL ON TABLE '
       || string_agg (format('%I.%I', table_schema, table_name), ',')
       || ' TO test'
   FROM   information_schema.tables
   WHERE  table_schema = 'public'
   AND    table_name LIKE 'test\_%'
   );
END
$$;

Related:

• Change column type from varchar to text in all tables at once

Answer 3

For postgresql 11 and later, the GRANT statement can take a comma separated list of objects of the same type as the grant targets, as Erwin's answer points out. What's left is the creation of the list of objects you need.

For me, awk's easier to used for what Erwin manages quite elegantly with PL/pgSQL:

psql DBNAME -c '\dt ${TABLE_PATTERN}*' | awk -F'|' 'BEGIN{s="";t=""} /^\s*public/{gsub(/ /, "", $2); t=t s $2; if(!s){s=","}} END{print t}' 

The above comma seperated list can then be used as the input directly (via copy/paste) or via parameter replacement by piping through xargs.

Note: I understand this is not a pure DBA answer, but there are definite instances of this kind of task falling under the devops umbrella (which is how I came to this question).