From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sam Stearns <sam.stearns@dat.com>
cc: pgsql-sql <pgsql-sql@lists.postgresql.org>,
        Julie Mather <julie.mather@dat.com>,
 Peter Garza <peter.garza@dat.com>,
        Henry Ashu <henry.ashu@dat.com>
Subject: Re: Permission Denied on INSERT
In-reply-to: 
 <CAN6TVj=MxX_FgXzvZLxLHXyvzFRzpX2JFyug5mwOvteLoG4qLg@mail.gmail.com>
References: 
 <CAN6TVj=MxX_FgXzvZLxLHXyvzFRzpX2JFyug5mwOvteLoG4qLg@mail.gmail.com>
Comments: In-reply-to Sam Stearns <sam.stearns@dat.com>
	message dated "Fri, 27 Jun 2025 08:11:00 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1650967.1751037993.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 27 Jun 2025 11:26:33 -0400
Message-ID: <1650968.1751037993@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1650968.1751037993%40sss.pgh.pa.us>
Precedence: bulk

Sam Stearns <sam.stearns@dat.com> writes:
> We're getting a permission denied error on an INSERT and cannot figure o=
ut
> why:
> *[postgres@thiludbapql01 log]$ psql -U treg csbtestPassword for user
> treg:psql (16.6)Type "help" for help.csbtest=3D# show search_path;
> search_path---------------------------- csbtfsprd, interface, treg(1
> row)csbtest=3D# grant usage on schema treg to treg;GRANTcsbtest=3D# GRAN=
T
> SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA treg TO
> treg;GRANTcsbtest=3D# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA tre=
g TO
> treg;GRANTcsbtest=3D# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA treg TO
> treg;GRANTcsbtest=3D# insert into treg.cd_combined_office_mappings
> (combined_office_id, tcsi_office_id, sb2_account_id, account_name, city,
> postal_code, state, category, is_preferred, is_closed, is_parent)csbtest=
-#
> values (('734309'::int8),('S.668863.785512'),('668863'::int4),('Testing
> Something'),('Beaverton'),('97008'),('OR'),('Carrier'),('Y'),('N'),('Y')=
);ERROR:
>  permission denied for schema tregLINE 1: SELECT 1 FROM ONLY
> "treg"."cd_combined_offices" x WHERE "com...
>  ^QUERY:  SELECT 1 FROM ONLY "treg"."cd_combined_offices" x WHERE
> "combined_office_id" OPERATOR(pg_catalog.=3D) $1 FOR KEY SHARE OF xcsbte=
st=3D#*

The error appears to be getting thrown in a generated
foreign-key-constraint-enforcement query.  From memory,
those are executed as the owner of the table (I think
owner of the referencing table, in this case).  You've
apparently not granted usage on treg to that role.

			regards, tom lane