Provide auth example with app users + row level security

[begriffs]

This blog post presents a great approach for auth: https://blog.2ndquadrant.com/application-users-vs-row-level-security/

Postgrest does not allow arbitrary user queries, so the article's approach becomes really simple. Our docs need a clear example of how to do auth.

[PierreRochard]

This may be a little tangential... I banged my head against RLS and ended up enforcing it in the API views like so: WHERE admin.table_settings.user = current_user; Is this a valid alternative or is there a vulnerability?

I'll work through 2ndQuadrant's blog post this weekend to contrast

[begriffs]

Note to self, also include example of read-only or invisible fields based on user permissions as @sqwishy requested in the chat.

I guess in my case I'd want to use different views for hiding or showing some fields. But I'm not sure how the read-only thing would work except to write a trigger to do that validation on update for every field I guess? Although, really with read-only fields I'd prefer that requests containing them be rejected outright - even if the values match what is already on the object.

[begriffs]

Also @daurnimator poses another scenario we might want to model:

you can see all friends details; some friends of friends details; and anyone else visibility is determined by privacy settings

[begriffs]

Planning to make this tutorial 2 (we've got 0 and 1 already written)

[lidorcg]

Hi, I'm trying to tackle authentication and I followed the example on the docs and it seems I have generated the mentioned functions and tables but:

1. I can't reach the login function on /rpc/login
2. The user registration part is not the clearest to me - I think it's accomplished by insertion to the user table, but the auth schema is not published so I'm not sure what is the best way to solve this.

If I could get some pointers on how to continue, I'd also be very happy to contribute to the docs.

Thanks :)

[begriffs]

Sorry for the silence. Did you manage to make the login function work?

[JacobEvelyn]

I too am struggling with this example. First I was getting

ERROR:  type "basic_auth.jwt_token" does not exist

when trying to create the login function. Then when I changed that to public.jwt_token I can't seem to execute the login function with /rpc/login as @lidorcg described.

It feels like I'm close but I'm pretty inexperienced with functions/stored procedures in Postgres. 😕

[lidorcg]

Sorry for not replying as well, I haven't been able to solve the problem myself (probably for lack of trying). However, I have found the subzero project which has users and authentication procedures built-in.

[sqwishy]

I could be wrong, but it seems that row-level security policies are not enforced when a table is being accessed through a view. Instead, the table is accessed using the privilege of the view's owner, instead of the current role?

This is based on a small amount of experimentation and my poor reading of https://www.postgresql.org/docs/10/static/sql-createpolicy.html

If this is the case, I suppose the thing to do is duplicate the security policy into the view's where clause (and maybe use with (security_barrier) for good measure?). Can anyone confirm if my understanding is correct and if there isn't a better solution?

See also: https://www.postgresql.org/docs/10/static/rules-privileges.html

Work:

aleatory=# \d+ api.player
                             View "api.player"
   Column   |           Type           | Modifiers | Storage  | Description 
------------+--------------------------+-----------+----------+-------------
 id         | uuid                     |           | plain    | 
 email      | text                     |           | extended | 
 password   | text                     |           | extended | 
 last_login | timestamp with time zone |           | plain    | 
View definition:
 SELECT player.id,
    player.email,
    player.password,
    player.last_login
   FROM impl.player;
Options: security_barrier=true

aleatory=# \d impl.player
                             Table "impl.player"
   Column   |           Type           |              Modifiers              
------------+--------------------------+-------------------------------------
 id         | uuid                     | not null default uuid_generate_v4()
 email      | text                     | not null
 password   | text                     | not null
 last_login | timestamp with time zone | 
...
Policies:
    POLICY "player_policy" FOR ALL
      USING ((("current_user"())::text = (id)::text))
      WITH CHECK ((("current_user"())::text = (id)::text))
...
aleatory=# set role anonymous;
SET
aleatory=> select id from impl.player;
 id 
----
(0 rows)

aleatory=> select id from api.player;
                  id                  
--------------------------------------
 cc2f1706-0df7-436d-a46e-4ada21c526ae
 2fa79e43-cfc2-4452-9735-9e4495565837
(2 rows)

[steve-chavez]

@sqwishy Yes, RLS rules are applied to the view owner and it's likely that you have a SUPERUSER(or a role with BYPASSRLS) as the owner and that will bypass RLS.

What you can do is to alter view api.player owner to <non-superuser> and then create policy for that role(or leave it at the default PUBLIC so it applies to all roles), then your RLS rules will work normally for that view.

There's an example in postgrest-starter-kit where a dedicated "api" role is created for this,
which has rls policies applied and then the views are owned by this role.

[steve-chavez]

FYI, there's an RLS bug on VIEWs that @daurnimator reported in psql-bugs.

Basically, subqueries in the RLS policy are not checked against the privileges of the view owner but of the view caller.

I also bumped in to this bug when working on a example for the rls tutorial.

Edit: I think the most simple workaround for this would be wrapping the subquery in a security definer function.

[steve-chavez]

The bug was fixed https://www.postgresql.org/message-id/CAEZATCV_yDYoptaxtjiVB4yLwxQ%3DN7OWu8Ls98rA5MvBL%2BjKiQ%40mail.gmail.com.

Haven't tried and see if it's available on pg recent releases though.

[wolfgangwalther]

By now we have examples for app users + row level security!