Обсуждение: INSERT Permission Denied

Поиск
Список
Период
Сортировка

INSERT Permission Denied

От
Sam Stearns
Дата:
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


Re: INSERT Permission Denied

От
Tom Lane
Дата:
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 "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

Re: INSERT Permission Denied

От
Sam Stearns
Дата:
USAGE has already been granted.

On Thu, Sep 25, 2025 at 9:58 AM Tom Lane <tgl@sss.pgh.pa.us> 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
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 "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


--

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com


Re: INSERT Permission Denied

От
Tom Lane
Дата:
Sam Stearns <sam.stearns@dat.com> writes:
> USAGE has already been granted.

[ shrug... ] Apparently not.

            regards, tom lane

Re: INSERT Permission Denied

От
Sam Stearns
Дата:
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 <tgl@sss.pgh.pa.us> wrote:
Sam Stearns <sam. stearns@ dat. com> writes: > USAGE has already been granted. [ shrug. . . ] Apparently not. regards, tom lane ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍ ‍
ZjQcmQRYFpfptBannerStart
This Message Is From an External Sender
This message came from outside your organization.
 
ZjQcmQRYFpfptBannerEnd
Sam Stearns <sam.stearns@dat.com> writes:
> USAGE has already been granted.

[ shrug... ] Apparently not.
			regards, tom lane


--

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com


Re: INSERT Permission Denied

От
Tom Lane
Дата:
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 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;

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

Re: INSERT Permission Denied

От
Sam Stearns
Дата:
Granting USAGE to the table owner did the trick!  Thank you, Tom!

Sam


On Thu, Sep 25, 2025 at 10:32 AM Tom Lane <tgl@sss.pgh.pa.us> 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
This Message Is From an External Sender
This message came from outside your organization.
 
ZjQcmQRYFpfptBannerEnd
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 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;

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


--

Samuel Stearns
Team Lead - Database
c: 971 762 6879 | o: 971 762 6879 | DAT.com