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.96) (envelope-from ) id 1vT7zU-00Dhtb-1g for pgsql-admin@arkaria.postgresql.org; Wed, 10 Dec 2025 00:23:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vT7zR-008lhe-0b for pgsql-admin@arkaria.postgresql.org; Wed, 10 Dec 2025 00:23:33 +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.96) (envelope-from ) id 1vT7zQ-008lhT-2B for pgsql-admin@lists.postgresql.org; Wed, 10 Dec 2025 00:23:33 +0000 Received: from mail-oa1-x35.google.com ([2001:4860:4864:20::35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vT7zO-0042e1-22 for pgsql-admin@lists.postgresql.org; Wed, 10 Dec 2025 00:23:31 +0000 Received: by mail-oa1-x35.google.com with SMTP id 586e51a60fabf-3ec96ee3dabso4795850fac.1 for ; Tue, 09 Dec 2025 16:23:30 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1765326210; x=1765931010; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=Yu8DcKkqJosgwAWI7ye6iwQ3RT2DjXsR6wGNzH79IYY=; b=gLkBosHpcG/SpRnRFPdToHcoR0EConaTVjF/74tVkCGcZIJvxmb+GVwYLpi7Q7aMrP 29UF10Q0aJIv1t44VbFPt2Jfe8y8oAPXgwKx9BTpKWWrMBrRIUYKXsJqA72OtMSigtmz /IZNt2WjYrrDfBdI16jAPbQIwQDggnKZQ690HTicBFTt/wsNtn3oWpXg+vC1kEKMeU+N OPNAZw29UHegJx9cDODtp4+VCiJMYecOYtrdejARW6a6kvSVb43LFRvuhfBmjXfYLLbT zJMnR0zFeRbyAlHkN9dF0/T9sEbImhCHxwH2eA9FbFSPWGREluZKEuVcuWfam6LXkfaf lBIA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1765326210; x=1765931010; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Yu8DcKkqJosgwAWI7ye6iwQ3RT2DjXsR6wGNzH79IYY=; b=O7QEbNK1EuI7fBefSfXqswPu93m/ahjYjen2ALTJDSzH4Z0DK9u0vMGbbqi4Qxi0e4 jPaFsnIhTX90QCCDkv8G7PrQ8N7U5hft0IS+Bh9oxnPLV+x70bp3KDEGVONsy5HDgkFB GjF+xBVQ2zkfL4RMLU7BPn+IMMQt9QCzE8aTTW/Zp357aTA/ZFso1oHaNXjddONzxtaF 8lWkTayjgNO6lquk8BpQ6FA2723OqK+XPYrD9AXefDCSMh5Jnec91Wkw1oglBEuFslAg 2quD7v6x8ShfDoK7XYuDjAVPY+uCjH2zDqjtDiM6sFIiXo7259re4UFLjThQv2gdxQ16 lH9A== X-Gm-Message-State: AOJu0Yyo9sNIWLqjdpkDxHr9z9kSFF44MqHFZCn3EAXRmr9E6oy0tejS SPU6IzH+jKz/yvusf3KMfCeUfBnHZFTJQMGIJ2vQI0UNkQXnyvEhIitiWVodGnkOYe43U5ZS0TQ Bn3OhXY1E4VFHlY1oNv3Wp/+dfvBzw3sLhoTd X-Gm-Gg: ASbGncv5ouXpY8F67IBAJ1ZZW6he9zPS3pojXdsXDGitbWX88DNEEAFUoJgQq98Ih2v +EHJ3r43sDlsblhnGhprnO4I73WYHfrx8C/hXrUPx0vKJzf1a1MLtcHi9bkIWXv2b9GRad8NeE9 XDLz+n5Dsbuqr15A7jPQ51lN1NkIUwEqwJWKc6McirMT3+rdqXD1VwHK+3FtmYhJbIxsEdiyzfp tALFq1A3aLJGLeCp45jf0U+jU2gw147nt5m2C4xT5e9bTm0v9qBYFzp3AvBxPBWztLHqPXA3FxZ SrQdNKU= X-Google-Smtp-Source: AGHT+IE3025QbdXmqfLTD0yoVSL7uTsqaObqKSRTacqmarkgqzUBCltEWyAZWoFqP+Ipx4O9BG0qVLtKE+bjuv+YGsQ= X-Received: by 2002:a05:6870:478d:b0:3d3:7135:105a with SMTP id 586e51a60fabf-3f5bdc7cca6mr498477fac.19.1765326209637; Tue, 09 Dec 2025 16:23:29 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 9 Dec 2025 19:23:18 -0500 X-Gm-Features: AQt7F2osu6EUvvTyIbmoIOQm1uwf6aefGWWhBJSovuf4PuGrAk3hPuiTYT46mho Message-ID: Subject: Re: database specific pg_read_all_data / pg_write_all_data To: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000a3610c06458e090b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a3610c06458e090b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Dec 9, 2025 at 6:21=E2=80=AFPM richard coleman wrote: > Ron, > > That wouldn't come even close to what pg_read_all_data grants. > A role assigned to pg_read_all_data automatically has the ability to read > everything, in every schema that exists now or in the future. > > The old way, your suggestion, means that you have to keep rerunning that > command everytime someone creates a schema, > Yes, for every new schema. Do schemata get created that often? > creates a table, creates a view, recreates a table, recreates a view, etc= . > for all eternity. > I don't think so: https://www.postgresql.org/docs/17/sql-alterdefaultprivileges.html "ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future." You *would* have to do an initial "GRANT ALL ON ... TO bar" but this can be scripted so you pass the user name as a parameter and loops through all schemata. Is it as convenient as per-database pg_read_all_data? No. But the inconvenience *can* be mitigated. Not only that, you have to tailor the command to each new schema, etc. > > This makes shared privs much more streamlined and removes the chance that > a user will forget to assign privs to objects that they create. > > I hope that helps make it clearer. > rik. > > > > > On Tue, Dec 9, 2025 at 5:46=E2=80=AFPM Ron Johnson > wrote: > >> On Tue, Dec 9, 2025 at 4:13=E2=80=AFPM richard coleman < >> rcoleman.ascentgl@gmail.com> wrote: >> >>> In PostgreSQL 16+ the built in roles such as pg_read_all_data >>> and pg_write_all_data are a welcome addition to permission setting in >>> PostgreSQL. >>> >>> Unfortunately they appear to be server-wide roles. >>> >>> Woud it be possible to have roles like these that are database specific= ? >>> >>> If there are 100 databases on a server, it would be extremely helpful t= o >>> be able to do something like: >>> >>> *grant *pg_read_all_data* on database *foo* to *user_role*;* >>> >>> Otherwise these roles are unusable from a practical stand point on >>> servers with multiple unrelated databases. >>> >> >> How about >> ALTER DEFAULT PRIVILEGES IN SCHEMA foo1, foo2, foo3, ... GRANT SELECT ON >> ALL TABLE TO bar; >> >> -- >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000a3610c06458e090b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Dec 9, 2025 at 6:21=E2=80=AFPM ri= chard coleman <rcoleman.a= scentgl@gmail.com> wrote:
Ron,

That wouldn't come even close to what pg_re= ad_all_data grants.
A role assigned to pg_read_all_data automatic= ally has the ability to read everything, in every schema that exists now or= in the future.
=C2=A0
The old way, your su= ggestion, means that you have to keep rerunning that command everytime some= one creates a schema,

Yes, for= every new schema.=C2=A0 Do schemata get created that often?
=C2= =A0
creates a table, creates a view, recreates a table, recreates a view,= etc. for all eternity.=C2=A0

I= don't think so:
"ALTER DEFAULT PRIVILEGES= =C2=A0allows you to set the privileges that will be applied to objects crea= ted in the future."

You would= =C2=A0have to do an initial "GRANT ALL ON ... TO bar" but this ca= n be scripted so you pass the user name as a parameter and loops through al= l schemata.

Is it as convenient as per-database pg= _read_all_data?=C2=A0 No.=C2=A0 But the inconvenience can=C2=A0be mi= tigated.

Not only that, you have to tailor the command to= each new schema, etc.

This makes shared privs muc= h more streamlined and removes the chance that a user will forget to assign= privs to objects that they create.

I hope that he= lps make it clearer.
rik.




On Tue, Dec 9, 2025 at 5:46=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.c= om> wrote:
On Tue, Dec 9, 2025 at 4:13=E2=80=AFPM = richard coleman <rcoleman.ascentgl@gmail.com> wrote:
In PostgreSQL 16+ the built in roles such as=C2=A0pg_read_all_data and= =C2=A0pg_write_all_data are a welcome addition to permission setting in Pos= tgreSQL.

Unfortunately they appear to be server-wide rol= es.

Woud it be possible to have roles like these t= hat are database specific?

If there are 100 databa= ses on a server, it would be extremely helpful to be able to do something l= ike:

grant pg_read_all_data on database = foo to user_role;

Otherwise thes= e roles are unusable from a practical stand point on servers with multiple = unrelated databases.

How about=C2=A0
ALTER=C2=A0DEFAULT PRIVILEGES IN SCHEMA fo= o1, foo2, foo3, ... GRANT SELECT ON ALL TABLE TO bar;

<= span class=3D"gmail_signature_prefix">--
Death to <Redacted>, and butter= sauce.
Don't boil me, I'm still alive.
<Redact= ed> lobster!


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--000000000000a3610c06458e090b--