Route authentication and protection

[guilhermeforprojeto]

Protection of all routes in an existing database (postgresql v16), authentication for routes with access levels.

Is there any working example?

[laurenceisla]

As a summary:

Routes are protected by the permissions given to the database user who is making the request. The user could be anonymous (set by db-anon-role) or an authenticated one through JWT. Only the schemas specified in db-schemas are exposed.

Is there any working example?

Suppose you have an existing database:

db: mydatabase
schemas: public, sales, accounting
roles: postgres, generic_user

And you set postgrest config like this:

db-uri = "postgres://postgres:password@localhost:5432/mydatabase"
db-schemas = "public"
db-anon-role = "generic_user"

Then only the relations inside the public schema will be exposed, and only those that can be accessed by generic_user (the anonymous role). E.g. if generic_user only has permissions to tables public.table_a, public.table_b, sales.orders, then only the routes /table_a and /table_b can be accessed by PostgREST with this anonymous user.

Maybe a more detailed example could be added to the docs for an existing database (commented in PostgREST/postgrest-docs#748 (comment))

---

With that in mind, check the docs for more complete info related to security:

• https://postgrest.org/en/latest/references/auth.html
• https://postgrest.org/en/latest/explanations/db_authz.html
• https://postgrest.org/en/latest/explanations/schema_isolation.html
  Also:
• Question about schema isolation #3188