user login and session

[dawsongzhao0523]

we think it is time to add security framework that performs authentication, authorization, cryptography, and session management for sqlpage after building a demo for teammate.

[lovasoa]

That is in my plans for the future of SQLite. At the moment, you have to handle this the traditional way, creating an user table, using the form component to ask for username and password, and using something like pgcrypto to hash passwords.

[lovasoa]

One thing that I would like to add first is support for cookies. Then, it will be support for oidc with a syntax that will look something like

SELECT 
   'login' AS 'component',
   'http://example.com/auth/realms/demo/.well-known/openid-configuration' AS openid_configuration,
   'xxx' as client_secret;

SELECT
    'text' AS component,
    'Welcome, ' || (sqlpage_jwt() ->> '$.name') AS contents;

What would you think about something like that ?
@leoedin, I'm also tagging you, in case you have comments about the feature design... Would user auth be useful to you ?

[lovasoa]

SQLPage v0.7.0 was just released with a cool new feature : it supports cookies.

-- Sets the username cookie to the value of the username parameter
SELECT 'cookie' as component,
    'username' as name,
    $username as value
WHERE $username IS NOT NULL;

SELECT 'form' as component;
SELECT 'username' as name,
    'User Name' as label,
    COALESCE($username, sqlpage.cookie('username')) as value,
    'try leaving this page and coming back, the value should be saved in a cookie' as description;

select 'text' as component;
select 'log out' as contents, 'logout.sql' as link;

logout.sql:

-- Remove the username cookie
SELECT 'cookie' as component,
    'username' as name,
    TRUE as remove;

SELECT 'http_header' as component, 'index.sql' as Location;

https://github.com/lovasoa/SQLpage/releases

[pinaraf]

That is in my plans for the future of SQLite. At the moment, you have to handle this the traditional way, creating an user table, using the form component to ask for username and password, and using something like pgcrypto to hash passwords.

Note: this is not a recommended way of doing it since it means sending your clear-text password to the database, thus through any layer in-between. It is always preferred to send hashes instead.

[lovasoa]

this is not a recommended way of doing it since it means sending your clear-text password to the database, thus through any layer in-between. It is always preferred to send hashes instead.

@pinaraf : This used to be true a few years ago, but encrypted database connections have been supported for a long time now, and are now the default for most db providers. SQLPage will use a TLS encrypted connection by default if it's supported by the server. If your threat model includes attackers having access to clear-text communication between your application and the database, you probably have a serious problem in your architecture anyway.

[pinaraf]

this is not a recommended way of doing it since it means sending your clear-text password to the database, thus through any layer in-between. It is always preferred to send hashes instead.

@pinaraf : This used to be true a few years ago, but encrypted database connections have been supported for a long time now, and are now the default for most db providers. SQLPage will use a TLS encrypted connection by default if it's supported by the server. If your threat model includes attackers having access to clear-text communication between your application and the database, you probably have a serious problem in your architecture anyway.

Your threat model may imply not sending clear text passwords to DBAs, or to the DB logs if the query ends up being slow for any reason... Also, there are tools that are hopelessly using clear-text sessions (looking at you pgbouncer) that you may encounter in real world.

[pinaraf]

BTW, even in PostgreSQL 15, pgcrypto does not include any modern recommended password hashing algorithm. The best it has to offer lags behind the GPU-era, and it would not be before PG17 that anything better could be available (if someone submits such a patch obviously)
cf. https://cheatsheetseries.owasp.org/cheatsheets/Password_Storage_Cheat_Sheet.html

[lovasoa]

All of these are indeed valid points for a large organisation of multiple teams with strict confidentiality policies, but I would not go as far as saying that this is a vulnerable setup. As far as algorithms go, using blowfish with a high-enough iteration count should not pose a major risk either, unless your attacker is a nation state.

I'll add an example in the examples directory, with all the caveats you mentioned above in its README.

I'll update it when the next version comes out with support for encrypting passwords on SQLPage's side.

And in the long term, I plan to add support for OIDC, and officially recommend that as the authentication solution.

[lovasoa]

I added a full user authentication demo to this repo: https://github.com/lovasoa/SQLpage/tree/main/examples/user-authentication

I'd love to get feedback from you.

Authentication is the main thing I want to work on for the next version, in order to make it easier, more secure, and to offer multiple authentication options.

[lovasoa]

Hello all !

I'd like to publish SQLPage v0.7.2 today, with many new features, including a new authentication component.

I'd love it if you could read the documentation and maybe even test the new version on docker (lovasoa/sqlpage:main) and give feedback before I release it !

@frol @setop @pinaraf @dawsongzhao0523 @leonedin

[lovasoa]

I just released v0.7.2 with all the new features, and updated the authentication example 💫

https://github.com/lovasoa/SQLpage/tree/main/examples/user-authentication

[fire]

Is this completed?

[lovasoa]

@fire : yes, authentication is fully supported, both using HTTP Basic Auth and custom user records and password hashes stored in the database. Check out:

• The documentation for the authentication component
• The password hashing documentation
• The cookie component that allows you to easily store session cookies
• most importantly, the full authentication web app example source

In addition to these two authentication mechanisms, Single-Sign-On using OIDC is on the roadmap and will be implemented in a future version.

[lovasoa]

Ill close this issue and open a different one for OIDC. Feel free to follow the new one.

[lovasoa]

I made a demo of single-sign-on to a central authentication service using OAuth and OIDC entirely in SQLPage here: https://github.com/lovasoa/SQLpage/tree/main/examples/single%20sign%20on