Hi, I would like to integrate SQLite with authentication into the NodeJS project. I am looking to know if it is possible to setup authentication parameters and to perform authentication via, for example, the PRAGMA statement? I am looking into NOT using C-level API that is offered by SQLite user authentication module, or wrapping the API into the Javascript. Thanks!

Are you talking about something like:

PRAGMA user_login = 'jose'
PRAGRA user_pword = 'myPassW0rd'

Is it something like that?

Yes, I am thinking something like that. I have been looking into the page which lists the supported PRAGMA statements, however, I haven't found anything that would perform authentication.

But SQLite isn't designed to be a database server accessed by multiple different users at once. That has many impacts, one of which is that auth basically just comes down to "can you read and write the relevant file(s)". What is the scenario you are thinking of where someone can read and write to the DB file but still needs to authenticate to the DB?

Thanks for your reply.

I would like to protect SQLite database file from malicious users with a password. The SQLite database would be used by only one user and that "user" would be application. So it would come down to "application is the only user to read and write the storage file".

I am basically looking into authenticating from the Javascript code and checking if SQLite provides a way to authenticate via PRAGMA. Otherwise, I would need to expose C-level API for authentication to Javascript.

I am only looking into what is supported so far, so I know my next steps.

You might be talking about permission or encryption.

You don't need PRAGMAs. Just put some code in your program to read account & password from a table called Users and check them. It will make the program look like it has some security.

The problem with storing the password in the database is that SQLite isn't a client/server system. To have permission to open a SQLite database, a user or program needs permission to read the file from disk. And if they or your program have that, a hacker can make a copy of your file and inspect it at their leisure, taking as much time as they like to crack any passwords.

To make that harder you need encryption, and encryption is a paid extention to SQLite. See here for details.

https://sqlite.org/com/see.html

Hi, thanks for the reply.

You made a good point to rule out username/password solution.

I haven't studied a solution that would use permissions yet.

SEE sounds as a good and affordable solution. However, due to very intensive operations on the SQLite database (e.g. write/read every second, or even less than a second), could tell more how SEE impacts overall performance of the application?

Thanks for the information.