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 1qqx0s-001q5C-If for pgsql-general@arkaria.postgresql.org; Thu, 12 Oct 2023 14:50:10 +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 1qqx0q-0081L6-7k for pgsql-general@arkaria.postgresql.org; Thu, 12 Oct 2023 14:50:08 +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 1qqx0p-0081Ky-T2 for pgsql-general@lists.postgresql.org; Thu, 12 Oct 2023 14:50:08 +0000 Received: from mail-oo1-xc2c.google.com ([2607:f8b0:4864:20::c2c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qqx0k-0008us-0S for pgsql-general@lists.postgresql.org; Thu, 12 Oct 2023 14:50:07 +0000 Received: by mail-oo1-xc2c.google.com with SMTP id 006d021491bc7-57de9237410so611861eaf.0 for ; Thu, 12 Oct 2023 07:50:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697122201; x=1697727001; 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=fCUTwefh7p6x/p2CCo2SiamxY7lFbQutTFjTuviSI3U=; b=hwdMRK//1NzcnAeDRGjhtzB3cm4IaCLA1BDR7IqxoRDhvM5AngVfGADBB6Gfy4ySzs zUgkUYJrb01hJ1AI8USJq+av85Wu3oP/wmkrbAqHNRKt5ezCXe9nRI+Xix62kYxis2cO DEe3X4vohW69O60vbEVZiSGcCb1wtQxPyKeDhFftoWoLnYkNFTfBWIOc2soeg3ZKofvs HgGNnJ6U/sthL4NRifsQ3aPDOtPywW+wBsGuMrwfOmMPm+hrU//5VAtosqXUAFjTBcpW qb3aSJ5cvvZxz0f8qZM+LoPinzSSNoFn043VaQWJ+MR47uPpgla4fHvnkYCxCTqEfMVN aUVA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697122201; x=1697727001; 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=fCUTwefh7p6x/p2CCo2SiamxY7lFbQutTFjTuviSI3U=; b=X7ZgYLIQuA5DsIW4pOPp19lGedj+L5IYEFmsAyn+FgrfeTv80lbvUNR2YBH7w+Whlw qqOEV4NXwKSK8aj9RR+Lj65yH3FArohhvTa0TPdXj80sxSN+J0jk3U7Ih0A/phnnhi63 l4hFakUeytxemj4p1pqrKxG36tHhtx98sO6UJ9XVUb8fEc3mKWV9PkIskA6LjadANjzr xPrQUw+rGEp+/NoinAuio2jJRqurpduoHzR1X1mI9uVpq/lmOlFBKIZ/5l8aujDR28KO l3+kkxn8BxA7m5x5jnAkjJRKukBLg07CkBlVY0GnrWqsY5KvXR3oJmcJFjznFHsI5ULQ Gs8Q== X-Gm-Message-State: AOJu0YxXxug4ABRhsRuvDb7Xb67zLOyLK10cO1rMpaHUSOxelicPwT2d r/J8XMluCmZkIPJIJrQzvll1ueo2/6BQ5svs0Mc= X-Google-Smtp-Source: AGHT+IF80H0/JX5S5P0OTrMXMAR8VPPRaInRXAfyRx0cRLAN5JfjUlSKB8ICXw3CNLAupuU+m/MoDBrCyndI/R5PCM8= X-Received: by 2002:a4a:9b94:0:b0:57b:5693:5ecb with SMTP id x20-20020a4a9b94000000b0057b56935ecbmr23493046ooj.2.1697122201341; Thu, 12 Oct 2023 07:50:01 -0700 (PDT) MIME-Version: 1.0 References: <13Y.H1Z.5kU5AvmUH7E.1b9}Lo@seznam.cz> In-Reply-To: <13Y.H1Z.5kU5AvmUH7E.1b9}Lo@seznam.cz> From: Dominique Devienne Date: Thu, 12 Oct 2023 16:49:50 +0200 Message-ID: Subject: Re: List users privileges for whole cluster/all databases in the cluster To: =?UTF-8?Q?Jana_Mihalidesov=C3=A1?= Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000f394ab0607860d02" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f394ab0607860d02 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Oct 12, 2023 at 3:42=E2=80=AFPM Jana Mihalidesov=C3=A1 wrote: > I try to find out some view, select or something what show me the > privileges for the user across the whole postgresql cluster. The > username/user is global for whole cluster not individual database, so I > would like to know the privileges for the user in all databases in the > cluster using one view, select. > I know how to list user's privileges in the individual database, but for > all databases... > As Tom already mentioned, this is per-DB. So you have to aggregate the privileges yourself, i.e. connect to any DB, lookup all databases the user can connect to, then connect to each DB in turn (possibly in parallel using several concurrent connections) to get the privs in that DB. This presumes the user doing the lookup can connect to at least the DBs that user's has access to. The query below should get you started on the first part. Just move the can-CONNECT test to the WHERE-clause instead of the SELECT-clause, and change session_user. --DD SELECT datname, datdba::regrole::text as owner, has_database_privilege(session_user, datname, 'CONNECT') as can_connect, has_database_privilege(session_user, datname, 'CREATE') as can_create FROM pg_database WHERE datistemplate =3D false --000000000000f394ab0607860d02 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Oct 12, 2023 at 3:42=E2=80=AFPM J= ana Mihalidesov=C3=A1 <Mihi.Jana@= seznam.cz> wrote:
I try to find out some view, selec= t or something what show me the privileges for the user across the whole po= stgresql cluster. The username/user is global for whole cluster not individ= ual database, so I would like to know the privileges for the user in all da= tabases in the cluster using one view, select.
I know how to list user&#= 39;s privileges in the individual database, but for all databases...
<= /blockquote>

As Tom already mentioned, this is per-DB. S= o you have to aggregate the privileges yourself,
i.e. connect to = any DB, lookup all databases the user can connect to, then connect to each = DB
in turn (possibly in parallel using several concurrent connect= ions) to get the privs in that DB.
This presumes the user doing t= he lookup can connect to at least the DBs that user's has access to.

The query below should get you started on the first = part. Just move the can-CONNECT test
to the WHERE-clause instead = of the SELECT-clause, and change session_user. --DD

SELECT datname, datdba::regrole::text as owner,
=C2=A0 =C2=A0 =C2=A0 = =C2=A0has_database_privilege(session_user, datname, 'CONNECT') as c= an_connect,
=C2=A0 =C2=A0 =C2=A0 =C2=A0has_database_privilege(session_us= er, datname, 'CREATE') =C2=A0as can_create
=C2=A0 FROM pg_databa= se
=C2=A0WHERE datistemplate =3D false
--000000000000f394ab0607860d02--