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 1uVCQu-00FEJB-LD for pgsql-sql@arkaria.postgresql.org; Fri, 27 Jun 2025 17:00:12 +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 1uVCQs-003Lbi-5r for pgsql-sql@arkaria.postgresql.org; Fri, 27 Jun 2025 17:00:10 +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 1uVCQr-003LbZ-Qx for pgsql-sql@lists.postgresql.org; Fri, 27 Jun 2025 17:00:10 +0000 Received: from mx0a-0039f802.pphosted.com ([205.220.164.45]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uVCQm-004RHY-1O for pgsql-sql@lists.postgresql.org; Fri, 27 Jun 2025 17:00:06 +0000 Received: from pps.filterd (m0209981.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 55REMNcG019663 for ; Fri, 27 Jun 2025 10:00:01 -0700 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dat.com; h=cc :content-type:date:from:in-reply-to:message-id:mime-version :references:subject:to; s=ppdk230404; bh=JizpfNQpDqyxiwrcqT1Rq88 PBxq1vjT0pCZt/eyj6Y0=; b=Et2KUKs2IrCYHaASQ4uKmCmunZZ/0jkHUSMWwcC 2QMMfqFZm/OL5HdP56F1/BOD1aP9UPX8nAOqm7nJ9kTXQHXkQxIa3NfOhqTWiBgE ukHg+ZH6y6umhBKBMA8ssD0X4Vr22/lDxiQJEg1vGsvKWsZhyQSZXDYVRz6AAZmm fMeHLYInibyFpTSfnconKRMJVV39zjmnHwNSraM54awNtNZDHZEM1WSeaz2lppZt 6gCzYD1v9z609MuV6Kpf300nS5XQl3UoazMM9WipH9ilUGiJLM+6t7QX/rdmUA/V axacfmTRSjqB3RRHLG1BFDgYV676+vvnKfKG9ZLx/SFsV9w== Received: from mail-ed1-f72.google.com (mail-ed1-f72.google.com [209.85.208.72]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 47hnm3s18y-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Fri, 27 Jun 2025 10:00:01 -0700 (PDT) Received: by mail-ed1-f72.google.com with SMTP id 4fb4d7f45d1cf-60c6d568550so29262a12.2 for ; Fri, 27 Jun 2025 10:00:00 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1751043598; x=1751648398; 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=JizpfNQpDqyxiwrcqT1Rq88PBxq1vjT0pCZt/eyj6Y0=; b=uqCpIldcgwjG0qqBjHtJLNv8pIHwLStLm4uno4gHCtC+jegTFIXU2Atnuo6+Je1oc3 ouQLnxtF3C8Gygb9/fZRnWPOBMbktU1DULWIvj82B7oixrrU0iLA+UU5RxWuSU/2KylQ G4vFIdRNRoagNZoVkNMvC/xu6o4xIEZBwocS0ZgPaP/C0nBbGkLEnJBM3SrJrsMDZbdL urBx+p+g1au3931bCaNeam12vC/loQLnis3cjTtEYSLx0VrpE2xC2W8PBCeo46Vok07q +hwYzr2S+4Q/GrgK4WysGb8XmWkKZTPIQm7SS0rtnqNegiMMiXG1UO0wQ+MSNsWly5fr cGuQ== X-Gm-Message-State: AOJu0YwpFfcaxVxfY0BFSLPCcDysbmfoEayL/23LIZq8rkJMpw8IWixR hzv6phDgoP8+G/vDJeW+O+fuQjyIEkO9W6gKLwAO3WD4KmPOV63LZrg5pEiIFe0sxXo0V26kwjS Ihk+/sHTyKV3Npehhhxbv/A2kwzUY/YQdeaW/5jA+axXyEDGT/sIqwa9rd+j03l7eRexw1rmv5S ftYzk51eFLQgAGj+A5OVILvCOwtvODZSYKwMSN2DsUldkd4xj3bJJCviKd22cBxBTytCaGTMGvN Req X-Gm-Gg: ASbGncvKJmTGDmLSqCBDz/s2qYkgDJ6ZZVAyfx8YqPq2PZJ2AI3tKLeknRcZwr5OGoe CS4Cbx5/C0zXlR7fM5QzXV9ln7B6JbTAYDnd9F+1djnpI7CB4GI6w007/8szbfBtLGA7lQk842F Z2qw== X-Received: by 2002:a05:6402:350c:b0:5fe:17be:a93 with SMTP id 4fb4d7f45d1cf-60c88e3ece4mr3061932a12.29.1751043597848; Fri, 27 Jun 2025 09:59:57 -0700 (PDT) X-Google-Smtp-Source: AGHT+IG8I3I4t/G045Frgb9HJdvmx1bI3U1X8ARFqHbEohgpUNy9EOhgwGv/bX85jqBkWlHvxAYrxT4Ru0XV5KI0hG4= X-Received: by 2002:a05:6402:350c:b0:5fe:17be:a93 with SMTP id 4fb4d7f45d1cf-60c88e3ece4mr3061913a12.29.1751043597349; Fri, 27 Jun 2025 09:59:57 -0700 (PDT) MIME-Version: 1.0 References: <1650968.1751037993@sss.pgh.pa.us> In-Reply-To: <1650968.1751037993@sss.pgh.pa.us> From: Sam Stearns Date: Fri, 27 Jun 2025 09:59:45 -0700 X-Gm-Features: Ac12FXyEiDCNbvj71k0Ac9zlF06rNG8FUYOGzYdl9VyawszYDMgHUxeLSCcR9Fs Message-ID: Subject: Re: Permission Denied on INSERT To: Tom Lane Cc: pgsql-sql , Julie Mather , Peter Garza , Henry Ashu Content-Type: multipart/alternative; boundary="0000000000009b4cb10638909ba5" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1099,Hydra:6.1.7,FMLib:17.12.80.40 definitions=2025-06-27_05,2025-06-26_05,2025-03-28_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000009b4cb10638909ba5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable You are right, Tom. That was the problem. Thank you! Sam On Fri, Jun 27, 2025 at 8:26=E2=80=AFAM Tom Lane wrote: > Sam Stearns writes: > We're getting a permission > denied error on an INSERT and cannot figure out > why: > *[postgres@ > thiludbapql01 log]$ psql -U treg csbtestPassword for user > treg: psql (1= 6. > 6)Type "help" > ZjQcmQRYFpfptBannerStart > This Message Is From an External Sender > This message came from outside your organization. > > ZjQcmQRYFpfptBannerEnd > > Sam Stearns writes: > > We're getting a permission denied error on an INSERT and cannot figure = out > > 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# GRA= NT > > SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA treg TO > > treg;GRANTcsbtest=3D# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA tr= eg 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)csbtes= t-# > > 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 xcsbt= est=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 > > --=20 Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com --0000000000009b4cb10638909ba5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You are right, Tom.=C2=A0 That was the problem.=C2=A0 Than= k you!

Sam


On = Fri, Jun 27, 2025 at 8:26=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Sam Stearns <sam.=E2=80=8Astearns@=E2=80=8Adat.=E2=80=8Acom> writes: = > We're getting a permission denied error on an INSERT and cannot fi= gure out > why: > *[postgres@=E2=80=8Athiludbapql01 log]$ psql -U tre= g csbtestPassword for user > treg:=E2=80=8Apsql (16.=E2=80=8A6)Type &quo= t;help"
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.
=C2=A0
ZjQcmQRYFpfptBannerEnd
Sam Stearns <sam.stearns@dat.com> writes:
> We're getting a permission denied error on an INSERT and cannot fi=
gure out
> 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# GR=
ANT
> SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA treg TO
> treg;GRANTcsbtest=3D# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA t=
reg 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, cit=
y,
> postal_code, state, category, is_preferred, is_closed, is_parent)csbte=
st-#
> values (('734309'::int8),('S.668863.785512'),('668=
863'::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_office=
s" x WHERE
> "combined_office_id" OPERATOR(pg_catalog.=3D) $1 FOR KEY SHA=
RE OF xcsbtest=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


--

Samuel Stearns
Team Lead - Databas= e
c: 971 76= 2 6879 = | o: 97= 1 762 6879 | D= AT.com


--0000000000009b4cb10638909ba5--