PATCH with updatable view of single record generates denied permission error #2962
Comments
|
This looks similar to this issue: #2169 The solution was to create a But, in this case, the problem is with a create schema core;
create type core.grade as enum('a','b','c','d','e');
create table core.updating (
id int primary key generated always as identity,
name text,
score core.grade
);
create view test.v_updating
as select id, name, score from core.updating;
insert into test.v_updating(name, score) values ('Maria', 'a'), ('Bob', 'b');
grant all on test.v_updating to postgrest_test_anonymous;curl -X PATCH "http://localhost:3000/v_updating?id=eq.2" -d '{"score": "a"}' -H "Content-Type: application/json" -i
# Latest
HTTP/1.1 401 Unauthorized
{"code":"42501","details":null,"hint":null,"message":"permission denied for schema core"}
#v10.2.0
HTTP/1.1 204 No ContentThe workaround I see is to |
BTW, there are now several ways to check for the version, the most common is to check the |
|
I tend to use user defined types perhaps more than the "next person". I say that b/c the one buggy theme with postgREST, is defined type. This is the 2-3rd time I had a confusing experience with PostgREST where the issue was to do with user defined types (e.g., in function params). In another postgresql project I created a "shared schema". It was a way to share types and open access to a schema for a clearly defined (encapsulated) intent. In other words avoids opening up the api to core. Perhaps there is value in considering a default exposure of not just "api" but also a shared schema to host the user-defined types? |
|
Pulling the |
|
I have this too since I upgraded from Postgrest 6.0.2 to 11.1.0. Now, I get errors when POSTing to views in the api schema when the json posted contains a field that has a custom type in the data schema to which the role is not granted usage permissions. This is the insert into query Postgrest 11.1 generated. WITH pgrst_source AS (
INSERT INTO "api"."retakes"(
"level",
"scope"
)
SELECT
"pgrst_body"."level",
"pgrst_body"."scope"
FROM
(SELECT '{"scope":"level","level":"1"}'::json AS json_data) pgrst_payload,
LATERAL (
SELECT
CASE
WHEN json_typeof(pgrst_payload.json_data) = 'array' THEN pgrst_payload.json_data
ELSE json_build_array(pgrst_payload.json_data)
END AS val
) pgrst_uniform_json,
LATERAL (
SELECT *
FROM json_to_recordset(pgrst_uniform_json.val) AS _(
"level" data.level, -- <------I believe this reference to the data schema was not there in Postgrest 6.0.2
"scope" text
)
) pgrst_body
RETURNING "api"."retakes".*
)
SELECT
'' AS total_result_set,
pg_catalog.count(_postgrest_t) AS page_total,
array[]::text[] AS header,
coalesce(json_agg(_postgrest_t)->0, 'null') AS body,
nullif(current_setting('response.headers', true), '') AS response_headers,
nullif(current_setting('response.status', true), '') AS response_status
FROM
(SELECT "retakes".* FROM "pgrst_source" AS "retakes" ) _postgrest_t;This is the error I now get when a user POSTs to the retakes view. I think the problem is introduced here: #2677 Updates
I am considering to copy the type in the data schema to one in the api schema, and then change the type of the column to |
|
@christiaanwesterbeek per my prior post, I solved the permissions issue within postgres with a shared schema. This avoided the need to give your data schema access to the api. Instead you give access to shared. Shared is only for user defined types so has a "clean intent". It does not solve the postgrest issue with defined types required for function params and views*. Your work-around of using text then casting to the type inside your function worked for me. It's not ideal but it I still get the "type safety", just not the "documentation by definition of the interface". For views, I might just create a function to access the view to accomplish the same. * note: In a non postgrest app, I shared the shared schema with the "webuser". I avoided any permission issues "on the edges"... views and function params as described. |
The system was up and running for nearly a year. I believe I may have triggered a new docker image pull. In doing so, I started to get a permissions/grant error.
The setup is based on the user-session manager example provided by postgrest community examples. The anonymous user is switched to a webuser when there is a valid session. The api provides an update-able view of the data hosted in the core schema. The view uses a where clause to limit the pull to whatever is set as the current user.
I have confirmed the ability to update the view directly using psql... without having to use a where clause (because by definition of the current user and role, I should only ever see one record). However, when I hit the endpoint by way of postgrest, I get a 401 response.
2 questions:
Thank you in advance. Using this tool has been great! I look forward to many more years of it.
- E
PostgreSQL 13.12 on x86_64-pc-linux-gnu
Postgrest version - using the latest docker image; version unknown b/c I could not find how to specify the version!
the postgrest generated query
the reported error
There is a
json_to_recordsetcall on/near char 485. The request body has the json object that echoes the fields in the view. The "request" response is a representation of the updated record. The details here may have changed. The details here may not have been fully replicated of what I confirmed in psql.The text was updated successfully, but these errors were encountered: