Subject: INSERT Permission Denied

From: Sam Stearns <[email-masked]>
Date: Thu, 25 Sep 2025 09:49:24 -0700

Howdy,

I have an INSERT:

INSERT INTO treg.cd_combined_office_mappings (
    tcsi_office_id, combined_office_id, sb2_account_id, postal_code,
category,
    account_name, city, state, is_preferred, is_closed, is_parent
) VALUES
('TCSI001', 10001, 2001, '90210', 'RETAIL', 'Acme Corp', 'Los Angeles',
'CA', 'Y', 'N', 'Y'),
('TCSI002', 10001, 2002, '10001', 'WHOLESALE', 'Beta LLC', 'New York',
'NY', 'N', 'N', 'N'),
('TCSI003', 10002, 2003, '60601', 'RETAIL', 'Gamma Inc', 'Chicago', 'IL',
'Y', 'N', 'N'),
('TCSI004', 10003, 2004, '77001', 'SERVICE', 'Delta Co', 'Houston', 'TX',
'N', 'Y', 'N'),
('TCSI005', 10003, 2005, '33101', 'RETAIL', 'Epsilon Ltd', 'Miami', 'FL',
'Y', 'N', 'Y');

that's failing with permission denied on the schema:

ERROR:  permission denied for schema treg
LINE 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 x

Table structure:

csbstage=# \d+ treg.cd_combined_office_mappings
                                             Table
"treg.cd_combined_office_mappings"
       Column       |         Type          | Collation | Nullable |
Default | Storage  | Compression | Stats target | Description
--------------------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 tcsi_office_id     | character varying(15) |           | not null |
  | extended |             |              |
 combined_office_id | numeric(38,0)         |           | not null |
  | main     |             |              |
 sb2_account_id     | integer               |           | not null |
  | plain    |             |              |
 postal_code        | character varying(15) |           | not null |
  | extended |             |              |
 category           | character varying(20) |           | not null |
  | extended |             |              |
 account_name       | character varying(50) |           | not null |
  | extended |             |              |
 city               | character varying(50) |           | not null |
  | extended |             |              |
 state              | character(2)          |           | not null |
  | extended |             |              |
 is_preferred       | character(1)          |           | not null |
  | extended |             |              |
 is_closed          | character(1)          |           | not null |
  | extended |             |              |
 is_parent          | character(1)          |           | not null |
  | extended |             |              |
Indexes:
    "cd_combined_office_mappings_pkey" PRIMARY KEY, btree (tcsi_office_id)
    "idx_combined_mappings_1" btree (combined_office_id)
    "idx_combined_mappings_2" btree (sb2_account_id)
Foreign-key constraints:
    "cd_combined_mappings" FOREIGN KEY (combined_office_id) REFERENCES
treg.cd_combined_offices(combined_office_id) NOT VALID
Publications:
    "csbstage_postgres_to_oracle"
Replica Identity: FULL
Access method: heap

The user has all required privileges from what I can tell.  The postgres
user even gets the same failure when running the INSERT.  Would anyone be
able to give any advice here, please?

Thanks,

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>

---

From: Tom Lane <[email-masked]>
Date: Thu, 25 Sep 2025 12:58:09 -0400

> LINE 1: SELECT 1 FROM ONLY "treg"."cd_combined_offices" x WHERE "com...

You need to grant USAGE permission on that schema.  It's roughly
comparable to search privilege on a directory in most OSes.

			regards, tom lane

---

From: Sam Stearns <[email-masked]>
Date: Thu, 25 Sep 2025 10:06:18 -0700

USAGE has already been granted.

On Thu, Sep 25, 2025 at 9:58 AM Tom Lane <[email-masked]> wrote:

> Sam Stearns <sam. stearns@ dat. com> writes: > I have an INSERT: > that's
> failing with permission denied on the schema: > ERROR: permission denied
> for schema treg > LINE 1: SELECT 1 FROM ONLY "treg". "cd_combined_offices"
> x WHERE
> ZjQcmQRYFpfptBannerStart
> This Message Is From an External Sender
> This message came from outside your organization.
>
> ZjQcmQRYFpfptBannerEnd
>

-- 

Samuel Stearns
Team Lead - Database

---

From: Tom Lane <[email-masked]>
Date: Thu, 25 Sep 2025 13:08:17 -0400

> USAGE has already been granted.

[ shrug... ] Apparently not.

			regards, tom lane

---

From: Sam Stearns <[email-masked]>
Date: Thu, 25 Sep 2025 10:15:20 -0700

csbstage=# grant usage on schema treg to cwstagesvc;
GRANT
csbstage=# \q
[postgres@shiludbapql01 scripts]$ psql -U cwstagesvc csbstage
Password for user cwstagesvc:
psql (16.9)
Type "help" for help.

csbstage=# begin;
BEGIN
csbstage=*# INSERT INTO treg.cd_combined_office_mappings (
csbstage(*#     tcsi_office_id, combined_office_id, sb2_account_id,
postal_code, category,
csbstage(*#     account_name, city, state, is_preferred, is_closed,
is_parent
csbstage(*# ) VALUES
csbstage-*# ('TCSI001', 10001, 2001, '90210', 'RETAIL', 'Acme Corp', 'Los
Angeles', 'CA', 'Y', 'N', 'Y'),
csbstage-*# ('TCSI002', 10001, 2002, '10001', 'WHOLESALE', 'Beta LLC', 'New
York', 'NY', 'N', 'N', 'N'),
csbstage-*# ('TCSI003', 10002, 2003, '60601', 'RETAIL', 'Gamma Inc',
'Chicago', 'IL', 'Y', 'N', 'N'),
csbstage-*# ('TCSI004', 10003, 2004, '77001', 'SERVICE', 'Delta Co',
'Houston', 'TX', 'N', 'Y', 'N'),
csbstage-*# ('TCSI005', 10003, 2005, '33101', 'RETAIL', 'Epsilon Ltd',
'Miami', 'FL', 'Y', 'N', 'Y');
ERROR:  permission denied for schema treg
LINE 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 x
csbstage=!# rollback;
ROLLBACK
csbstage=#

On Thu, Sep 25, 2025 at 10:08 AM Tom Lane <[email-masked]> wrote:

> Sam Stearns <sam. stearns@ dat. com> writes: > USAGE has already been
> granted. [ shrug. . . ] Apparently not. regards, tom lane 
> 
> 
> 
> ZjQcmQRYFpfptBannerStart

-- 

Samuel Stearns
Team Lead - Database

---

From: Tom Lane <[email-masked]>
Date: Thu, 25 Sep 2025 13:32:55 -0400

Sam Stearns <[email-masked]> writes:
> ...

Looking closer, that's not your original query: it looks to be
a foreign-key enforcement query.  That'll be run as the owner
of the table (I think the owner of the referencing table, but
I might have that backwards).  That owner is what is lacking
permissions.

			regards, tom lane

---

From: Sam Stearns <[email-masked]>
Date: Thu, 25 Sep 2025 11:59:10 -0700

Granting USAGE to the table owner did the trick!  Thank you, Tom!

Sam

On Thu, Sep 25, 2025 at 10:32 AM Tom Lane <[email-masked]> wrote:

> Sam Stearns <sam. stearns@ dat. com> writes: > csbstage=*# INSERT INTO
> treg. cd_combined_office_mappings ( > csbstage(*# tcsi_office_id,
> combined_office_id, sb2_account_id, > postal_code, category, > .. . >
> ERROR: permission
> ZjQcmQRYFpfptBannerStart

-- 

Samuel Stearns
Team Lead - Database