Subject: Permission Denied on INSERT

From: Sam Stearns <[email-masked]>
Date: Fri, 27 Jun 2025 08:11:00 -0700

Howdy,

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=# show search_path;
search_path---------------------------- csbtfsprd, interface, treg(1
row)csbtest=# grant usage on schema treg to treg;GRANTcsbtest=# GRANT
SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA treg TO
treg;GRANTcsbtest=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA treg TO
treg;GRANTcsbtest=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA treg TO
treg;GRANTcsbtest=# 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.=) $1 FOR KEY SHARE OF xcsbtest=#*

Any help would be greatly appreciated, please.

Thank you,

Sam

-- 

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com

<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>

---

From: Tom Lane <[email-masked]>
Date: Fri, 27 Jun 2025 11:26:33 -0400

> "combined_office_id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF xcsbtest=#*

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

---

From: Sam Stearns <[email-masked]>
Date: Fri, 27 Jun 2025 09:59:45 -0700

You are right, Tom.  That was the problem.  Thank you!

Sam

On Fri, Jun 27, 2025 at 8:26 AM Tom Lane <[email-masked]> wrote:

> Sam Stearns <sam. stearns@ dat. com> 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"
> ZjQcmQRYFpfptBannerStart
> This Message Is From an External Sender
> This message came from outside your organization.
>
> ZjQcmQRYFpfptBannerEnd
>

-- 

Samuel Stearns
Team Lead - Database

---

From: "DINESH NAIR" <[email-masked]>
Date: Mon, 30 Jun 2025 03:01:57 +0000

Hi Sam,

Problem statement:  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 . You'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 privilege 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 <[email-masked]>
Sent: Friday, June 27, 2025 10:29 PM
To: Tom Lane <[email-masked]>
Cc: pgsql-sql <[email-masked]>; Julie Mather <[email-masked]>; Peter Garza <[email-masked]>; Henry Ashu <[email-masked]>
Subject: Re: Permission Denied on INSERT

You don't often get email from [email-masked]. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>
Caution: This email was sent from an external source. Please verify the sender’s 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 AM Tom Lane <[email-masked]<mailto:[email-masked]>> wrote:
This Message Is From an External Sender
This message came from outside your organization.

                        regards, tom lane

--

Samuel Stearns
Team Lead - Database

[https://outlook.office.com/mail/inbox/id/AAQkAGQ3NGZiYWE4LWFkN2EtNGEzMi1iZjQ2LWY2YTdhZGRjMWU1YQAQAJyFnJqD7UJPk3uygehmZLY%3D?nativeVersion=1.2025.617.100]<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>

---

From: Sam Stearns <[email-masked]>
Date: Mon, 30 Jun 2025 07:36:40 -0700

Thanks, Dinesh!

We resolved this by changing the owner of the table.

Sam

On Sun, Jun 29, 2025 at 8:02 PM DINESH NAIR <[email-masked]>
wrote:

> Hi Sam, Problem statement: 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 . 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 executed
> as the owner of the table . You'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 privilege
> 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.
>
> ------------------------------
> *From:* Sam Stearns <[email-masked]>
> *Sent:* Friday, June 27, 2025 10:29 PM
> *To:* Tom Lane <[email-masked]>
> *Cc:* pgsql-sql <[email-masked]>; Julie Mather <
> [email-masked]>; Peter Garza <[email-masked]>; Henry Ashu <
> [email-masked]>
> *Subject:* Re: Permission Denied on INSERT
>
> You don't often get email from [email-masked]. Learn why this is
> important <https://aka.ms/LearnAboutSenderIdentification>
> Caution: This email was sent from an external source. Please verify the
> sender’s identity before clicking links or opening attachments.
> *This Message Is From an External Sender*

-- 

Samuel Stearns
Team Lead - Database