6

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

6

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.

5

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:

-4

First revoke your database REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

then try with your GRANT query.

  • 1
    That query cannot possibly work, and REVOKEing first - be it CONNECT ON DATABASE or anything else - will not help. – Craig Ringer Jun 16 '15 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.