calebmer/postgrest-auth.md

## postgrest-auth.md

      
    Raw
  

              postgrest-auth.md
            
          
This document is a work in progress. Any comments would be extremely helpful and appreciated!

PostgREST Auth Protocol

This document will describe an authentication framework very loosely based on the OAuth2 specification for a seperate authentication server to be used with the main PostgREST resource server. As PostgREST is going in a layered direction, this authentication layer must be interchangeable with other authentication implementations.
Options


The first parameter (same as PostgREST) must be a PostgreSQL database connection string.
-p, --port [number]: The port on which the server will listen for HTTP requests. Defaults to 3001.
-u, --user-relation [relation]: The relation (table or view) which PostgREST Auth will use for generating JWTs. If the relation is a view, it is recommend that it be auto-updateable. Must be user defined. The default is postgrest.users.
-r, --refresh-relation [relation]: The relation where PostgREST Auth will store refresh tokens. May be defined by PostgREST Auth. The default is postgrest.refresh.
-i, --grant-issuer [role]: As the refresh relation may be created by PostgREST Auth, the roles specified with this flag will be granted both insert and delete rights on the refresh relation. This is optional and can be done in SQL.
-w, --pass-regex [string]: A regular expression for validating pass properties on users. Defaults to .{6,} which is any string longer than 6 characters with the justification being that users have learned how to write a good password by now, let them use whatever they want.
-e, --jwt-expire [time]: The relative time it takes for a JWT to expire. May be written as a number with a time unit (days, hours, minutes, seconds). Shorter times are recommended, default is 30 minutes. Important: Users may be confused at why tokens magically stop working after 30 minutes. It must be made explicitly clear in the docs why this is done.
-j, --jwt-secret [string]: The secret to use when encrypting JWTs. If it is the default value, we must emit an error. Defaults to secret.
-c, --camelcase: Camelcases everything in the REST interface (🐫).

User Relation

The user relation is how PostgREST Auth knows what username password combinations are valid. PostgREST auth expects the following columns to be present in the relation:

user: A unique user name. Could be an email.
pass: The bcrypted password. PostgREST Auth will automatically encrypt via Bcrypt on insertion.
role: The database role to be included in the JWT.
claims (optional): This optional column will be a JSON object of extra claims to be included in the JWT.

The table may contain extra columns. However, the user relation should still be updatable either by the fact that it is a table, auto-updatable view, or a view with instead of triggers. This allows PostgREST Auth to insert and update the relation whenever it gets an authenticated request.
pass restrictions.

Whenever PostgREST Auth attempts to insert or update the pass property, it will first check the --pass-regex parameter. If it passes then the pass is valid. Further triggers may be placed on the users relation to restrict what can be used as a pass.
Refresh Relation

The user of PostgREST Auth should never really need to look at the refresh relation. All it does is stores refresh tokens. The columns of this relation are as follows:

token: The actual refresh token which was given to the user.
issued_by: The user whom this token was assigned by.
issued_to: The user whom this token was assigned to.
created_at: A timestamp indicating when the token was created.
last_used_at: A timestamp indicating when the token was last used.

Users which may not issue refresh tokens should not have insert rights on this table. Users who may issue refresh tokens should have both insert and delete rights.
Refresh tokens are revokeable.
Generated JWTs

JWTs generated by PostgREST Auth will have the following claims structure:
{
  iss,      // `issued_by` in the refresh relation.
  sub,      // `issued_to` in the refresh relation.
  exp,      // Seconds since the epoch, using the `--jwt-expire` parameter.
  role,     // `role` for `issued_to` in the user relation.
  ...claims // `claims` for `issued_to` in the user relation.
}
Endpoints

