--- title: "Database" description: "The database provider of Derafu Auth" type: "docs" category: "doc" tags: [] authors: [Anonymous] date: "2026-10-08" last_update: "2026-10-08" time_minutes: 9 draft: false unlisted: false url: "https://www.derafu.dev/docs/core/auth/database" --- # Database The database provider authenticates the users against a table of a database: the application has a login form, and the user logs in with an identity (an email, for example) and a password. It does not use the libraries of the Keycloak provider. Its login form is protected with a CSRF token, so it needs a CSRF token manager and a session: [`derafu/csrf`](https://www.derafu.dev/docs/core/csrf) and [`derafu/session`](https://www.derafu.dev/docs/core/session), which a site that uses [`derafu/foundation`](https://www.derafu.dev/docs/core/foundation) already has (see [The login form](#the-login-form)). It has no real consumer yet, so its design may still change. Import `auth-database-services.yaml` and `auth-database-routes.yaml` (see [Configuration](configuration#what-the-application-imports)). The routes are `/auth/login` (the page, and the POST of the form) and `/auth/logout`. The variables are in [Configuration](configuration#variables-of-the-database-provider). ## The users The identity and the hash of the password are columns of a table (`AUTH_DATABASE_USER_TABLE`, `AUTH_DATABASE_USER_FIELD_IDENTITY` and `AUTH_DATABASE_USER_FIELD_PASSWORD`); the hash is the one of `password_hash()`. The roles and the details come from two queries, with the parameter `:identity`: ```env AUTH_DATABASE_USER_SQL_GET_ROLES="SELECT r.name FROM role r JOIN user_role ur ON ur.role_id = r.id JOIN user u ON u.id = ur.user_id WHERE u.email = :identity" AUTH_DATABASE_USER_SQL_GET_DETAILS="SELECT id, name FROM user WHERE email = :identity" ``` - The **roles** are the first column of each row. - The **details** are the first row. The default query is `SELECT * FROM WHERE = :identity`. The column of the password is **never** kept in the user, even if the query gives it: the user goes to the session. - The standard fields of the user (`getName()`, `getEmail()`...) are the columns that are called like them (`name`, `email`, `given_name`...), or the ones that the query renames. A column that is not there gives `null`, nothing fails: see [Users](users). - The names of the table and the columns can only be names (letters, numbers and underscores, and a schema for the table), because they go in the queries as they are. Anything else is a `ConfigurationException`. - The connection is made when it is needed, from `AUTH_DATABASE_URL` (or `DATABASE_URL` if it is empty), unless the application gives a `PDO` to `DatabaseUserRepository`. ## Active users A user can exist, with its password and its roles, and not be let in (it was suspended, it left the company). The package asks the database whether the user is **active** with a query, `AUTH_DATABASE_USER_SQL_IS_ACTIVE`, in the two places where it decides who gets in: **the login** and **the check of the session** (see [Security](security#the-user-of-the-session-is-a-copy)). A user that is not active can not log in, and its session is closed when the session is asked again. The default query uses a column of the table, `active` (`AUTH_DATABASE_USER_FIELD_ACTIVE` changes its name): ```sql SELECT active FROM user WHERE email = :identity ``` - **The first value of the first row says it.** `0`, `false`, `null`, an empty text and the texts `0`, `f`, `false`, `n`, `no` and `off` say that the user is **not** active; any other value says that it is (`1`, `true`, `t`, a count of two). No row is not active. - **Your own query.** If the table has no such column, or being active depends on more than a column, give the query in `AUTH_DATABASE_USER_SQL_IS_ACTIVE`. It has the parameter `:identity`: ```env AUTH_DATABASE_USER_SQL_IS_ACTIVE="SELECT COUNT(*) FROM user WHERE email = :identity AND suspended_at IS NULL AND deleted_at IS NULL" ``` - **No check.** If the application has no such concept, `AUTH_DATABASE_USER_SQL_IS_ACTIVE=false` (`'sql_is_active' => false` in the configuration) turns the check off: every user that exists is active. - **A query that does not work is a configuration error.** If the table has no column `active` (and the application did not give its own query or turn the check off), the first login or check fails with a `ConfigurationException` that says so and what to do: it is not taken for a database that is down, and the session is not kept waiting. - **The login does not say why.** A user that is not active with the right password gets the same message as a wrong password (`Invalid identity or password.`), and its hash is not changed. A user needs **at least one role** to get in a protected path: the authorization middleware of Mezzio asks for each role of the user, and a user with none is denied (see [Authorization](authorization#a-user-without-roles)). ## The login form The login form is protected with a **captcha** and with a **CSRF token**. The captcha (`captcha_protection: true` in its options, see [Captcha](https://www.derafu.dev/docs/ui/form/captcha)) is the one that the application configures with `CAPTCHA_PROVIDER` ([`derafu/captcha`](https://www.derafu.dev/docs/core/captcha)): its page renders the widget, and a login without having solved it, or with what was solved for another form, is rejected before the credentials are looked at (the user sees "The captcha is not valid. Try again."). **Without a captcha configured the login page fails** with a message that says what to configure (`CAPTCHA_PROVIDER=altcha` with `CAPTCHA_SECRET_KEY` needs no account; `CAPTCHA_PROVIDER=none` says, on purpose, that the application has none). The CSRF token works like this: its page renders a hidden field `_token`, and a login that comes without it, or with the token of another session, is rejected before the credentials are looked at (the user sees "The form is not valid or has expired. Reload the page and try again."). It is the protection that `derafu/form` gives to every form (see [CSRF Protection](https://www.derafu.dev/docs/ui/form/csrf)), and the token is asked for the form `login`. The application needs `derafu/csrf`, `derafu/session` and `derafu/captcha`, which the package only suggests (the Keycloak provider has no form and does not need them), and the middleware of the tokens after the session ones: ```yaml $middlewares: - '@Derafu\Http\Middleware\RouterMiddleware' - '@Mezzio\Session\SessionMiddleware' - '@Mezzio\Flash\FlashMessageMiddleware' - '@Derafu\Csrf\CsrfSessionMiddleware' - '@Mezzio\Authentication\AuthenticationMiddleware' # ... ``` Without a manager, rendering the login page fails with an exception that says so: a login form is never left open by omission. ## The password - It is verified with `password_verify()`. - A user that does not exist takes as long as one that does (the password is verified against a hash anyway), so the time of the answer does not say which identities exist. - If the hash was made with an algorithm or a cost that is not the current one (`PASSWORD_DEFAULT`), it is made again when the user logs in, which is the only time that the password is known. The column of the password must be writable. ## Failed attempts The failed attempts to log in are counted in a window that starts with the first one and lasts `AUTH_DATABASE_LOGIN_LOCK_SECONDS` (900 by default): - An identity from a network can fail `AUTH_DATABASE_LOGIN_MAX_ATTEMPTS` times (5 by default). - A network can fail five times as many, whatever the identity. Then the login is limited until the window ends: the credentials are **not even checked**, so a password that is guessed in the window is of no use, and the user sees "Too many failed login attempts. Try again in N minutes." A login that works clears the count of that identity from that address. A form that is not valid (a field is missing) is not a failed attempt. The counts are in the PSR-6 cache pool of the application, so they are shared by every request (`derafu/foundation` has one). The address is the **network of the client** that the HTTP layer decided (the attribute `client_network` that `ClientIpMiddleware` of [`derafu/http`](https://www.derafu.dev/docs/core/http/middleware#client-ip) leaves in the request, which is the `/64` for an IPv6 client), or the `REMOTE_ADDR` of the connection if the request does not have it. The headers of the request (`X-Forwarded-For`...) are never read: behind a proxy, say which ones are trusted in `ClientIpMiddleware`, otherwise every user is the proxy. The identity is compared without its case or its spaces. ## The login form and the pages - The form is `LoginForm`: its fields are named after the two columns (so `email` and `password` by default), and its titles are the fixed texts `Username` and `Password`, in the translation domain `auth` (see [Translations](translations)). - `DatabaseController::login` renders `auth/login` (a template that extends `layouts/default`, the layout of the application, and shows the flash messages). - After a login that works, and when a user that is logged in asks for the login page, the controller redirects to the page that was requested before the login, or to `AUTH_LOGIN_REDIRECT_PATH`: the login is a POST, and after it comes a redirect so a reload does not send the form again. The page is remembered by its path and query only, never by its host. - The protection of the form against cross-site requests is the one of `derafu/form`. ## The user of the session The session keeps the user as the database had it when the user logged in. Every `AUTH_REFRESH_INTERVAL_SECONDS` seconds (300 by default) the next request asks the database again by the identity that the session has, with the same queries as the login (`sql_get_details` and `sql_get_roles`): the roles and the details of the session are the ones of that moment. A user that is not in the table loses the session. If the database can not be asked, the session is kept, the user is not let in for that request, and the next one asks again. See [Security](security#the-user-of-the-session-is-a-copy). The identity is the only thing that the check uses: the password is not asked again, and it is neither read nor changed. ## Logout A POST to `/auth/logout` from the same origin (see [Security](security#logout)): the session is closed, its id is renewed and the user goes to `AUTH_LOGOUT_REDIRECT_PATH` (the login page by default). --- Last updated on 08/10/2026