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;
%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.
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 $$;
First revoke your database
REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;
then try with your