INSERT Permission Denied - Mailing list pgsql-admin

From Sam Stearns
Subject INSERT Permission Denied
Date
Msg-id CAN6TVj=MEs68RQJ3sBBNW9CH9OuwnTSj1OpgVvNrE+voWz7aHg@mail.gmail.com
Whole thread Raw
Responses Re: INSERT Permission Denied
List pgsql-admin
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


pgsql-admin by date:

Previous
From: Sbob
Date:
Subject: Debezium Streaming ERROR: invalid memory alloc request size
Next
From: Tom Lane
Date:
Subject: Re: INSERT Permission Denied