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, 2016 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, 2020 at 4:21
  • which changes more often: the number of users or the number of schemas?
    – Jasen
    Jan 21, 2020 at 4:23

1 Answer 1

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 and acknowledge you have read our privacy policy.

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