4

I'm trying to understand the initial steps of PostgREST tutorial.

In the mentioned tutorial, it is recommended to create two different roles named web_anon and authenticator as below:

create role web_anon nologin;

grant usage on schema api to web_anon;
grant select on api.todos to web_anon;

create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;

As far as I know, the the PostgREST server receives Rest API requests from the clients, without any information about the user (role). And also, as far as I know, nologin roles can't do login to database. (can they send queries?)

So the questions are:

  1. why do we need two different roles? What is the role of web_anon and what is the role of authenticator?

  2. What can a nologin role do in postgres?

  3. When PostgREST receives a rest API query, which user does it use to send and execute that query to the database?

Ebrahim Ghasemi
  • 5,850
  • 10
  • 52
  • 113
  • https://www.postgresql.org/docs/current/database-roles.html –  Mar 31 '20 at 13:28
  • 1
    It's not in the linked tutorial, but the PostgREST docs explain why these roles are needed http://postgrest.org/en/v6.0/auth.html#authentication-sequence. – Steve Chavez Mar 31 '20 at 16:08

1 Answers1

7

To question 1:

A NOLOGIN role can be seen as a user group. The idea is to attach all privileges to a group rather than to individual users, which has several advantages:

  • It is possible to drop the user, because it does not have any privileges.

  • It is less work to add a user to a group or remove a user from a group than to grant or revoke lots of permissions whenever you have to add a user or change its privileges.

  • There is no danger of having so many individual ACL entries attached to a single database object that it becomes impossible to add more permissions (the whole metadata row has to fit into a single 8kB block).

This whole exercise only makes sense if you have many users in the database, otherwise it is silly. But it is a good idea to have different users for different purposes.

To question 2:

A NOLOGIN role can either be a group that carries privileges that users can inherit.

Another use is that you can use SET ROLE to assume the identity of the role.

To question 3:

I guess whatever user you use in the PostgreSQL connect string.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you dear Laurenz. Is there any difference between _NOLOGIN_ user and a _GROUP_? I mean why they (the PostgreSQL developer) introduced _NOLOGIN_ users while they already have groups? – Ebrahim Ghasemi Mar 31 '20 at 15:03
  • 1
    There are neither users nor groups in PostgreSQL. Both are roles. You can still say "user" to mean "login role" and "group" for "nologin role". I used the terms synonymously in my answer. – Laurenz Albe Mar 31 '20 at 15:14
  • And based on your answer, I can conclude that the _Postgrest_ needs the the name of user with LOGIN permission in its config file only, not the NOLOGIN user. right? While in the tutorial name of both users are written in the configuration file. Any idea why? – Ebrahim Ghasemi Mar 31 '20 at 15:20
  • Thanks for clarifying that. You are welcome to edit and improve my answer, since I don't know PostgREST. But I guess it is ok to use the same role for both, right? – Laurenz Albe Mar 31 '20 at 16:26
  • 1
    To clarify the answer to question 3, PostgREST doesn't use the connection user to send the query. It switches to the `web_anon` role through `SET LOCAL ROLE` to send the query as that role. The connection user doesn't have privileges other than to LOGIN to the db and switch to the `web_anon` role or other roles you define. – Steve Chavez Mar 31 '20 at 16:31
  • 2
    @LaurenzAlbe No, PostgREST docs don't recommend using the same role. `web_anon` is meant to be an "application role"(with RLS enabled) that serves a number of unauthenticated web application users. LOGIN roles such as `authenticator` are another concern. LOGIN roles could rotate and that shouldn't affect application roles. Or the same LOGIN could be used for different applications and you could have a `web_anon_for_app1` and `web_anon_for_app2` both with different privileges. Does that make sense? I could edit your answer with a summary of that. – Steve Chavez Mar 31 '20 at 17:23
  • @SteveChávez That would be nice of you. – Laurenz Albe Mar 31 '20 at 19:31