All PostgREST Auth endpoints require an authentication scheme. Either Basic or Bearer. Bearer accepts the same JWTs as PostgREST would (with an exp check). Basic accepts the username and password encoded in the standard HTTP Basic authentication format. On every request which uses the Basic authentication scheme, the users relation must be queried to ensure that the username and password match.
Because authentication is required at every endpoint, the first user must be created by the developer manually in SQL. This is recommended to be done with UNION VALUES (…) (It would be nice if the docs could provide a convenience Bcrypt password generator for getting the pass column value).
Authentication is required on every request to ensure that only a client who created a refresh token may ever use it again.
SET LOCAL (role); is performed on every request with the role coming from the users relation and the authenticated client to limit rights access.
All endpoints start at the root (/). Anyone using the PostgREST Auth server is expected to mount their server on the route they are most comfortable with (recommended is /auth).
All endpoints will also return JSON data with the Content-Type of application/json.
For all following endpoints, user might be a query parameter. To avoid confusion, the user specified in the query parameter will henceforth be know as the “Requested User.“ And the user specified in earlier authentication steps will henceforth be known as the “Authenticated User.“
GET /access_token

Will always return an access_token (JWT) which will authenticate with a PostgREST resource server. Query parameters for this request include:

user: The user to create a token for.
refresh_token: A refresh token for the user.

The following checks must pass:

The refresh_token exists.
The authenticated user matches the refresh_token‘s issued_by.
The request user matches the refresh_token‘s issued_to.

If the first check fails we error with a 404. If any other check fails we error with a 403 and revoke the token.
If all checks pass we generate a new access_token and return it.
POST /refresh_token

Creates a refresh_token and also returns an initial access_token (TODO: Maybe no access_token?). Properties of the JSON request body are as follows:

user: The user to create a token for.
pass: The unencrypted password of the user (to be checked against the encrypted password in the database).

If not body is defined a refresh_token is created for the authenticated user. If a body is defined both user and pass are required.
The following checks must pass in order for this request to be successful:

The authenticated user may issue a token (has insert rights to the refresh relation).
The authenticated user‘s role has been granted the requested user‘s role (TODO: More PostgreSQL idiomatic way to word this?).
The requested user‘s user and pass combination are valid and exist in the user relation.

If any of these checks fail we error with a 403.
If all of these checks pass a refresh_token is created where issued_to is the requested user and issued_by is the authenticated user. The inserted token column will be a new random UUID.
DELETE /refresh_token

Will revoke one or more tokens for the authenticated user. Optional query parameters include:

user: The user to revoke all tokens for.
refresh_token: The specific token to delete.
unused_since: A time which will revoke all tokens that haven‘t been used since after that time.

If nothing is defined, all refresh tokens for the authenticated user will be revoked. Query parameters are used to filter the refresh tokens to be revoked.
GET /user

Returns the user column of the authenticated user. Nginx (or another proxy) could use this column to select more information about the user from the PostgREST resource server.
POST /user/pass

This route updates the authenticated user‘s password. The body of the request must be a JSON object containing both an old_pass and a new_pass property. This ensures that even if someone with malicious intent obtains a refresh_token or access_token they won‘t be able to lock the user out of their account.
Before setting a new password, the old_pass property must be validated against the database and the new_pass must oblige with the password restrictions defined above.
After setting the new password, revoke all of the refresh tokens for the user. Clients may want to use the new_pass data to obtain a new refresh token after this.
Why POST?

Becuase PUT is idempotent, by requiring the old_pass this endpoint cannot be idempotent without some state knowledge.
POST /users

Attempts to create a user with the provided JSON data. The pass property must oblige to password restricitions defined above. The pass property will be encrypted before going into PostgeSQL.
References


The Google OAuth2 implementation.
OAuth2 Simplified.
The OAuth2 specification.
OAuth2 and the road to hell written by former lead author and editor of the OAuth2 standard.
HTTP Method Definitions.
HTTP Status Code Definitions.
PostgREST Docs.

TODO


Thorough analysis of edgecases and potential security threats.
Create a “Forgot my Password“ account recovery feature (can be used for email validation and recovery).
Limit the number of refresh tokens a client can issue for a user (identical pairs of issued_by and issued_to are limited in the refresh relation).
Create a way to encrypt JWTs asymetrically via RSA. The auth server would get a private and public key whereas PostgREST would only get a public key.