Shared postgres-cluster / permissions

From: Christian Lehmann <info(at)chlehmann(dot)ch>
To: pgsql-admin(at)postgresql(dot)org
Subject: Shared postgres-cluster / permissions
Date: 2022-03-17 10:48:53
Message-ID: CAEh-KgtfhSp4q+0CbMtjTDAiyNZB1B+6SZ1W2CLJRRi2TxFpyg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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!

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Holger Jakobs 2022-03-17 11:29:40 Re: Shared postgres-cluster / permissions
Previous Message Shaozhong SHI 2022-03-16 20:32:02 Apparently table locks are the key issue to see red flags