public inbox for [email protected]
help / color / mirror / Atom feedPermission Denied on INSERT
5+ messages / 3 participants
[nested] [flat]
* Permission Denied on INSERT
@ 2025-06-27 15:11 Sam Stearns <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Sam Stearns @ 2025-06-27 15:11 UTC (permalink / raw)
To: pgsql-sql <[email protected]>; +Cc: Julie Mather <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Permission Denied on INSERT
@ 2025-06-27 15:26 Tom Lane <[email protected]>
parent: Sam Stearns <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2025-06-27 15:26 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: pgsql-sql <[email protected]>; Julie Mather <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
Sam Stearns <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Permission Denied on INSERT
@ 2025-06-27 16:59 Sam Stearns <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Sam Stearns @ 2025-06-27 16:59 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-sql <[email protected]>; Julie Mather <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
You are right, Tom. That was the problem. Thank you!
Sam
On Fri, Jun 27, 2025 at 8:26 AM Tom Lane <[email protected]> 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 <[email protected]> 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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Permission Denied on INSERT
@ 2025-06-30 03:01 DINESH NAIR <[email protected]>
parent: Sam Stearns <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: DINESH NAIR @ 2025-06-30 03:01 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; Tom Lane <[email protected]>; +Cc: pgsql-sql <[email protected]>; Julie Mather <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
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 protected]>
Sent: Friday, June 27, 2025 10:29 PM
To: Tom Lane <[email protected]>
Cc: pgsql-sql <[email protected]>; Julie Mather <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
Subject: Re: Permission Denied on INSERT
You don't often get email from [email protected]. 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 protected]<mailto:[email protected]>> wrote:
This Message Is From an External Sender
This message came from outside your organization.
Sam Stearns <[email protected]<mailto:[email protected]>> 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/AAQkAGQ3NGZiYWE4LWFkN2EtNGEzMi1iZjQ2LWY2YTdhZGRjMWU1YQAQAJy...]<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Permission Denied on INSERT
@ 2025-06-30 14:36 Sam Stearns <[email protected]>
parent: DINESH NAIR <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Sam Stearns @ 2025-06-30 14:36 UTC (permalink / raw)
To: DINESH NAIR <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-sql <[email protected]>; Julie Mather <[email protected]>; Peter Garza <[email protected]>; Henry Ashu <[email protected]>
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 protected]>
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 <[email protected]>
> *Sent:* Friday, June 27, 2025 10:29 PM
> *To:* Tom Lane <[email protected]>
> *Cc:* pgsql-sql <[email protected]>; Julie Mather <
> [email protected]>; Peter Garza <[email protected]>; Henry Ashu <
> [email protected]>
> *Subject:* Re: Permission Denied on INSERT
>
> You don't often get email from [email protected]. 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 protected]> wrote:
>
> *This Message Is From an External Sender*
> This message came from outside your organization.
>
>
> Sam Stearns <[email protected]> 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;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-06-30 14:36 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-27 15:11 Permission Denied on INSERT Sam Stearns <[email protected]>
2025-06-27 15:26 ` Tom Lane <[email protected]>
2025-06-27 16:59 ` Sam Stearns <[email protected]>
2025-06-30 03:01 ` DINESH NAIR <[email protected]>
2025-06-30 14:36 ` Sam Stearns <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox