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 1uWFd5-00Ax2d-8M for pgsql-sql@arkaria.postgresql.org; Mon, 30 Jun 2025 14:37:07 +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 1uWFd3-0006Ga-4K for pgsql-sql@arkaria.postgresql.org; Mon, 30 Jun 2025 14:37:05 +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.94.2) (envelope-from ) id 1uWFd2-0006F2-HZ for pgsql-sql@lists.postgresql.org; Mon, 30 Jun 2025 14:37:05 +0000 Received: from mx0a-0039f802.pphosted.com ([205.220.164.45]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uWFcx-004mHQ-1G for pgsql-sql@lists.postgresql.org; Mon, 30 Jun 2025 14:37:01 +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 55UE826E012547 for ; Mon, 30 Jun 2025 07:36:57 -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=Wz7KvVFCsQD5DeYgv2/kG6g tSEHtwcEn8Ie+fxaaAWo=; b=iqe6Cm46En2J93CKuol6f3PZpjNedlbqG0k635W n9vzkKpHEQPr6pX3+g9XeJ1qxH+fVWHtSbI3F0SggrdHapgbDdJcnUwqTa+fDCvm gjEtipuCMRCKcAWo/a2Q8sc/Id1UGkJDRld1tMrFyLR0o7JxVLqJ0Sq5D6o4G+rW 4WsM4b9i8JfRsWqtbtkuFPsb0rTLc9CyOjigE3KVzva0d2oARnPWy+gD2xMMR6Z9 RcQXYCg7Llji2OeVsQh4flcnFgR1oP0A2Ro4yZ+RFrY7Vk/rayXfmKE41s1pNrBA JFzjhuBVjf10YN364u1Ox9LcDEu35GX+W+Vy0tBOH4PnmSg== Received: from mail-ed1-f69.google.com (mail-ed1-f69.google.com [209.85.208.69]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 47jdtxaba3-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Mon, 30 Jun 2025 07:36:57 -0700 (PDT) Received: by mail-ed1-f69.google.com with SMTP id 4fb4d7f45d1cf-60c3a8f370aso3799168a12.3 for ; Mon, 30 Jun 2025 07:36:57 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1751294214; x=1751899014; 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=Wz7KvVFCsQD5DeYgv2/kG6gtSEHtwcEn8Ie+fxaaAWo=; b=fdvCVDHHGUWB8RgIFwYqdei6L8hbBgYM9jakMXXojOoUIiC97Wg6RrFRPP6gp3rzWm ysoZ3ldCVVNCHsOceiUXgmbtEduyHMcAG2NPz1wMwudtAO01l9M6BqImK9ObK8TD6tgv +eVvsrxBZ0jI6lURyGvzEU6ZN8PpwJaILB6zpdyXRaDD5uzUS0DaS2iCre0nvUh4Hgxh 9cWV2zuvY8nkvUdHQB+kO181yEMxZZOvVUQppHwXEMNZJ082tCV4nuClGG5L9PRPP1Yp 2VHXEQxYZ4OwSz64/yp/Mp1BMqvjdS3KmCcYCJcPgiwA3C+1gJ4XPPalkrknsMxyVvlh oHWQ== X-Forwarded-Encrypted: i=1; AJvYcCX991gdA/8sJjraPUifIMhRnijqyM5D7TmZqPvz03MdaDcrtS+vi5bQxoBlYGYYWap1LGYpcZGiEoA=@lists.postgresql.org X-Gm-Message-State: AOJu0YxQGIuwejkUxEn5I1c1o5OmVd7nUSt1a0dLkcs4l77N+TiyCM2k 5eej/VvbVzfrwxA5kuvO7veiWMQjRxwGaGUIsI9FFBzRpxZTCQRo2BDxW8yCBk1dYfQm3ZXo9YU f8ZpDwKFQD24SQRimfqk9MKtpaqw3BY0Z7di0DKljEHdyN0i6UTtOy/CMDaVqmxT0mqo3tNd9wA xgowLFsJcxmtwsq16LIhF59z2ydkupwWUQykfyIXwCNgV9gzX+3f/7eL+Xm6Z0K2JWTbe/ X-Gm-Gg: ASbGncsc1hIT53KrdjW/H0KJuimbcONi7gyt665Hv/tLmJWkcF9mgZvN5RUUxJVaHK/ 5dPsjxzgC4tWe/N/Dbb1vtnglCcV32cXa3iOzjjB/hpU5kL1xMPyZXTGzyJVycTjOrPsUoqejFX HV+A== X-Received: by 2002:a05:6402:455a:b0:607:f513:4800 with SMTP id 4fb4d7f45d1cf-60c88b56830mr11145952a12.10.1751294213642; Mon, 30 Jun 2025 07:36:53 -0700 (PDT) X-Google-Smtp-Source: AGHT+IGdTDsXf1VTUZuKxQSVO1pGCFGimnchI9504sfocRq8e7y3Gl2G1+OCCmJcx0vzcNG/nQPNPS56ldLB+oRPZns= X-Received: by 2002:a05:6402:455a:b0:607:f513:4800 with SMTP id 4fb4d7f45d1cf-60c88b56830mr11145930a12.10.1751294213103; Mon, 30 Jun 2025 07:36:53 -0700 (PDT) MIME-Version: 1.0 References: <1650968.1751037993@sss.pgh.pa.us> In-Reply-To: From: Sam Stearns Date: Mon, 30 Jun 2025 07:36:40 -0700 X-Gm-Features: Ac12FXz1uKkfoISLNXxY3iR-8uwSC2vhdb971n3WXw14aFn-ghCwZLXVOQP6ibY Message-ID: Subject: Re: Permission Denied on INSERT To: DINESH NAIR Cc: Tom Lane , pgsql-sql , Julie Mather , Peter Garza , Henry Ashu Content-Type: multipart/alternative; boundary="000000000000783b020638caf516" 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-30_04,2025-06-27_01,2025-03-28_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000783b020638caf516 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks, Dinesh! We resolved this by changing the owner of the table. Sam On Sun, Jun 29, 2025 at 8:02=E2=80=AFPM DINESH NAIR wrote: > Hi Sam, Problem statement: The error appears to be getting thrown in a > generated foreign-key-constraint-enforcement query. From memory, those ar= e > executed as the owner of the table . You've apparently not granted usage = on > treg to that role. > ZjQcmQRYFpfptBannerStart > This Message Is From an Untrusted Sender > You have not previously corresponded with this sender. > > ZjQcmQRYFpfptBannerEnd > Hi Sam, > > Problem statement: The error appears to be getting thrown in a generated > foreign-key-constraint-enforcement query. From memory, those are execute= d > as the owner of the table . You've apparently not granted usage on treg t= o > that role. > > Solution : 1. In this case Role-Based Access Control (RBAC) user roles, > the role assigned to your user might not have the necessary INSERT privil= ege > assigned to the tables , or your user might not be correctly assigned to > the role that *does* have the necessary insert privilege. > > 2. Schema, tables are granted necessary insert privileges . Sequences , > triggers(if any) have necessary privileges. > > Possible reasons getting privilege issue during record > > 3. Table-Specific Constraints/Status : a . If the table is having read > access > b. triggers and procedures not having > c. Due to row level security or policies > d. Separate log files are maintained and user not having necessary > permissions. > > > > Thanks > > Dinesh Nair > > > > > ------------------------------ > *From:* Sam Stearns > *Sent:* Friday, June 27, 2025 10:29 PM > *To:* Tom Lane > *Cc:* pgsql-sql ; Julie Mather < > julie.mather@dat.com>; Peter Garza ; Henry Ashu < > henry.ashu@dat.com> > *Subject:* Re: Permission Denied on INSERT > > You don't often get email from sam.stearns@dat.com. Learn why this is > important > Caution: This email was sent from an external source. Please verify the > sender=E2=80=99s identity before clicking links or opening attachments. > You are right, Tom. That was the problem. Thank you! > > Sam > > > On Fri, Jun 27, 2025 at 8:26=E2=80=AFAM Tom Lane wrot= e: > > *This Message Is From an External Sender* > This message came from outside your organization. > > > 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 > > > > -- > > Samuel Stearns > Team Lead - Database > c: 971 762 6879 | o: 971 762 6879 | DAT.com > > > --=20 Samuel Stearns Team Lead - Database c: 971 762 6879 | o: 971 762 6879 | DAT.com --000000000000783b020638caf516 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks, Dinesh!

We resolved this by cha= nging=C2=A0the owner of the table.

Sam
<= br>

On Sun, Jun 29, 2025 at 8:02=E2=80=AFPM DINE= SH NAIR <Dinesh_Nair@i= itmpravartak.net> wrote:
Hi Sam, Problem statement: The error appears to be getting thrown in a gene= rated foreign-key-constraint-enforcement query. From memory, those are exec= uted as the owner of the table . You've apparently not granted usage on= treg to that role.=E2=80=8A
ZjQcmQRYFpfptBannerStart
This Message Is From an Untrusted Sender
You have not previously corresponded with this sender.
=C2=A0
ZjQcmQRYFpfptBannerEnd
Hi Sam,

Problem statement:=C2=A0 The error appears to be getting thrown in a genera= ted foreign-key-constraint-enforcement query.=C2=A0 From memory, those are = executed as the owner of the table .=C2=A0You've apparently not granted= usage on treg to that role.

Solution : 1. In this case Role-Based Access Control (RBAC) user roles, the= role assigned to your user might not have the necessary INSERT=C2=A0privilege assigned to the tables , or your user mi= ght not be correctly assigned to the role that does=C2=A0have the necessary insert privilege.

2. Schema, tables are granted necessary insert privileges . Sequences , tri= ggers(if any) have necessary privileges.

Possible reasons getting privilege issue during record=C2=A0=C2=A0

3. Table-Specific Constraints/Status : a . If the table is having read acce= ss
=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2= =80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80= =82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82= =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82b. triggers = and procedures not having=C2=A0=C2=A0
=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2= =80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80= =82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82= =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82c. = Due to row level security or policies=C2=A0
=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2= =80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80= =82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82= =E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82=E2=80=82d. Separate = log files are maintained and user not having necessary permissions.



Thanks

Dinesh Nair




From:=C2=A0Sam Stearns <sam.stearns@dat.com>
Sent:=C2=A0Friday, June 27, 2025 10:29 PM
To:=C2=A0Tom Lane <tgl@sss.pgh.pa.us>
Cc:=C2=A0pgsql-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:=C2=A0Re: Permission Denied on INSERT

You don't often get email from sam.stearns@dat.com. Learn why this is important
Caution: This email was sent from a= n external source. Please verify the sender=E2=80=99s identity before click= ing links or opening attachments.
You are right, Tom.=C2=A0 That was the problem= .=C2=A0 Thank you!

Sam


On Fri, Jun 27, 2025 at 8:26=E2=80=AFAM Tom La= ne <tgl@sss.pgh.pa.us> wrote:
This Message Is From an External Sender
This message came from outside your organization.
=C2=A0
Sam Stearns=
 <sam.stearns@dat.com<=
/a>> 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:
> =C2=A0permission denied for schema tregLINE 1: SELECT 1 FROM ONLY
> "treg"."cd_combined_offices" x WHERE "com...
> =C2=A0^QUERY: =C2=A0SELECT 1 FROM ONLY "treg"."cd_combi=
ned_offices" 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.=C2=A0 From memory,
those are executed as the owner of the table (I think
owner of the referencing table, in this case).=C2=A0 You've
apparently not granted usage on treg to that role.

			regards, tom lane


--

Samuel Stearns
Team Lead - Database
c: 971 762 6879 |=C2=A0o: 971 762 6879 |=C2=A0DAT.com



--

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


--000000000000783b020638caf516--