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(dot)somayajula(at)pragmaticplay(dot)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(at)sqlexec(dot)com <mailto:michaelvitale(at)sqlexec(dot)com>

703-600-9343