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!
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 |