Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSgSt-00FFDy-2z for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 16:51:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rSgSr-006UF3-TA for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 16:51:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSgSr-006UER-EI for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 16:51:01 +0000 Received: from mail-ot1-x32d.google.com ([2607:f8b0:4864:20::32d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rSgSk-0039qJ-JP for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 16:51:00 +0000 Received: by mail-ot1-x32d.google.com with SMTP id 46e09a7af769-6ddca59e336so2516310a34.0 for ; Wed, 24 Jan 2024 08:50:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1706115053; x=1706719853; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=aq/1cAd/kikXcikjCWssPMQ/mgKDvLBcAqYnbaBNeHo=; b=gEWAkETM4/rUkP8/OELQjRCpN38Whp1rMTHKCtEtpEOT2Q6FuKpTvJAgycEB/s37VW OhJzHQczE5RD6cwRYAi2A22LPHZssIm/KhlK7kkOnyF6SjA0YoDH5jZXn671pscrg8Fd bETctgijm45rx/3JBbGYNumsOiu7ZHIDMpGyMp1/jxvMioYS6otSeAf0gT9YegXtx6/Y feVXahCT+qKGHEvdHlxsDMX2Wsg5udgIGBmguykvQt/31aOa2mH/M8d22gu3i/Jw1NGi 4IKSRlXgHJzBt9aoUW2jnykirwWd/YT4CK9DbVEQLfBYJFHiI9XuPV+/NpykWPicuIBp JCcQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1706115053; x=1706719853; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=aq/1cAd/kikXcikjCWssPMQ/mgKDvLBcAqYnbaBNeHo=; b=oltBc2Zc2iOKFujURCvotzIRRuryHwg9t65PeNKVR8kf9MT1BWq6kYasE9EJa9VuOh JcDQzrFZuYjJYYlZFLbtMfHI2yjCNBoNVTxazoBla/53bFHlKroGpXhBmV7Jg8Zd5LOy R+/inJdRA7kCDb0db5EaA7UHAsIlyPq66gF+Aac7teyF3Wc2jDprABWt7AaQ/uOFSHR2 1/OtQ3tX43/91HYlzJtpx7LCunudPGyidJJWOX/ekkRJwiOy0/yH0gEwMZmgnoycBlXD Eo3RJteu89ebnH968sku7YQ+DA9L9GKjd824HfmNfkUzoP9/EFMnoQpXA8PbpKXHRdFy qfIg== X-Gm-Message-State: AOJu0YwSiLFVho7VQa/5BEQN6WFSS3fRx5JlLQ+j728g4BtTn6AK+Era hBmSI3K3XXAk/O1TMB2LhxdkdngK1Cg1l5Osli6dAGkP3HToSrjIIksll+0s7Hw0sLiRKz4IVv2 zFVypyyGuYhbUbGIembQBQwxli48= X-Google-Smtp-Source: AGHT+IGbubNPs6jwlngMpSKUPQYkpfr2ZZOaUzlghOoHxFyHPg0gVhqS9TF/yJjTZDlLtAgVQ8nTuXi75n6RxHfV5hM= X-Received: by 2002:a05:6830:16c2:b0:6dc:1fc:8902 with SMTP id l2-20020a05683016c200b006dc01fc8902mr542941otr.36.1706115053542; Wed, 24 Jan 2024 08:50:53 -0800 (PST) MIME-Version: 1.0 References: <2023185982.281851219.1646733038464.JavaMail.root@zimbra15-e2.priv.proxad.net> <1712096587.208766558.1706090901073.JavaMail.zimbra@free.fr> <1327973565.212682013.1706107220766.JavaMail.zimbra@free.fr> <176777248.214045315.1706113384094.JavaMail.zimbra@free.fr> In-Reply-To: <176777248.214045315.1706113384094.JavaMail.zimbra@free.fr> From: "David G. Johnston" Date: Wed, 24 Jan 2024 09:50:17 -0700 Message-ID: Subject: Re: SQL command : ALTER DATABASE OWNER TO To: gparc@free.fr Cc: Laurenz Albe , Daniel Gustafsson , pgsql-docs Content-Type: multipart/alternative; boundary="000000000000b69bda060fb3ddc2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b69bda060fb3ddc2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jan 24, 2024 at 9:23=E2=80=AFAM 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 (=3DTc/tst) > [postgres@PGDEV14] postgres=3D# create user tst password 'tst'; > CREATE ROLE > [postgres@PGDEV14] postgres=3D# create database tst owner =3D tst; > CREATE DATABASE > 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. [postgres@PGDEV14] postgres=3D# grant all on database tst to tst; > GRANT > [postgres@PGDEV14] postgres=3D# \l+ tst > Liste des bases de donn=C3= =A9es > Nom | Propri=C3=A9taire | Encodage | Collationnement | Type caract. | Dr= oits > d'acc=C3=A8s | Taille | Tablespace | Description > > -----+--------------+----------+-----------------+--------------+--------= --------+---------+------------+------------- > tst | tst | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =3DTc/t= st > +| 9809 kB | pg_default | > | | | | | > tst=3DCTc/tst | | | > (1 ligne) > > What are you trying to demonstrate here? > [postgres@PGDEV14] tst=3D# \dn+ tst > Liste des sch=C3=A9mas > Nom | Propri=C3=A9taire | Droits d'acc=C3=A8s | Description > -----+--------------+----------------+------------- > tst | tst | | > (1 ligne) > > David J. --000000000000b69bda060fb3ddc2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jan 24, 2024 at 9:23=E2=80=AFAM <gparc@free.fr> wrote:-
[postgres] $ psql
psql (14.10)


You really should add commentary, e= specially since you never demonstrated the tst role (I advise picking diffe= rent names for all of the objects in the future) being unable to login.=C2= =A0 Which they should be able to since public is shown to have "c"= ; connect privileges (=3DTc/tst)

=C2=A0
[postgres@PGDEV14] postgres=3D# create user tst password 'tst';
CREATE ROLE
[postgres@PGDEV14] postgres=3D# create database tst owner =3D tst;
CREATE DATABASE

This=C2=A0next command is= pointless, it is a no-op, as soon as you made them owner of the tst databa= se they already had all privileges to it,=C2=A0granted by the same user tha= t created=C2=A0the database.=C2=A0 And only it, that command is not recursi= ng through the database into schemas and tables and adding more permissions= .=C2=A0 That isn't how this all works, a database is an object.=C2=A0 W= hile it is also a concept that encompasses the entire schema within it the = permissions system only cares about the first definition.

[postgres@PGDEV14] postgres=3D# grant all on database tst to tst;
GRANT
[postgres@PGDEV14] postgres=3D# \l+ tst
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 Liste des bases de donn=C3=A9es
=C2=A0Nom | Propri=C3=A9taire | Encodage | Collationnement | Type caract. |= Droits d'acc=C3=A8s | Taille=C2=A0 | Tablespace | Description
-----+--------------+----------+-----------------+--------------+----------= ------+---------+------------+-------------
=C2=A0tst | tst=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | UTF8=C2=A0 =C2=A0 =C2= =A0| fr_FR.UTF-8=C2=A0 =C2=A0 =C2=A0| fr_FR.UTF-8=C2=A0 | =3DTc/tst=C2=A0 = =C2=A0 =C2=A0 =C2=A0+| 9809 kB | pg_default |
=C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | tst= =3DCTc/tst=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
(1 ligne)


What are you trying to demo= nstrate here?


[postgres@PGDEV14] tst=3D# \dn+ tst
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Liste des sch= =C3=A9mas
=C2=A0Nom | Propri=C3=A9taire | Droits d'acc=C3=A8s | Description
-----+--------------+----------------+-------------
=C2=A0tst | tst=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
(1 ligne)


David J.

=
--000000000000b69bda060fb3ddc2--