public inbox for [email protected]  

help / color / mirror / Atom feed

From: DINESH  NAIR <[email protected]>
To: Sam Stearns <[email protected]>
To: Tom Lane <[email protected]>
Cc: pgsql-sql <[email protected]>
Cc: Julie Mather <[email protected]>
Cc: Peter Garza <[email protected]>
Cc: Henry Ashu <[email protected]>
Subject: Re: Permission Denied on INSERT
Date: Mon, 30 Jun 2025 03:01:57 +0000
Message-ID: <PN4P287MB43817566E64E530AC483B89B9C46A@PN4P287MB4381.INDP287.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CAN6TVjn4SAHC8_-4xN9qcWzYSUAZQPoqK-pqAXQdjHF-iKMbHA@mail.gmail.com>
References: <CAN6TVj=MxX_FgXzvZLxLHXyvzFRzpX2JFyug5mwOvteLoG4qLg@mail.gmail.com>
	<[email protected]>
	<CAN6TVjn4SAHC8_-4xN9qcWzYSUAZQPoqK-pqAXQdjHF-iKMbHA@mail.gmail.com>

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;

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Permission Denied on INSERT
  In-Reply-To: <PN4P287MB43817566E64E530AC483B89B9C46A@PN4P287MB4381.INDP287.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox