MIME-Version: 1.0
References: 
 <CAGRrpzbhG3YaR6bDV4z6=cSND3+RVx0dEN9f_PiSVLE_DCiNzA@mail.gmail.com>
In-Reply-To: 
 <CAGRrpzbhG3YaR6bDV4z6=cSND3+RVx0dEN9f_PiSVLE_DCiNzA@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sun, 29 Mar 2026 20:34:56 -0700
Message-ID: 
 <CAKFQuwaiP+kYLCtUh_5Hdd7XKUHHH_Y5JAvb-0x2JQevJevVeA@mail.gmail.com>
Subject: Re: [PATCH] Report column-level error when lacking privilege
To: Steve Chavez <steve@supabase.io>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000fc85ca064e358a20"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwaiP%2BkYLCtUh_5Hdd7XKUHHH_Y5JAvb-0x2JQevJevVeA%40mail.gmail.com>
Precedence: bulk

--000000000000fc85ca064e358a20
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, Mar 29, 2026 at 6:07=E2=80=AFPM Steve Chavez <steve@supabase.io> wr=
ote:

> When a role `xx` has `grant select (name) on items to xx;`, a generic
> table-level error is given:
>
> select * from items;
> ERROR:  permission denied for table items
>
> With this patch, we now give:
>
> select * from items;
> ERROR:  permission denied for column "id" of relation "items"
>
>
Not too fond of picking one column as a representative for the error
message.  Better to say something like:

ERROR:  permission denied for column subset of table items

Haven't looked but it should be doable to run a query for a given relation
and role and report for each column whether a grant is available or not;
which would be the one-stop shop for figuring out which columns at least
don't have permissions granted.  The user would still have to know which
ones their query is actually using.

David J.

--000000000000fc85ca064e358a20
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_defa=
ult" style=3D"font-family:arial,helvetica,sans-serif"><span style=3D"font-f=
amily:Arial,Helvetica,sans-serif">On Sun, Mar 29, 2026 at 6:07=E2=80=AFPM S=
teve Chavez &lt;<a href=3D"mailto:steve@supabase.io" target=3D"_blank">stev=
e@supabase.io</a>&gt; wrote:</span></div></div><div class=3D"gmail_quote"><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>When=
 a role `xx` has `grant select (name) on items to xx;`, a generic table-lev=
el error is given:</div><div><br>select * from items;<br>ERROR: =C2=A0permi=
ssion denied for table items<br><br>With this patch, we now give:<br><br>se=
lect * from items;<br>ERROR: =C2=A0permission denied for column &quot;id&qu=
ot; of relation &quot;items&quot;<br></div><br></div></blockquote><div><br>=
</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,san=
s-serif">Not too fond of picking one column as a representative for the err=
or message.=C2=A0 Better to say something like:</div><div class=3D"gmail_de=
fault" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div clas=
s=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">ERROR:=
=C2=A0 permission denied for column subset of table items</div><div class=
=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></d=
iv><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-s=
erif">Haven&#39;t looked but it should be doable to run a query for a given=
 relation and role and report for each column whether a grant is available =
or not; which would be the one-stop shop for figuring out which columns at =
least don&#39;t have permissions granted.=C2=A0 The user would still have t=
o know which ones their query is actually using.</div><div class=3D"gmail_d=
efault" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div cla=
ss=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">David=
 J.</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,=
sans-serif"><br></div></div></div>
</div>

--000000000000fc85ca064e358a20--