From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Shared postgres-cluster / permissions
Date: Thu, 17 Mar 2022 12:29:40 +0100
Message-ID: <36b97e1a-8cba-4075-8a66-6a07e22270e3@jakobs.com> (raw)
In-Reply-To: <CAEh-KgtfhSp4q+0CbMtjTDAiyNZB1B+6SZ1W2CLJRRi2TxFpyg@mail.gmail.com>
References: <CAEh-KgtfhSp4q+0CbMtjTDAiyNZB1B+6SZ1W2CLJRRi2TxFpyg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
Subject: Re: Shared postgres-cluster / permissions
In-Reply-To: <36b97e1a-8cba-4075-8a66-6a07e22270e3@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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