Re: Permission Denied on INSERT
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
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 |
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 SenderThis 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
В списке pgsql-sql по дате отправления: