Search code examples
sqlpostgresqldynamic-sqlsql-grant

How to grant permissions in Postgres on tables that start with parcel?

GRANT UPDATE 
ON (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'parcel%') 
TO police;
Solution

  • GRANT takes only literal table names. What you want requires dynamic SQL, ie dynamically building a statement and executing it in an anonymous block.

    Consider:

    do
$$
begin
    execute (
        select 
            'grant all on table '
            || string_agg (format('%I.%I', table_schema, table_name), ', ')
            || ' to police'
        from information_schema.tables
        where table_name like 'parcel%'
    );
end
$$;