This document is a work in progress. Any comments would be extremely helpful and appreciated!
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.
- 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 ispostgrest.users
.-r, --refresh-relation [relation]
: The relation where PostgREST Auth will store refresh tokens. May be defined by PostgREST Auth. The default ispostgrest.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 validatingpass
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 tosecret
.-c, --camelcase
: Camelcases everything in the REST interface (🐫).
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.
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
.
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.
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.
}
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.“
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
‘sissued_by
. - The request user matches the
refresh_token
‘sissued_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.
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
andpass
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.
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.
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.
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.
Becuase PUT
is idempotent, by requiring the old_pass
this endpoint cannot be idempotent without some state knowledge.
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.
- 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.
- 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
andissued_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.
Hi, I am a novice end-user. Sorry to revive a 3 year old thread; this page is a top google result for 'automatic token refresh with postgrest', and on first read very obviously the exact thing I'm trying to do.
Has there been progress from here? Was this decided as out-of-scope for pgrest? Personally, I think it (this topic, token refresh) would be an outstanding candidate for 'Tutorial 2' at 'https://postgrest.org/en/stable/'.
The rest of my 'automatic token refresh with postgrest' search results go into implementations via Postgres /node/sequelize. If there's a postgresT solution somewhere please link me to it!