How to allow users to create and modify tables only in their own schemas, but with generic table owner
Hi List I'd like to setup my database in a way that only a superuser may create schemas, then grants permission to a specific user to create tables inside this schema. This should work so far with GRANT CREATE ON SCHEMA ... TO user_a. However I want the table owner not to be the user that creates the tables. Instead the owner should rather be a generic role (e.g. table_owner), and the owner should be the same over all tables of the whole database. This would work, too, if I grant membership in role table_owner to all users that may create tables. (The users must issue a SET ROLE table_owner before creating tables.) What I didn't achieve so far is making sure that user_a who created tables in schema_a cannot crete/modify tables of schema_b that were created by user_b. Do you see any way to achieve this, while still sticking to that generic owner role? Thanks a lot for your thoughts. Andy
PGDG PostgreSQL Debian package: Question on conditions for creation of new cluster
Hi I'm trying to install the postgresql-17 Debian package from PGDG apt repo alongside an already existing PostgreSQL 16 installation. So there is already a PostgreSQL 16 cluster on my machine (which I want to upgrade to version 17 using the pg_upgrade command later on). If I'm not wrong, the PostgreSQL packages used to automatically create a new cluster during installation, even if there already was an older cluster present. (Using a different port in this case.) But this doesn't happen with the 17.3 package. Does anyone know if this behavior has changed? Or anyone can explain on which conditions a new cluster is created and when not? There was a now closed discussion on this topic on Stack Overflow: https://stackoverflow.com/questions/79441161/postgres-17-3-installation-no-longer-creates-a-new-cluster. Of course, I will create the additional cluster using pg_createcluster if this is the way to go. The point is that I'm installing PostgreSQL using an Ansible script, and pg_createcluster was not needed until now. So before I update my script, I hope to get some more information about the behavior of the PostgreSQL packages regarding automatic cluster creation. Thanks a lot, Andy smime.p7s Description: S/MIME cryptographic signature
AW: PGDG PostgreSQL Debian package: Question on conditions for creation of new cluster
> -Ursprüngliche Nachricht- > On Wed, 2025-02-19 at 08:45 +, Schmid Andreas wrote: > > I'm trying to install the postgresql-17 Debian package from PGDG apt > > repo alongside an already existing PostgreSQL 16 installation. So > > there is already a PostgreSQL 16 cluster on my machine (which I want > > to upgrade to version 17 using the pg_upgrade command later on). > > > > If I'm not wrong, the PostgreSQL packages used to automatically create > > a new cluster during installation, even if there already was an older > > cluster present. (Using a different port in this case.) But this > > doesn't happen with the 17.3 package. Does anyone know if this > > behavior has changed? Or anyone can explain on which conditions a new > > cluster is created and when not? > > > > There was a now closed discussion on this topic on Stack Overflow: > > https://stackoverflow.com/questions/79441161/postgres-17-3-installatio > > n-no-longer-creates-a-new-cluster Of course, I will create the > > additional cluster using pg_createcluster if this is the way to go. > > The point is that I'm installing PostgreSQL using an Ansible script, > > and pg_createcluster was not needed until now. > > So before I update my script, I hope to get some more information > > about the behavior of the PostgreSQL packages regarding automatic cluster > creation. > > I just tried on an Ubuntu system (I think that the packages are pretty much > the same), and installing 17.3 created a cluster. > I think the Stackoverflow question was about installing 17.3 if v17 is already > installed. In that case, no new cluster is created. > Well, according to the last comment below the Stack Overflow question, it was about installing 17.3 with a v14 cluster already present. Anyways, I now tried on an Ubuntu 24.04 machine as well, and installing 17.3 alone created a cluster. Cleaned up the machine, then installed 15.11, which created a cluster. Installed 16.7 alongside, which did not create an additional cluster. So it's probably not a 17.3 problem. Same outcome on Ubuntu 20.04. Maybe there generally is no cluster created if there is one present already. I now checked /usr/share/postgresql-common/maintscripts-functions which is installed by the postgresql-common_273.pgdg24.04+1_all.deb package. This script contains the create_main_cluster() function, which according to a comment "skips if any other cluster already exists". So could it be that this function has been updated recently? Can you point me to the repo where this script is maintained? smime.p7s Description: S/MIME cryptographic signature
AW: AW: PGDG PostgreSQL Debian package: Question on conditions for creation of new cluster
> -Ursprüngliche Nachricht- > I think that lives here: > https://salsa.debian.org/postgresql/postgresql-common Yes, thanks for the hint. The commit which apparently introduced the change is https://salsa.debian.org/postgresql/postgresql-common/-/commit/d9139fa42a39b5b6fabbffc9f020fad4dce5: "debian/maintscripts-functions: Create "main" cluster only when no other clusters exist yet." Maybe I'm going to ask on the pgsql-pkg-debian mailing list for more details about it, as proposed by Laurenz. @Adrian, please note that I'm not the one who asked on Stack Overflow, so I'm not cross posting over here. And I definitely am doing an "alongside" installation of two major versions: On my server is installed v16, and now I'm adding v17, as described in my first message. Thanks again to everybody for your help. Andy smime.p7s Description: S/MIME cryptographic signature
