Re: Restrict Write Users

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@pragmaticplay.com> 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
version

You 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

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343