postgresqlpermissionsschemadatabase-administration

How do I give a postgreSQL user read-only permissions on all schemata programmatically?


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?


Solution

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