Horton

Thread

  1. Permission Denied on INSERT

    Sam Stearns <sam.stearns@dat.com> — 2025-06-27T15:11:00Z

    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>

  2. Re: Permission Denied on INSERT

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-27T15:26:33Z

    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" 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=#*

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

  3. Re: Permission Denied on INSERT

    Sam Stearns <sam.stearns@dat.com> — 2025-06-27T16:59:45Z

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

Sam


On Fri, Jun 27, 2025 at 8:26 AM Tom Lane <tgl@sss.pgh.pa.us> 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
>
> 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" 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=#*
>
> 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

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

  4. Re: Permission Denied on INSERT

    DINESH NAIR <dinesh_nair@iitmpravartak.net> — 2025-06-30T03:01:57Z

    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 <sam.stearns@dat.com>
Sent: Friday, June 27, 2025 10:29 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
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

You don't often get email from sam.stearns@dat.com. 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 <tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>> wrote:
This Message Is From an External Sender
This message came from outside your organization.


Sam Stearns <sam.stearns@dat.com<mailto: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" 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=#*

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

[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>

  5. Re: Permission Denied on INSERT

    Sam Stearns <sam.stearns@dat.com> — 2025-06-30T14:36:40Z

    Thanks, Dinesh!

We resolved this by changing the owner of the table.

Sam


On Sun, Jun 29, 2025 at 8:02 PM DINESH NAIR <Dinesh_Nair@iitmpravartak.net>
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.
>
> 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 <sam.stearns@dat.com>
> *Sent:* Friday, June 27, 2025 10:29 PM
> *To:* Tom Lane <tgl@sss.pgh.pa.us>
> *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
>
> You don't often get email from sam.stearns@dat.com. 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 <tgl@sss.pgh.pa.us> wrote:
>
> *This Message Is From an External Sender*
> This message came from outside your organization.
>
>
> 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" 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=#*
>
> 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
> <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>
>
>

-- 

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>