“Codeless” backend using PostgreSQL, PostgREST and Keycloak

#1

Post by runeks » Thu, Feb 04, 2021, 9:13 AM UTC

“Codeless” backend using PostgreSQL, PostgREST and Keycloak
mathieupassenaud.fr

Re: “Codeless” backend using PostgreSQL, PostgREST and Keycloak

#2

Post by runeks » Thu, Feb 04, 2021, 9:51 AM UTC

Regarding the "users" table: how are users registered with Keycloak saved to the app DB? I assume this is necessary if I want to use Postgres row-level security. Is Keycloak set up to store newly created users in the app DB?

The article mentions a PostgREST endpoint "/rpc/add_user_if_not_exists". Do I need to set up Keycloak to perform a POST request on this endpoint upon a new user registering via Keycloak?

Or can this be done for all requests by simply executing "api.add_user_if_not_exists()" before all other queries, hereby assuming that whatever is in "request.jwt.claim.sub" has been verified by Keycloak?

Re: “Codeless” backend using PostgreSQL, PostgREST and Keycloak

#3

Post by mathieupassenau » Fri, Feb 05, 2021, 1:12 PM UTC

runeks wrote: post #2

Regarding the "users" table: how are users registered with Keycloak saved to the app DB? I assume this is necessary if I want to use Postgres row-level security. Is Keycloak set up to store newly created users in the app DB? The article mentions a PostgREST endpoint "/rpc/add_user_if_not_exists". Do I need to set up Keycloak to perform a POST request on this endpoint upon a new user registering via Keycloak? Or can t…

Hi,

In this case, we use this procedure from the frontend app. When a user registers on Keycloak, this user is only stored on Keycloak.

Keycloak is used as an authentication provider, a "login with". The user is registered in the app on first use, even if the account already exists in Keycloak.

so, calling "api.add_user_if_not_exists" can be used on every request, only the first has an effect.

The field request.jwt.claim.sub is called "subject", this is a unique identifier for the user (a GUID with Keycloak). This field is provisionned by Keycloak, the JWT token has been verified by Postgrest with all informations (Keys).

Re: “Codeless” backend using PostgreSQL, PostgREST and Keycloak

#4

Post by runeks » Tue, Feb 09, 2021, 10:27 AM UTC

mathieupassenau wrote: post #3

runeks wrote: post #2

Regarding the "users" table: how are users registered with Keycloak saved to the app DB? I assume this is necessary if I want to use Postgres row-level security. Is Keycloak set up to store newly created users in the app DB? The article mentions a PostgREST endpoint "/rpc/add_user_if_not_exists". Do I need to set up Keycloak to perform a POST request on this endpoint upon a new user registering via Keycloak? Or can t…

Hi, In this case, we use this procedure from the frontend app. When a user registers on Keycloak, this user is only stored on Keycloak. Keycloak is used as an authentication provider, a "login with". The user is registered in the app on first use, even if the account already exists in Keycloak. so, calling "api.add_user_if_not_exists" can be used on every request, only the first has an effect. The field request.jwt.c…

I'm not sure how you found my question, but thank you very much for your answer, Mathieu!