public inbox for [email protected]
help / color / mirror / Atom feedINSERT Permission Denied
7+ messages / 2 participants
[nested] [flat]
* INSERT Permission Denied
@ 2025-09-25 16:49 Sam Stearns <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Sam Stearns @ 2025-09-25 16:49 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>; +Cc: Dugan Kniesteadt <[email protected]>; Henry Ashu <[email protected]>; Avi Vallarapu <[email protected]>
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT Permission Denied
@ 2025-09-25 16:58 Tom Lane <[email protected]>
parent: Sam Stearns <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-09-25 16:58 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Dugan Kniesteadt <[email protected]>; Henry Ashu <[email protected]>; Avi Vallarapu <[email protected]>
Sam Stearns <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT Permission Denied
@ 2025-09-25 17:06 Sam Stearns <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Sam Stearns @ 2025-09-25 17:06 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Dugan Kniesteadt <[email protected]>; Henry Ashu <[email protected]>; Avi Vallarapu <[email protected]>
USAGE has already been granted.
On Thu, Sep 25, 2025 at 9:58 AM Tom Lane <[email protected]> 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 <[email protected]> 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
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT Permission Denied
@ 2025-09-25 17:08 Tom Lane <[email protected]>
parent: Sam Stearns <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-09-25 17:08 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Dugan Kniesteadt <[email protected]>; Henry Ashu <[email protected]>; Avi Vallarapu <[email protected]>
Sam Stearns <[email protected]> writes:
> USAGE has already been granted.
[ shrug... ] Apparently not.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT Permission Denied
@ 2025-09-25 17:15 Sam Stearns <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Sam Stearns @ 2025-09-25 17:15 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Dugan Kniesteadt <[email protected]>; Henry Ashu <[email protected]>; Avi Vallarapu <[email protected]>
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 protected]> 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 <[email protected]> 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
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT Permission Denied
@ 2025-09-25 17:32 Tom Lane <[email protected]>
parent: Sam Stearns <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-09-25 17:32 UTC (permalink / raw)
To: Sam Stearns <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Dugan Kniesteadt <[email protected]>; Henry Ashu <[email protected]>; Avi Vallarapu <[email protected]>
Sam Stearns <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: INSERT Permission Denied
@ 2025-09-25 18:59 Sam Stearns <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Sam Stearns @ 2025-09-25 18:59 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pgsql-admin <[email protected]>; Dugan Kniesteadt <[email protected]>; Henry Ashu <[email protected]>; Avi Vallarapu <[email protected]>
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 protected]> 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 <[email protected]> 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
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link;
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-09-25 18:59 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-25 16:49 INSERT Permission Denied Sam Stearns <[email protected]>
2025-09-25 16:58 ` Tom Lane <[email protected]>
2025-09-25 17:06 ` Sam Stearns <[email protected]>
2025-09-25 17:08 ` Tom Lane <[email protected]>
2025-09-25 17:15 ` Sam Stearns <[email protected]>
2025-09-25 17:32 ` Tom Lane <[email protected]>
2025-09-25 18:59 ` Sam Stearns <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox