Revoke privileges for a user on tables with like names

Question

Attempting to adapt this solution, but for revoking privileges in this way:

DO
$$
DECLARE
    t record;
BEGIN
    FOR t IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'my_schema'
        AND table_name LIKE 'test\_%'
        LOOP
            EXECUTE format('REVOKE DELETE, INSERT, UPDATE ON TABLE %I.%I FROM test;', table_schema, table_name);
        END LOOP;
END;
$$ LANGUAGE plpgsql;

However, I'm getting "column table_schema does not exist". Am I approaching this the right way?

Using postgresql 9.6 (french locale).

score 3 · Accepted Answer · answered May 19 '21 at 20:41

You need to reference the record of the cursor loop:

EXECUTE format('REVOKE DELETE, INSERT, UPDATE ON TABLE %I.%I FROM test;', 
               t.table_schema, t.table_name);
               ^-- here        ^-- here

score 1 · Answer 2 · answered May 20 '21 at 11:26

Or use psql and run

SELECT format(
          'REVOKE DELETE, INSERT, UPDATE ON TABLE %I.%I FROM test;',
          table_schema,
          table_name
       )
FROM information_schema.tables
WHERE table_schema = 'my_schema'
AND table_name LIKE 'test\_%' \gexec

Revoke privileges for a user on tables with like names

2 Answers2