Re: SQL command : ALTER DATABASE OWNER TO
Hello, as one of my colleagues fell into the same trap I repeat my request for correction of the documentation on this subject. Many thanks in advance Regards Gilles - Mail original - De: "gparc" À: "pgsql-docs" Envoyé: Mardi 8 Mars 2022 10:50:38 Objet: SQL command : ALTER DATABASE OWNER TO Hello, for this "ALTER DATABASE" form, it should be mentioned that after execution of the command, the old database owner loses all his privileges on it (even connection) although it might still owns schemas or objects (tables, index,...) inside it. Thanks in advance to add this important precision. Regards Gilles
Re: SQL command : ALTER DATABASE OWNER TO
Hello, maybe a misunderstanding of my part, but your proposed modification doesn't matched with the current behaviour of the command as precisely the object privileges of the old owner are **NOT** transferred to the new owner along with the ownership Regards Gilles - Mail original - De: "Daniel Gustafsson" À: "Laurenz Albe" Cc: "gparc" , "pgsql-docs" Envoyé: Mercredi 24 Janvier 2024 15:26:22 Objet: Re: SQL command : ALTER DATABASE OWNER TO > On 24 Jan 2024, at 15:23, Laurenz Albe wrote: > > On Wed, 2024-01-24 at 11:08 +0100, gp...@free.fr wrote: >> for this "ALTER DATABASE" form, it should be mentioned that after execution >> of the command, >> the old database owner loses all his privileges on it (even connection) >> although it might >> still owns schemas or objects (tables, index,...) inside it. >> >> Thanks in advance to add this important precision. > > How about this: > > diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml > index 4044f0908f..44042f863c 100644 > --- a/doc/src/sgml/ddl.sgml > +++ b/doc/src/sgml/ddl.sgml > @@ -1891,6 +1891,8 @@ ALTER TABLE table_name OWNER > TO new_owne >Superusers can always do this; ordinary roles can only do it if they are >both the current owner of the object (or inherit the privileges of the >owning role) and able to SET ROLE to the new owning > role. > + All object privileges of the old owner are transferred to the new owner > + along with the ownership. > Doesn't seem unreasonable to me, it won't make the docs harder to read and use for experienced users while it may make them easier to follow for new users. -- Daniel Gustafsson
Re: SQL command : ALTER DATABASE OWNER TO
- Mail original - De: "Laurenz Albe" À: "gparc" , "Daniel Gustafsson" Cc: "pgsql-docs" Envoyé: Mercredi 24 Janvier 2024 16:35:10 Objet: Re: SQL command : ALTER DATABASE OWNER TO On Wed, 2024-01-24 at 15:40 +0100, gp...@free.fr wrote: > maybe a misunderstanding of my part, but your proposed modification doesn't > matched > with the current behaviour of the command as precisely the object privileges > of the old owner are **NOT** transferred > to the new owner along with the ownership But that is what happens. The permissions are transferred to the new owner, so the old owner doesn't have any privileges on the object (and, in your case, cannot connect to the database any more). Yours, Laurenz Albe Laurenz, may be better with an example to explain what I mean with "the old database owner loses all his privileges on it (even connection) although it might still owns schemas or objects (tables, index,...) inside it" [postgres] $ psql psql (14.10) [postgres@PGDEV14] postgres=# create user tst password 'tst'; CREATE ROLE [postgres@PGDEV14] postgres=# create database tst owner = tst; CREATE DATABASE [postgres@PGDEV14] postgres=# grant all on database tst to tst; GRANT [postgres@PGDEV14] postgres=# \l+ tst Liste des bases de données Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits d'accès | Taille | Tablespace | Description -+--+--+-+--++-++- tst | tst | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/tst +| 9809 kB | pg_default | | | | | | tst=CTc/tst | || (1 ligne) [postgres@PGDEV14] postgres=# \c tst tst Mot de passe pour l'utilisateur tst : Vous êtes maintenant connecté à la base de données « tst » en tant qu'utilisateur « tst ». [tst@PGDEV14] tst=> create schema tst; CREATE SCHEMA [tst@PGDEV14] tst=> create table t1 (x int); CREATE TABLE [tst@PGDEV14] tst=> \dn+ tst Liste des schémas Nom | Propriétaire | Droits d'accès | Description -+--++- tst | tst || (1 ligne) [tst@PGDEV14] tst=> \dt+ t1 Liste des relations Schéma | Nom | Type | Propriétaire | Persistence | Méthode d'accès | Taille | Description +-+---+--+-+-+-+- tst| t1 | table | tst | permanent | heap| 0 bytes | (1 ligne) [tst@PGDEV14] tst=> \c - postgres Vous êtes maintenant connecté à la base de données « tst » en tant qu'utilisateur « postgres ». [postgres@PGDEV14] tst=# alter database tst owner to postgres; ALTER DATABASE [postgres@PGDEV14] tst=# \l+ tst Liste des bases de données Nom | Propriétaire | Encodage | Collationnement | Type caract. |Droits d'accès | Taille | Tablespace | Description -+--+--+-+--+---+-++- tst | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/postgres +| 9809 kB | pg_default | | | | | | postgres=CTc/postgres | || (1 ligne) [postgres@PGDEV14] tst=# \dn+ tst Liste des schémas Nom | Propriétaire | Droits d'accès | Description -+--++- tst | tst || (1 ligne) [postgres@PGDEV14] tst=# \dt tst.t1 Liste des relations Schéma | Nom | Type | Propriétaire +-+---+-- tst| t1 | table | tst (1 ligne) Regards Gilles
Re: SQL command : ALTER DATABASE OWNER TO
De: "David G. Johnston" À: "Laurenz Albe" Cc: "gparc" , "Daniel Gustafsson" , "pgsql-docs" Envoyé: Mercredi 24 Janvier 2024 17:36:43 Objet: Re: SQL command : ALTER DATABASE OWNER TO On Wed, Jan 24, 2024 at 9:13 AM Laurenz Albe < [ mailto:laurenz.a...@cybertec.at | laurenz.a...@cybertec.at ] > wrote: On Wed, 2024-01-24 at 08:47 -0700, David G. Johnston wrote: > I dislike this change, ownership of an object is completely independent of > the grant system of privileges. The granted privileges of the old row do > not transfer to the new owner when alter ... owner to is executed. CREATE TABLE mytab (); REVOKE ALL ON mytab FROM PUBLIC; \z mytab Access privileges Schema │ Name │ Type │ Access privileges │ Column privileges │ Policies ╪═══╪═══╪═══╪═══╪══ public │ mytab │ table │ postgres=arwdDxt/postgres │ │ (1 row) ALTER TABLE mytab OWNER TO laurenz; \z mytab Access privileges Schema │ Name │ Type │ Access privileges │ Column privileges │ Policies ╪═══╪═══╪═╪═══╪══ public │ mytab │ table │ laurenz=arwdDxt/laurenz │ │ (1 row) You need to actually revoke something to make the point stand out. postgres=# \z tt1 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies +--+---+---+---+-- public | tt1 | table | davidj=arwdDxt/davidj | | (1 row) postgres=# revoke update on tt1 from davidj; REVOKE postgres=# \z tt1 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies +--+---+--+---+-- public | tt1 | table | davidj=ardDxt/davidj | | (1 row) postgres=# alter table tt1 owner to testowner; ALTER TABLE postgres=# \z tt1 Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies +--+---++---+-- public | tt1 | table | testowner=ardDxt/testowner | | (1 row) The new owner, testowner, is missing the same update privilege that davidj removed from himself. In short, setting owner does indeed cause explicit grants to appear in the system, grants that can be revoked. And so, yes, transferring ownership transfers the set of grants currently in effect for the existing owner. I can see making this detail more clear in the DDL chapter. It is unrelated to the confusion behind the topic of this thread though. David J. Hello again, note that my point concerns "alter database" not "alter table". See my last reply for an example Regards
Re: SQL command : ALTER DATABASE OWNER TO
De: "David G. Johnston" À: "gparc" Cc: "Laurenz Albe" , "Daniel Gustafsson" , "pgsql-docs" Envoyé: Mercredi 24 Janvier 2024 17:50:17 Objet: Re: SQL command : ALTER DATABASE OWNER TO On Wed, Jan 24, 2024 at 9:23 AM < [ mailto:gp...@free.fr | gp...@free.fr ] > wrote: - [postgres] $ psql psql (14.10) You really should add commentary, especially since you never demonstrated the tst role (I advise picking different names for all of the objects in the future) being unable to login. Which they should be able to since public is shown to have "c" connect privileges (=Tc/tst) BQ_BEGIN [postgres@PGDEV14] postgres=# create user tst password 'tst'; CREATE ROLE [postgres@PGDEV14] postgres=# create database tst owner = tst; CREATE DATABASE BQ_END This next command is pointless, it is a no-op, as soon as you made them owner of the tst database they already had all privileges to it, granted by the same user that created the database. And only it, that command is not recursing through the database into schemas and tables and adding more permissions. That isn't how this all works, a database is an object. While it is also a concept that encompasses the entire schema within it the permissions system only cares about the first definition. BQ_BEGIN [postgres@PGDEV14] postgres=# grant all on database tst to tst; GRANT [postgres@PGDEV14] postgres=# \l+ tst Liste des bases de données Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits d'accès | Taille | Tablespace | Description -+--+--+-+--++-++- tst | tst | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/tst +| 9809 kB | pg_default | | | | | | tst=CTc/tst | | | (1 ligne) BQ_END What are you trying to demonstrate here? BQ_BEGIN [postgres@PGDEV14] tst=# \dn+ tst Liste des schémas Nom | Propriétaire | Droits d'accès | Description -+--++- tst | tst | | (1 ligne) BQ_END David J. David, what I wanted to demonstrate/convey is that when I alter the ownership of a **database**, the old owner loses all his privileges on it (even CONNECT) although he still owns schema and objects (table, index,..) inside it. As such, he can't use his own schema anymore. That's why I propose to update the documentation as it's weird, at least for me, when you get caught by this behaviour. Regards Gilles
Re: SQL command : ALTER DATABASE OWNER TO
David, reading again your last reply, it reminded me that as soon as we create a database we revoke default PUBLIC grants (i.e revoke all on from public) to grant only databases privileges to specific roles/users. That's why after changing database ownership, we have to (re)grant privileges (ie. grant all on database) to the old owner...and that's what i forgot to do. So no problem at all and documentation is OK. Sorry for the noise ! Best regards Gilles De: "gparc" À: "David G. Johnston" Cc: "Laurenz Albe" , "Daniel Gustafsson" , "pgsql-docs" Envoyé: Mercredi 24 Janvier 2024 18:11:30 Objet: Re: SQL command : ALTER DATABASE OWNER TO De: "David G. Johnston" À: "gparc" Cc: "Laurenz Albe" , "Daniel Gustafsson" , "pgsql-docs" Envoyé: Mercredi 24 Janvier 2024 17:50:17 Objet: Re: SQL command : ALTER DATABASE OWNER TO On Wed, Jan 24, 2024 at 9:23 AM < [ mailto:gp...@free.fr | gp...@free.fr ] > wrote: - [postgres] $ psql psql (14.10) You really should add commentary, especially since you never demonstrated the tst role (I advise picking different names for all of the objects in the future) being unable to login. Which they should be able to since public is shown to have "c" connect privileges (=Tc/tst) BQ_BEGIN [postgres@PGDEV14] postgres=# create user tst password 'tst'; CREATE ROLE [postgres@PGDEV14] postgres=# create database tst owner = tst; CREATE DATABASE BQ_END This next command is pointless, it is a no-op, as soon as you made them owner of the tst database they already had all privileges to it, granted by the same user that created the database. And only it, that command is not recursing through the database into schemas and tables and adding more permissions. That isn't how this all works, a database is an object. While it is also a concept that encompasses the entire schema within it the permissions system only cares about the first definition. BQ_BEGIN [postgres@PGDEV14] postgres=# grant all on database tst to tst; GRANT [postgres@PGDEV14] postgres=# \l+ tst Liste des bases de données Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits d'accès | Taille | Tablespace | Description -+--+--+-+--++-++- tst | tst | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/tst +| 9809 kB | pg_default | | | | | | tst=CTc/tst | | | (1 ligne) BQ_END What are you trying to demonstrate here? BQ_BEGIN [postgres@PGDEV14] tst=# \dn+ tst Liste des schémas Nom | Propriétaire | Droits d'accès | Description -+--++- tst | tst | | (1 ligne) BQ_END David J. David, what I wanted to demonstrate/convey is that when I alter the ownership of a **database**, the old owner loses all his privileges on it (even CONNECT) although he still owns schema and objects (table, index,..) inside it. As such, he can't use his own schema anymore. That's why I propose to update the documentation as it's weird, at least for me, when you get caught by this behaviour. Regards Gilles
Re: Add a different archive_command example for Linux / Unix
Thanks Stephen for your detailed reply and broad perspective. But I see the cp example command used **as is** most of the time. Regards Gilles - Mail original - > De: "Stephen Frost" > À: "gparc" , "pgsql-docs" > Envoyé: Jeudi 8 Février 2024 13:00:13 > Objet: Re: Add a different archive_command example for Linux / Unix > Greetings, > > * PG Doc comments form (nore...@postgresql.org) wrote: >> Hello, >> in >> https://www.postgresql.org/docs/16/continuous-archiving.html#BACKUP-ARCHIVING-WAL >> the example given could be improved for Linux / Unix environment. >> >> As cp command is buffered it means it could return success although the data >> didn't reach the disk >> which puts backups at risk. > > Yup. > >> I propose to use dd command with its fsync option. >> >> So the actual equivalent example would be : >> >> archive_command = 'dd if=%p of=/mnt/server/archivedir/%f bs=1M >> conv=fsync,excl status=none' # Unix >> >> What do you think ? > > This doesn't fsync the directory though, for one thing, and there are > other considerations beyond that when having archive_command run and > more generally when doing backups with PG. In short, the example in the > documentation is not to ever be used but is intended to show how the > replacement is done when the command is called, so that backup tool > authors know how it works. > > In reality though, to write backup software for PG, you really do need > to know PG in much more detail than the documentation provides, which > means reading the source- for example, backup software should be > checking the pg_control file's CRC as it's possible to read it just as > it's being written and end up with an invalid pg_control file in the > backup, making the backup invalid. There's been some discussion about > how to improve this situation but nothing exists today from PG, so > backup authors have to handle it. This is just one example, there are > lots of others- unlogged table handling, temporary file handling, etc, > etc. > > I'd strongly recommend using one of the existing well maintained backup > tools which have been written specifically for PG for your backups. > Writing a new backup tool for PG is a good bit of work and isn't > really reasonable to do with shell scripts or simple unix commands. > > I do feel that we could improve the documentation around this by > dropping comments like "using any convenient file-system-backup tool > such as tar or cpio" as those don't, for example, support any way to > reasonably deal with unlogged tables by themselves. Technically you > could scan the data directory and provide an exclude file, or not > include unlogged table files in the list of files to include, but then > you're starting to get into things like how to tell if a file is > associated with an unlogged table or not and while that's deep in the > documentation, we don't make any mention or reference to unlogged tables > in the backup documentation. Perhaps an addition to the low-level > documentation under 'Backing Up The Data Directory' along these lines > would be helpful: > > # > You should omit any unlogged relation files (other than the 'init' fork) > as they will be reset to be empty upon recovery and backing them up will > simply increase your backup size (potentially significantly) and slow > down the restore process. Unlogged tables have an init fork (link to > storage-init.html) which is a file with the same filenode number as the > relation but with a suffix added of '_init' (link to > storage-file-layout.html). When an '_init' fork exists for a given > relation, the '_init' file should be included in the backup, but all > other files for that relation (the 'main' fork, which does not have a > suffix, and all other forks which exist other than the 'init' fork) > should be excluded from the backup. > # > > There's also no way for tar or cpio to directly validate that the copy > of pg_control that they copied is valid. Worse, on a restore, they'll > restore pg_control more-or-less whenever and then if the restore doesn't > complete for whatever reason, you might end up with a cluster that can > be started, run for a while, but be missing whole tables. While it was > only demonstrated relatively recently that the pg_control file can, in > fact, be invalid when read during a backup, it's a real issue that's > been around for, probably, forever, and there isn't really a good way to > address it today.
Re: Add a different archive_command example for Linux / Unix
Hello Stephen, For the missing fsync directory, in case of a system crash which I had in mind using this command, I thought that fsck will fixed the discrepancy. I support your proposal i.e. archive_command = 'backup_tool %p /mnt/server/archivedir/%f' as at least people will investigate what choices they have for backup tools. Regards Gilles - Mail original - > De: "Stephen Frost" > À: "gparc" > Cc: "gparc" , "pgsql-docs" > Envoyé: Jeudi 8 Février 2024 22:54:29 > Objet: Re: Add a different archive_command example for Linux / Unix > Greetings, > > * gp...@free.fr (gp...@free.fr) wrote: >> Thanks Stephen for your detailed reply and broad perspective. >> But I see the cp example command used **as is** most of the time. > > In those cases- how would changing it to be a dd command be helpful? > The directory still wouldn't be fsync'd and there's a very good chance > that the rest of the documentation isn't followed or understood either, > leading almost certainly to broken backup setups. This wouldn't be the > only issue in any case, to be sure. > > This comes back to my earlier suggestion that perhaps we should just > change it to something like: > > archive_command = 'backup_tool %p /mnt/server/archivedir/%f' > > and not talk about specific tools that exist but don't perform in the > manner we actually expect from an archive command that we're using. We > already make it pretty clear to anyone who knows the tools mentioned > that the 'example' command won't work, if you read everything under that > section. > > Alternatively, we could actually document the tools we're aware of that > do work and which do strive, at least, to try and be good backup tools > and good archive commands for PG. That would certainly be a service to > our users and might result in far fewer misconfigured systems using the > examples because they thought (despite the explicit note in our > documentation) that they were recommendations. > > Thanks, > > Stephen
Drop a partitioned table "side effect"
Hello, for a partitioned table, it should be mentioned in the documentation that currently the drop will also silently drop all the partitions underneath. By the way, it's a pity that "cascade" is not considered to be used in such a case. Regards Gilles
SQL command : ALTER DATABASE OWNER TO
Hello, for this "ALTER DATABASE" form, it should be mentioned that after execution of the command, the old database owner loses all his privileges on it (even connection) although it might still owns schemas or objects (tables, index,...) inside it. Thanks in advance to add this important precision. Regards Gilles
