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:
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
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
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,
pgsql-admin by date: