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 1sx9Sx-008FxF-Ix for pgsql-general@arkaria.postgresql.org; Sat, 05 Oct 2024 18:25:20 +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 1sx9Sv-003GJU-DT for pgsql-general@arkaria.postgresql.org; Sat, 05 Oct 2024 18:25:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sx9Su-003GIZ-Gn for pgsql-general@lists.postgresql.org; Sat, 05 Oct 2024 18:25:17 +0000 Received: from mta-p8.oit.umn.edu ([134.84.196.208]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sx9Sk-002jsq-1f for pgsql-general@lists.postgresql.org; Sat, 05 Oct 2024 18:25:15 +0000 Received: from localhost (unknown [127.0.0.1]) by mta-p8.oit.umn.edu (Postfix) with ESMTP id 4XLYjn3RH6z9vBt8 for ; Sat, 5 Oct 2024 18:25:01 +0000 (UTC) X-Virus-Scanned: amavisd-new at umn.edu Received: from mta-p8.oit.umn.edu ([127.0.0.1]) by localhost (mta-p8.oit.umn.edu [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id qszlHy96tGcg for ; Sat, 5 Oct 2024 13:25:01 -0500 (CDT) Received: from mail-pj1-f72.google.com (mail-pj1-f72.google.com [209.85.216.72]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by mta-p8.oit.umn.edu (Postfix) with ESMTPS id 4XLYjn0lfGz9vBt7 for ; Sat, 5 Oct 2024 13:25:00 -0500 (CDT) DMARC-Filter: OpenDMARC Filter v1.3.2 mta-p8.oit.umn.edu 4XLYjn0lfGz9vBt7 DKIM-Filter: OpenDKIM Filter v2.11.0 mta-p8.oit.umn.edu 4XLYjn0lfGz9vBt7 Received: by mail-pj1-f72.google.com with SMTP id 98e67ed59e1d1-2e08a1e1110so269367a91.3 for ; Sat, 05 Oct 2024 11:25:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=d.umn.edu; s=google; t=1728152700; x=1728757500; 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=psXH5ndLMuO0Fp/h9TvbaBRmTIFK4EEOUPEpZ9KF6ck=; b=XjA16FIr0kmwkDiPZat6+Jy11rwzsdLZiVkGSNtuKUGiFQNqupr4DvWyH5vS2c52O2 s31WR5s6tHWtlR/jBAY5x+9oBUKvvveEX5rqmcoKm6aUHKNY/tlGYd0yHYaSWn4dMYGa C+yncTn6qh5EEREsu+eRAevHADQNPsTRSqOCwRRCJkwk8l3qnHEXjsbyFYplewtVP8AH N41sEmGgDRBhxYfeum6iueSQSga4iBt0YJMcaHtBTjKVd3Lcr/SgY2+UBUqVtGkgIn5R 3+O9r9bIWHzQyw5lEz3X6iC5/d57e8xyKmII5TR6/ZaXKUvn0XVV+PjCE70e+dnJrmTs MXhA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1728152700; x=1728757500; 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=psXH5ndLMuO0Fp/h9TvbaBRmTIFK4EEOUPEpZ9KF6ck=; b=svbSoe/OtzeV7m0xBHf1HziWOlei+8aiH2eVzXZSRvkMP50QQNRzTJcS0DSqqx8Okp OuIz6aohcNTxwwt2A4JBRL67fwPaNzrgGelucTs6YVi7f5hJg51uOE4rb9DnKhE4tScO WN6twyMCYUesI+Bo2We5zWpu9DsspBzk7csOK6mEhUfD/8fiTsCxIOKlf+e5AeSvcY08 1C/poiSGnt3BDcEmnqEFzu+xYl3pz6xNteB4SmsTe+cQVl2GHw1S1u92yoQM+aPkndd4 jyqeZHhNN+ar0ULm3Sxmp8aoAN2fMno3m67i2kv9cA9sP1cjcXe0a59C/U7vAObehNSC X6qg== X-Forwarded-Encrypted: i=1; AJvYcCU/IhYa5teWQFehPFHBWffE6c8XuDgvS7l3tBHJt60jPVKrVeCElvBFFGzv1J+iJ7Rmsm3/gzTs0yRBpXYm@lists.postgresql.org X-Gm-Message-State: AOJu0YzHoM72k0nZbiAyKOHxxdIYOygtB65zVm3NwqkndccFrouV0aNL Gsf4yBqmUErs1IrLf3Yp/crm22Mv2MLwr9T8Cr/LBKdqSMZBapepsuOVVOValGZZwXW2SxKo02z AHkjAMYVNjvuL6/Zd10ynPuv6yw+77xiCM1ow5EEkD+KGA5mQB/wowg6uN8oaU/wdrSo+yuRveK cQ7JAq/oV4M/crrymJIWn0+mSfn98qMLzieVV07Hv8jtB9KkQ= X-Received: by 2002:a17:90b:1e49:b0:2db:87f0:2173 with SMTP id 98e67ed59e1d1-2e1e63b3f73mr3356965a91.9.1728152700005; Sat, 05 Oct 2024 11:25:00 -0700 (PDT) X-Google-Smtp-Source: AGHT+IFr7FcSFb1NOROAUo/Bbn/cwLFuMeWq72wTtF8pyyWH4tfYe5ZbpLoo/SDyOKKgeR1f4c50FRfGgrH3Gx2MdMc= X-Received: by 2002:a17:90b:1e49:b0:2db:87f0:2173 with SMTP id 98e67ed59e1d1-2e1e63b3f73mr3356958a91.9.1728152699644; Sat, 05 Oct 2024 11:24:59 -0700 (PDT) MIME-Version: 1.0 References: <0c089041-d4eb-4bf4-bfcb-6451224190c9@aklaver.com> In-Reply-To: <0c089041-d4eb-4bf4-bfcb-6451224190c9@aklaver.com> From: Matt Zagrabelny Date: Sat, 5 Oct 2024 13:24:47 -0500 Message-ID: Subject: Re: grant connect to all databases To: Adrian Klaver Cc: "David G. Johnston" , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000c7ca110623bee792" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c7ca110623bee792 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, Oct 5, 2024 at 11:26=E2=80=AFAM Adrian Klaver wrote: > On 10/5/24 09:04, Matt Zagrabelny wrote: > > > > > > On Sat, Oct 5, 2024 at 10:27=E2=80=AFAM Adrian Klaver > > wrote: > > > > On 10/5/24 07:13, Matt Zagrabelny wrote: > > > Hi David (and others), > > > > > > Thanks for the info about Public. > > > > > > I should expound on my original email. > > > > > > In our dev and test environments our admins (alice, bob, eve) ar= e > > > superusers. In production environments we'd like the admins to b= e > > read-only. > > > > What are the REVOKE and GRANT commands you use to achieve that? > > > > > > GRANT alice TO pg_read_all_data; > > Does alice have existing GRANTs? > Nope. I create the role (via puppet) and then add the GRANT pg_read_all_data TO (via puppet). > > I would try: > > GRANT pg_read_all_data TO alice; > > As example: > > psql -d test -U postgres > > List of role grants > Role name | Member of | Options | Grantor > ------------+----------------------+--------------+---------- > aklaver | app_admin | INHERIT, SET | postgres > aklaver | production | INHERIT, SET | postgres > dd_admin | dd_owner | ADMIN, SET | postgres > dd_user | dd_admin | INHERIT, SET | postgres > pg_monitor | pg_read_all_settings | INHERIT, SET | postgres > pg_monitor | pg_read_all_stats | INHERIT, SET | postgres > pg_monitor | pg_stat_scan_tables | INHERIT, SET | postgres > postgres | dd_owner | INHERIT, SET | postgres > > > grant pg_read_all_data to adrian; > GRANT ROLE > > test=3D# \drgS > What is \drgS? I don't believe I have that. > List of role grants > Role name | Member of | Options | Grantor > ------------+----------------------+--------------+---------- > adrian | pg_read_all_data | INHERIT, SET | postgres > aklaver | app_admin | INHERIT, SET | postgres > aklaver | production | INHERIT, SET | postgres > dd_admin | dd_owner | ADMIN, SET | postgres > dd_user | dd_admin | INHERIT, SET | postgres > pg_monitor | pg_read_all_settings | INHERIT, SET | postgres > pg_monitor | pg_read_all_stats | INHERIT, SET | postgres > pg_monitor | pg_stat_scan_tables | INHERIT, SET | postgres > postgres | dd_owner | INHERIT, SET | postgres > > \dt csv_test > List of relations > Schema | Name | Type | Owner > --------+----------+-------+---------- > public | csv_test | table | postgres > > test=3D# \q > > psql -d test -U adrian > > test=3D> select * from csv_test ; > id | val > ----+------ > 1 | test > 2 | dog > 3 | cat > 4 | test > 5 | fish > > That looks good. Here is the output of puppet's create role: drop role alice; The next puppet run and I get: 'CREATE ROLE "alice" ENCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER CONNECTION LIMIT -1' GRANT pg_read_all_data TO alice; test=3D# \du List of roles Role name | Attributes | Member of ----------------------+----------------------------------------------------= --------+-------------------- alice | | {pg_read_all_data} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} ...but I still cannot connect: $ psql -d test -U alice psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: permission denied for database "test" DETAIL: User does not have CONNECT privilege. Thanks for the help! -m --000000000000c7ca110623bee792 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Sat, Oct 5, 2024 at 11:26=E2=80=AF= AM Adrian Klaver <adrian.kl= aver@aklaver.com> wrote:
On 10/5/24 09:04, Matt Zagrabelny wrote:
>
>
> On Sat, Oct 5, 2024 at 10:27=E2=80=AFAM Adrian Klaver <adrian.klaver@aklaver.co= m
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0On 10/5/24 07:13, Matt Zagrabelny wrote:
>=C2=A0 =C2=A0 =C2=A0 > Hi David (and others),
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Thanks for the info about Public.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > I should expound on my original email.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > In our dev and test environments our admins (= alice, bob, eve) are
>=C2=A0 =C2=A0 =C2=A0 > superusers. In production environments we'= ;d like the admins to be
>=C2=A0 =C2=A0 =C2=A0read-only.
>
>=C2=A0 =C2=A0 =C2=A0What are the REVOKE and GRANT commands you use to a= chieve that?
>
>
> GRANT alice TO pg_read_all_data;

Does alice have existing GRANTs?

Nope. = I create the role (via puppet) and then add the GRANT pg_read_all_data TO (= via puppet).
=C2=A0

I would try:

GRANT pg_read_all_data TO alice;

As example:

psql -d test -U postgres

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 List of role grants
=C2=A0 Role name=C2=A0 |=C2=A0 =C2=A0 =C2=A0 Member of=C2=A0 =C2=A0 =C2=A0 = =C2=A0|=C2=A0 =C2=A0Options=C2=A0 =C2=A0 | Grantor
------------+----------------------+--------------+----------
=C2=A0 aklaver=C2=A0 =C2=A0 | app_admin=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | INHERIT, SET | postgres
=C2=A0 aklaver=C2=A0 =C2=A0 | production=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| INHERIT, SET | postgres
=C2=A0 dd_admin=C2=A0 =C2=A0| dd_owner=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| ADMIN, SET=C2=A0 =C2=A0| postgres
=C2=A0 dd_user=C2=A0 =C2=A0 | dd_admin=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| INHERIT, SET | postgres
=C2=A0 pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
=C2=A0 pg_monitor | pg_read_all_stats=C2=A0 =C2=A0 | INHERIT, SET | postgre= s
=C2=A0 pg_monitor | pg_stat_scan_tables=C2=A0 | INHERIT, SET | postgres
=C2=A0 postgres=C2=A0 =C2=A0| dd_owner=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| INHERIT, SET | postgres


grant pg_read_all_data to adrian;
GRANT ROLE

test=3D# \drgS

What is \drgS? I don'= ;t believe I have that.
=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 List of role grants
=C2=A0 Role name=C2=A0 |=C2=A0 =C2=A0 =C2=A0 Member of=C2=A0 =C2=A0 =C2=A0 = =C2=A0|=C2=A0 =C2=A0Options=C2=A0 =C2=A0 | Grantor
------------+----------------------+--------------+----------
=C2=A0 adrian=C2=A0 =C2=A0 =C2=A0| pg_read_all_data=C2=A0 =C2=A0 =C2=A0| IN= HERIT, SET | postgres
=C2=A0 aklaver=C2=A0 =C2=A0 | app_admin=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | INHERIT, SET | postgres
=C2=A0 aklaver=C2=A0 =C2=A0 | production=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| INHERIT, SET | postgres
=C2=A0 dd_admin=C2=A0 =C2=A0| dd_owner=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| ADMIN, SET=C2=A0 =C2=A0| postgres
=C2=A0 dd_user=C2=A0 =C2=A0 | dd_admin=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| INHERIT, SET | postgres
=C2=A0 pg_monitor | pg_read_all_settings | INHERIT, SET | postgres
=C2=A0 pg_monitor | pg_read_all_stats=C2=A0 =C2=A0 | INHERIT, SET | postgre= s
=C2=A0 pg_monitor | pg_stat_scan_tables=C2=A0 | INHERIT, SET | postgres
=C2=A0 postgres=C2=A0 =C2=A0| dd_owner=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| INHERIT, SET | postgres

=C2=A0 \dt csv_test
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0List of relations
=C2=A0 Schema |=C2=A0 =C2=A0Name=C2=A0 =C2=A0| Type=C2=A0 |=C2=A0 Owner
--------+----------+-------+----------
=C2=A0 public | csv_test | table | postgres

test=3D# \q

psql -d test -U adrian

test=3D> select * from csv_test ;
=C2=A0 id | val
----+------
=C2=A0 =C2=A01 | test
=C2=A0 =C2=A02 | dog
=C2=A0 =C2=A03 | cat
=C2=A0 =C2=A04 | test
=C2=A0 =C2=A05 | fish


That looks good.

<= div>Here is the output of puppet's create role:

drop role alice;

The next puppet run and I g= et:

=C2=A0'CREATE ROLE "alice" E= NCRYPTED PASSWORD \'$NEWPGPASSWD\' LOGIN NOCREATEROLE NOCREATEDB NO= SUPERUSER =C2=A0CONNECTION LIMIT -1'
GRANT pg_read_all_data T= O alice;

test=3D# \du
=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=A0List of roles=
=C2=A0 =C2=A0 =C2=A0 Role name =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 Attribut= es =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 Member of
----------------------+---------= ---------------------------------------------------+--------------------=C2=A0alice =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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| {pg_read_all_d= ata}
=C2=A0postgres =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Superuse= r, Create role, Create DB, Replication, Bypass RLS | {}

<= /div>

=C2=A0...but I still cannot connect:
$ psql -d test -U alice
psql: error: connection to server on= socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: =C2=A0= permission denied for database "test"
DETAIL: =C2=A0User does = not have CONNECT privilege.

Thanks for the help!

-m
--000000000000c7ca110623bee792--