PATCH with updatable view of single record generates denied permission error

[EdmundsEcho]

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:

1. is this related to an issue mentioned in the past couple of releases? (there was one in particular that mentioned a new permissions issue)
2. how can I specify the precise docker image in the kube manifest to avoid/eliminate this as a possible source of the problem? (also, I'm in a kube context and could not see an clear way to print the version of postgrest).

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

--pid=3111104,user=xxx,db=xxx_v3,app=[unknown],client=xxx STATEMENT: 
WITH pgrst_source AS
  (UPDATE "api"."user_profiles"
   SET "company" = "pgrst_body"."company",
       "email" = "pgrst_body"."email",
       "first_name" = "pgrst_body"."first_name"
   FROM
     (SELECT $1 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 _("company" text, "email" core.domain_email, "first_name" text)) 
  pgrst_body RETURNING 1)
SELECT 
  '' AS total_result_set,
  pg_catalog.count(_postgrest_t) AS page_total,
  array[]::text[] AS header,
  '' AS body,
  nullif(current_setting('response.headers', TRUE), '') AS response_headers,
  nullif(current_setting('response.status', TRUE), '') AS response_status

the reported error

pid=3111104,user=xxx,db=xxx_v3,app=[unknown],client=xxx 
ERROR: permission denied for schema core at character 485

There is a json_to_recordset call 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.

[laurenceisla]

This looks similar to this issue: https://github.com/PostgREST/postgrest/issues/2169 The solution was to create a DOMAIN as a workaround.

But, in this case, the problem is with a VIEW, not a FUNCTION and I can confirm that this PATCH request works for version v10.2.0 but it does not for v11.0.1 and up. To reproduce:

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 Content

The workaround I see is to ALTER the column with the DOMAIN created for that type (e.g. create domain api.domain_email as core.domain_email), and use the Docker v10.2.0 for now, until the changes are made. Not sure if there's another solution for now.

[laurenceisla]

Postgrest version - using the latest docker image; version unknown b/c I could not find how to specify the version!

BTW, there are now several ways to check for the version, the most common is to check the Server header in the HTTP response.

[EdmundsEcho]

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?

[EdmundsEcho]

Pulling the postgrest/postgrest:v10.2.0 solved the problem. This was a subtle problem for sure. It hit specifically my ability to register new users. Similar updating codebase using PATCH worked fine. The difference being the presence of a user defined type. The timely response was helpful and greatly appreciated!

[christiaanwesterbeek]

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.

ERROR:  permission denied for schema data
LINE 21:                 "level" data.level,
                                 ^

I think the problem is introduced here: https://github.com/PostgREST/postgrest/pull/2677

Updates

1. To solve this quickly I'll probably downgrade to v10.2.0 en see if that helps. Will keep you posted...
2. Just noticed I will lose these new features of 11, that I started relying on: Top-level Ordering, Spread embedded resource. Well not in production yet luckily...
3. Confirmed that the downgrade to v10.2.0 fixed the problem!!

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 api.level. But that means I am referencing the api schema inside the data schema and that feels weird. So instead I might change the datatype of the column to simply text with a check constraint on it. I'll probably just wait for a month or so and see if you can come up with a way of not referring the column type in the generated insert into-statement...

[EdmundsEcho]

@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.