public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Sam Stearns <[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: Fri, 27 Jun 2025 11:26:33 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAN6TVj=MxX_FgXzvZLxLHXyvzFRzpX2JFyug5mwOvteLoG4qLg@mail.gmail.com>
References: <CAN6TVj=MxX_FgXzvZLxLHXyvzFRzpX2JFyug5mwOvteLoG4qLg@mail.gmail.com>

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

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]
  Subject: Re: Permission Denied on INSERT
  In-Reply-To: <[email protected]>

* 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