1

Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy way/script to do that?

3
  • Does postgres support roles like SQL 20xx?
    – CoveGeek
    Jun 6 '16 at 18:46
  • @CoveGeek I don't know if they are "like SQL 20xx", but it has had roles for a long time.
    – Jasen
    Jan 21 '20 at 4:21
  • which changes more often: the number of users or the number of schemas?
    – Jasen
    Jan 21 '20 at 4:23
1

Yes, using DO, cursors, execute, and the pg_namespace table:

DO $$DECLARE _schema record; _user name := '<your_username>'
BEGIN
  FOR _schema IN
    SELECT * FROM pg_namespace WHERE nspowner > 10 OR nspname = 'public'
  LOOP
    EXECUTE 'GRANT SELECT, INSERT ON SCHEMA '||
            quote_ident(_schema.nspname)||' TO '||quote_ident(_user);
  END LOOP;
END;$$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.