CODEWITHSUNDEEP
postgresql
madibat
madibat

Reputation: 111

Revoke privleges for user on all schemas

I'd like to revoke privleges for all schemas from a specific user and thought I could use a for loop for that. But I just can't get it to work.

I'm using dbeaver and a postgresql image in docker with version 11.3 to execute my commands. Here's what I tried so far:

FOR temprow IN
    SELECT schema_name FROM information_schema.schemata
LOOP
    REVOKE ALL PRIVILEGES on ALL tables in schema temprow.schema_name FROM TestUser1;
END LOOP;

I always get error near for loop as an exception. Is there a way to get this to work?

Upvotes: 0

Views: 1068

Answers (1)

JGH
JGH

Reputation: 17906

You can use psql and its \gexec functionality:

SELECT format('REVOKE ALL PRIVILEGES on ALL tables in schema %I FROM TestUser1', schema_name)
FROM information_schema.schemata;\gexec

Upvotes: 2

Related Questions