| Everything that Erik said is good, but
looking at this from a bird's eye view, I would recommend using this
general approach which will make it soooooo much easier to manage user
privileges: NEVER assign privileges directory to a LOGIN role. ONLY assign privileges to a NON-LOGIN roles (following rules like the ones specified by Erik). Then when you have your NON-LOGIN roles (aka groups) defined with appropriate privileges, you can easily effect them on LOGIN users by simply adding or removing them from belonging to groups (NON-LOGIN roles). Assuming you defined a WRITE and READ NON-LOGIN roles, you can easily remove a LOGIN user from the WRITE group and add them to the READ group. Erik Wienhold wrote on 6/20/2023 8:05 AM: On 20/06/2023 13:23 CEST Phani Prathyush Somayajula <phani.somayajula@xxxxxxxxxxxxxxxxx> wrote: Is there a way to restrict write access to a user by restricting the user to have read-only on other databases on the instance. I’m using postgresql-14 versionYou should look into https://www.postgresql.org/docs/14/ddl-priv.html. Start with a user that has no privileges and grant additional privileges as necessary give read and/or write access. The user must not be the owner of database objects, must not be a member of an owner role, and must not be a superuser. Also check default privileges and privileges granted to PUBLIC. But granting privileges in one database does not affect privileges in other databases, except for role memberships because roles are not tied to a specific database. -- Erik |
