pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRestrict Write Users
3+ messages / 3 participants
[nested] [flat]
* Restrict Write Users
@ 2023-06-20 11:23 Phani Prathyush Somayajula <phani.somayajula@pragmaticplay.com>
0 siblings, 1 reply; 3+ messages in thread
From: Phani Prathyush Somayajula @ 2023-06-20 11:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi All,
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
Regards,
Phani Pratz
PPBET-DBA
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Restrict Write Users
@ 2023-06-20 12:05 Erik Wienhold <ewie@ewie.name>
parent: Phani Prathyush Somayajula <phani.somayajula@pragmaticplay.com>
0 siblings, 1 reply; 3+ messages in thread
From: Erik Wienhold @ 2023-06-20 12:05 UTC (permalink / raw)
To: Phani Prathyush Somayajula <phani.somayajula@pragmaticplay.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Restrict Write Users
@ 2023-06-20 12:57 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 3+ messages in thread
From: MichaelDBA @ 2023-06-20 12:57 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: Phani Prathyush Somayajula <phani.somayajula@pragmaticplay.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
pgadvanced3.jpg (image/jpeg, 20.6K) download
view image
end of thread, other threads:[~2023-06-20 12:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-06-20 11:23 Restrict Write Users Phani Prathyush Somayajula <phani.somayajula@pragmaticplay.com>
2023-06-20 12:05 ` Erik Wienhold <ewie@ewie.name>
2023-06-20 12:57 ` MichaelDBA <MichaelDBA@sqlexec.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox