user-authentication

User authentication demo

This example demonstrates how to handle user authentication with SQLpage.

It uses a PostgreSQL database to store user information and session ids, but the same principles can be applied to other databases.

All the user and password management is done in SQLPage, which uses best practices for password storage.

This demonstrates how to implement:

• a signup form
• a login form
• a logout button
• secured pages that can only be accessed by logged-in users

User authentication is a complex topic, and you can follow the work on implementing differenet authentication methods in this issue.

How to run

Install docker and docker-compose.

Then run the following command in this directory:

docker-compose up

Then open http://localhost:8080 in your browser.

Caveats

In this example, we handle user creation and login in SQLpage.

If you are implementing user authentication in an public application with potentially sensitive data, you should propably read the Authentication Cheat Sheet from OWASP.

Screenshots

Signup form	Login form	Protected page

How it works

User creation

The signup form is a simple form that is handled by create_user.sql. You could restrict user creation to existing administrators and create an initial administrator in a database migration.

User login

The login form is a simple form that is handled by login.sql.

login.sql checks that the username exists and that the password is correct using the authentication component extension with

SELECT 'authentication' AS component,
    'signin.sql' AS link,
    (SELECT password_hash FROM user_info WHERE username = :username) AS password_hash,
    :password AS password;

If the login is successful, an entry is added to the login_session table with a random session id.

If it is not, the authentication component will redirect the user to the login page and stop the execution of the page.

The session id is then stored in a cookie on the user's browser.

The user is then redirected to ./protected_page.sql which will check that the user is logged in.

Protected pages

Protected pages are pages that can only be accessed by logged-in users.

There is an example in protected_page.sql that uses the redirect component to redirect the user to the login page if they are not logged in.

Checking whether the user is logged in is as simple as checking that session id returned by sqlpage.cookie('session') exists in the login_session table.

User logout

The cookie can be deleted in the browser by navigating to ./logout.sql.