Postgrest: Error "permission denied for sequence user_id_seq" when POSTing

[omani]

Hi,

I guess this is more a postgresql question than releated to postgrest, but anyway:

I created a table in schema public named user. Then I created a view in schema "1" named users AS a simple select * from public.user.

I can GET and DELETE entries from the above view. but when I want to insert a new row I get the error as mentioned in the title:

the grants look as followed:
GRANT ALL ON TABLE public.user TO superuser;
GRANT ALL ON TABLE "1".users TO superuser;
GRANT ALL ON TABLE "1".users TO myuser;

GRANT myuser TO superuser;

Notice: user "myuser" is the user who gets a token from postgrest (that is, he is not the anonymous user).

Do I have to explicitly set a GRANT on the user_id_seq sequence as well? Because nothing is mentioned in the docs of postgrest and I wondered if it is my fault that this does not work.

[begriffs]

Oh I think you do have to grant access to the sequence too. Give it a try and let me know if that helps.

grant all on sequence user_id_seq to myuser;

[omani]

yes after a GRANT on the user_id_seq it works for me.

[begriffs]

Excellent! Could you update the docs please?

[omani]

Of course. thank you.

[omani]

wiki updated. https://github.com/begriffs/postgrest/wiki/Security-and-Permissions/_history

[amit151993]

@begriffs It worked, thanks

[steve-chavez]

From PostgreSQL >= 10, GENERATED AS IDENTITY should be preferred instead of serial. That way there's no need for an additional GRANT to the sequence for INSERTing.

Check this for more details: https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/