Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nk2ln-00072o-DZ for pgsql-admin@arkaria.postgresql.org; Thu, 28 Apr 2022 11:57:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nk2lm-0003YG-66 for pgsql-admin@arkaria.postgresql.org; Thu, 28 Apr 2022 11:57:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nk2ll-0003Y6-PZ for pgsql-admin@lists.postgresql.org; Thu, 28 Apr 2022 11:57:13 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nk2lf-00056O-4h for pgsql-admin@lists.postgresql.org; Thu, 28 Apr 2022 11:57:12 +0000 Received: by mail-ej1-x62d.google.com with SMTP id y3so9020807ejo.12 for ; Thu, 28 Apr 2022 04:57:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=l0tZ905aVuCoQe8kH5Kd0dxe/2ky+ez9ddfqbr/DTP4=; b=bAjMxvlf5s1kkI0Xo9dTA9sE8l62gJDn8zCqkp4Zu22wL2Va68SrGxs7QLwmsQ00Vb wlDyoCn/QZt9lTIvm8mXEVfxhssH3mAKprCLqBk56NN9+eahUHyfKB7mP6tZ4u6jdukL ZXm3XLPcIXpvMJAmYe23xx246oEeiC4af40Ype///BkGyzlp/Hy1BL2HugBCfhD1MrkC UHV7Fb+tLJsoprrkIzQXG4KnltkVsJAgByV15y1mGn6mqmHT/RMlRwNMURQ+WhsFcBKd q0SAOFtUTX/FPlEVCAW0WUj6l4WVA1lUJmt82oXAYlWTj2K3PpupJnUyWvaeKV2cy2YU gteQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=l0tZ905aVuCoQe8kH5Kd0dxe/2ky+ez9ddfqbr/DTP4=; b=GFtiqxLsC1JhzszdSjAMkfDuxtBLMN6vCvZ3wMUmEOcbsuYwQlZHkK8DFW8QCA8FF+ ADDw2Yqub/H9eZalIiRzrmIAYecdm7YHJvl7VHzq01Z/aWzaZRDCHQZfXY4/UEAgq5je Ln2hC9wqVVmiceu2zPx6LPcXnhJk6k2PY2wk9wjM3KP3mb0Ct+Ar6J9gvaAuycs/K7Uf 5Arr26jlIm+GYfiwdEz7fORd8w7E8RpnZs7xSX1CYypkjuenRIbMWDi/eta6lXl6DBYW A2xl9PknvbwFVmblIbuIg1csQxTpOo0DaHq1qSYBy1XOGi27eHZTSMbKE4m6pf7pu/ss Bqug== X-Gm-Message-State: AOAM5329K8Pfnui4n+acsfsKIQ4O9XBrYsWP3owL22soukAovQVNghu3 nOQyHKt5Y4EAt/Nluc4B9oVcYT0Toe8J/zWy3Mtm4rco X-Google-Smtp-Source: ABdhPJwgG4UhrBtvTAPzN48pPbvJPWwKdABYy6r4mOTlKiwOtIL4nC3Cy+JCNVQY4rp36qhz0jx7T412rtJeWnQpCbw= X-Received: by 2002:a17:907:8a11:b0:6df:db0a:e5ab with SMTP id sc17-20020a1709078a1100b006dfdb0ae5abmr31162879ejc.528.1651147025158; Thu, 28 Apr 2022 04:57:05 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:640c:1d16:b0:15a:6ae0:bda4 with HTTP; Thu, 28 Apr 2022 04:57:04 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Thu, 28 Apr 2022 04:57:04 -0700 Message-ID: Subject: Re: SELECT has_database_privilege('user01', 'db01', 'connect'); To: "William Sescu (Suva)" Cc: "pgsql-admin@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000e815b205ddb59fa4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e815b205ddb59fa4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, April 28, 2022, William Sescu (Suva) wrote: > Hello % > > I am kinda confused, if I miss something. I have two questions: > > * A new created user should not have the connect privilege per default, i= f > the user is not the owner of the db, right? > * The function has_database_privilege should return false, if I revoke th= e > connect privilege, right? > > (postgres@[local]:55042)[postgres]> CREATE USER user01 ENCRYPTED PASSWORD > 'user01'; > CREATE ROLE > (postgres@[local]:55042)[postgres]> CREATE DATABASE db01 WITH OWNER =3D > postgres; > CREATE DATABASE > (postgres@[local]:55042)[postgres]> SELECT has_database_privilege('user01= ', > 'db01', 'connect'); > has_database_privilege > ------------------------ > t <=3D have expected false here > (1 row) Not how it works, see default privileges, namely for =E2=80=9Cpublic=E2=80= =9D. > (postgres@[local]:55042)[postgres]> REVOKE CONNECT ON DATABASE db01 FROM > user01; > REVOKE > (postgres@[local]:55042)[postgres]> SELECT has_database_privilege('user01= ', > 'db01', 'connect'); > has_database_privilege > ------------------------ > t <=3D have expected false here even more after = the > revoke statement > (1 row) > > Or do I have some misunderstanding in regards of how it should work? > The privilege being found is inherited, you revoked a non-existent grant which doesn=E2=80=99t do anything. You need to revoke the privilege being inherited, from =E2=80=9Cpublic=E2=80=9D. David J. --000000000000e815b205ddb59fa4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, April 28, 2022, William Sescu (Suva) <william.sescu@suva.ch> wrote:
Hello %

I am kinda confused, if I miss something. I have two questions:

* A new created user should not have the connect privilege per default, if = the user is not the owner of the db, right?
* The function has_database_privilege should return false, if I revoke the = connect privilege, right?

(postgres@[local]:55042)[postgres]> CREATE USER user01 ENCRYPTED PA= SSWORD 'user01';
CREATE ROLE
(postgres@[local]:55042)[postgres]> CREATE DATABASE db01 WITH OWNER= =3D postgres;
CREATE DATABASE
(postgres@[local]:55042)[postgres]> SELECT has_database_privilege(&= #39;user01', 'db01', 'connect');
=C2=A0has_database_privilege
------------------------
=C2=A0t=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<=3D have expected false here
(1 row)

Not how it works, see default privi= leges, namely for =E2=80=9Cpublic=E2=80=9D.
=C2=A0
(postgres@[local]:55042)[postgres]> REVOKE CONNECT ON DATABASE db01= FROM user01;
REVOKE
(postgres@[local]:55042)[postgres]> SELECT has_database_privilege(&= #39;user01', 'db01', 'connect');
=C2=A0has_database_privilege
------------------------
=C2=A0t=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<=3D have expected false here even more after th= e revoke statement
(1 row)

Or do I have some misunderstanding in regards of how it should work?

The privilege being found is inherited, yo= u revoked a non-existent grant which doesn=E2=80=99t do anything.=C2=A0 You= need to revoke the privilege being inherited, from =E2=80=9Cpublic=E2=80= =9D.

David J.

--000000000000e815b205ddb59fa4--