Greenplum/Postgresql Can I grant a user truncate privileges on tables start with tmp_?

Question

I want to create a user:etl_user to do etl job, so I grant select, update, insert privileges to etl_user, however sometimes truncate also needed, but for security, I want only grant truncate on specific tables, like table name start with tmp_.

Any idea? Thanks in advance.

Answer 1

Usually in such situations, bash helps

for tbl in `psql -qAt -c "select schemaname || '.' || tablename
    from pg_tables
    where tablename like 'tmp_%';" -d database_name`; 
do 
    psql -c "grant truncate on $tbl to etl_user" -d database_name ; 
done