pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedShared postgres-cluster / permissions
3+ messages / 3 participants
[nested] [flat]
* Shared postgres-cluster / permissions
@ 2022-03-17 10:48 Christian Lehmann <info@chlehmann.ch>
0 siblings, 2 replies; 3+ messages in thread
From: Christian Lehmann @ 2022-03-17 10:48 UTC (permalink / raw)
To: pgsql-admin
Hi
using postgres 14.2 on RHEL.
I am working on a shared postgres-cluster where users should not be able to
use other users databases. I followed this guide to achieve my goal (
https://wiki.postgresql.org/wiki/Shared_Database_Hosting) and it mostly
works.
When creating a new database i create two roles (dbname_rw and dbnabe_ro)
where the _rw-role is owner of the database. I also create a s_databse
serviceuser and append it to the _rw role. More users can be created on
request and are appended on the rw or ro-role.
There is a "alter default privileges in schema public" in place which
grants ALL to the rw-role for new tables and select to the ro-role. but it
only does it, if the rw-role is creating the table. if the user (for
example serviceuser) is creating new items, it does not add this default
privs. (which is by design, but bad because other users won't be able to
access the table).
do you have a good way to work around this? is my role-setup "correct" or
do you have a better idea how to do this? as i understood, there is no
possibility to have a wildcard-default-privilege-setting, right?
thanks for any help with this!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Shared postgres-cluster / permissions
@ 2022-03-17 11:29 Holger Jakobs <holger@jakobs.com>
parent: Christian Lehmann <info@chlehmann.ch>
1 sibling, 0 replies; 3+ messages in thread
From: Holger Jakobs @ 2022-03-17 11:29 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi Christian,
You can use event triggers for this:
https://www.postgresql.org/docs/current/event-triggers.html
Regards,
Holger
Am 17.03.22 um 11:48 schrieb Christian Lehmann:
> Hi
> using postgres 14.2 on RHEL.
>
> I am working on a shared postgres-cluster where users should not be
> able to use other users databases. I followed this guide to achieve my
> goal (https://wiki.postgresql.org/wiki/Shared_Database_Hosting) and it
> mostly works.
>
> When creating a new database i create two roles (dbname_rw and
> dbnabe_ro) where the _rw-role is owner of the database. I also create
> a s_databse serviceuser and append it to the _rw role. More users can
> be created on request and are appended on the rw or ro-role.
>
> There is a "alter default privileges in schema public" in place which
> grants ALL to the rw-role for new tables and select to the ro-role.
> but it only does it, if the rw-role is creating the table. if the user
> (for example serviceuser) is creating new items, it does not add this
> default privs. (which is by design, but bad because other users won't
> be able to access the table).
>
> do you have a good way to work around this? is my role-setup "correct"
> or do you have a better idea how to do this? as i understood, there is
> no possibility to have a wildcard-default-privilege-setting, right?
>
> thanks for any help with this!
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Shared postgres-cluster / permissions
@ 2022-03-17 15:29 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Christian Lehmann <info@chlehmann.ch>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2022-03-17 15:29 UTC (permalink / raw)
To: Christian Lehmann <info@chlehmann.ch>; pgsql-admin
On Thu, 2022-03-17 at 11:48 +0100, Christian Lehmann wrote:
> There is a "alter default privileges in schema public" in place which grants ALL
> to the rw-role for new tables and select to the ro-role. but it only does it, if
> the rw-role is creating the table. if the user (for example serviceuser) is creating
> new items, it does not add this default privs. (which is by design, but bad because
> other users won't be able to access the table).
>
> do you have a good way to work around this? is my role-setup "correct" or do you
> have a better idea how to do this? as i understood, there is no possibility to have
> a wildcard-default-privilege-setting, right?
You can run an ALTER DEFAULT PRIVILEGES for each user.
But often it is better to have only one role own the tables. If all roles that may create
tables are members of the rw-role, you can give CREATE on the schema only to that role,
and anybody who wants to create an object has to SET ROLE "rw-role".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-03-17 15:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-17 10:48 Shared postgres-cluster / permissions Christian Lehmann <info@chlehmann.ch>
2022-03-17 11:29 ` Holger Jakobs <holger@jakobs.com>
2022-03-17 15:29 ` Laurenz Albe <laurenz.albe@cybertec.at>
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