Re: Shared postgres-cluster / permissions

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