public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected]
To: David G. Johnston <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: pgsql-docs <[email protected]>
Subject: Re: SQL command : ALTER DATABASE OWNER TO
Date: Wed, 24 Jan 2024 20:08:08 +0100 (CET)
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <2023185982.281851219.1646733038464.JavaMail.root@zimbra15-e2.priv.proxad.net>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAKFQuwbjczTu0H2Jcxkt=SnCx73Crgos0vDpd12KEBxM-Rrh3w@mail.gmail.com>
<[email protected]>
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 <new_database> 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" <[email protected]>
À: "David G. Johnston" <[email protected]>
Cc: "Laurenz Albe" <[email protected]>, "Daniel Gustafsson" <[email protected]>, "pgsql-docs" <[email protected]>
Envoyé: Mercredi 24 Janvier 2024 18:11:30
Objet: Re: SQL command : ALTER DATABASE OWNER TO
De: "David G. Johnston" <[email protected]>
À: "gparc" <[email protected]>
Cc: "Laurenz Albe" <[email protected]>, "Daniel Gustafsson" <[email protected]>, "pgsql-docs" <[email protected]>
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:[email protected] | [email protected] ] > 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
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL command : ALTER DATABASE OWNER TO
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox