Revoke privileges for a user on tables with like names

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Method 2

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply