Sam Stearns <sam.stearns@dat.com> writes:
> We're getting a permission denied error on an INSERT and cannot figure out
> why:
> *[postgres@thiludbapql01 log]$ psql -U treg csbtestPassword for user
> treg:psql (16.6)Type "help" for help.csbtest=# show search_path;
> search_path---------------------------- csbtfsprd, interface, treg(1
> row)csbtest=# grant usage on schema treg to treg;GRANTcsbtest=# GRANT
> SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA treg TO
> treg;GRANTcsbtest=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA treg TO
> treg;GRANTcsbtest=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA treg TO
> treg;GRANTcsbtest=# insert into treg.cd_combined_office_mappings
> (combined_office_id, tcsi_office_id, sb2_account_id, account_name, city,
> postal_code, state, category, is_preferred, is_closed, is_parent)csbtest-#
> values (('734309'::int8),('S.668863.785512'),('668863'::int4),('Testing
> Something'),('Beaverton'),('97008'),('OR'),('Carrier'),('Y'),('N'),('Y'));ERROR:
> permission denied for schema tregLINE 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 xcsbtest=#*
The error appears to be getting thrown in a generated
foreign-key-constraint-enforcement query. From memory,
those are executed as the owner of the table (I think
owner of the referencing table, in this case). You've
apparently not granted usage on treg to that role.
regards, tom lane
