In a multi-tenanted application, each customer's data is stored in a separate schema of a postgreSQL database.
I want to grant certain permissions (read-only in my case, but I don't think that's relevant) to a user over ALL schemata in my database.
I know how I can grant permissions to a user over ONE particular schema, and I can write a script which iterates through all the existing schemata and give permissions to the user.
Yet, new schemata allocated to new customers will be excluded, and I will have to re-run the script regularly.
Is there a solution to programmatically give a certain permission to a user over all existing and future schemata?
It's your lucky day! Not only are there extensive manuals written by the core team that develops PostgreSQL but they are freely available on the internet.
https://www.postgresql.org/docs/current/predefined-roles.html
The link above is a page covering standard pre-defined roles. I think you probably want to grant the read_all_data
role to your user.