Database password authentication

[jaytxrx]

I am a beginner to SQLite. I have few questions regarding password authentication.

1. Does SQLite support password authentication ? I searched in internet and found answers that it doesn't. Is this true ?
2. In "go-sqlite3", I see functions like "AuthEnabled". What kind of authentication does this package support ?
3. In my project, I am accessing "go-sqlite3" as a driver as shown below. When I try to access "AuthEnabled", I get the error "sqlite_db.AuthEnabled undefined (type *sql.DB has no field or method AuthEnabled)"
   How can I access "AuthEnabled" ?

import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3" // Import go-sqlite3 library
)

[rittneje]

Does SQLite support password authentication ? I searched in internet and found answers that it doesn't. Is this true ?

It supports authentication via the userauth extension. https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt However, by default there is no authentication at all.

When I try to access "AuthEnabled", I get the error "sqlite_db.AuthEnabled undefined (type *sql.DB has no field or method AuthEnabled)"

This is because you tried to call the method on the sql.DB you got from sql.Open. You would need to access the raw driver connection.

db, err := sql.Open(...)
if err != nil { ... }

...

conn, err := db.Conn(context.Background())
if err != nil { ... }
defer conn.Close()

err := conn.Raw(func(driverConn interface{}) error {
    enabled := driverConn.(*sqlite3.Conn).AuthEnabled()
    ...
})
...

Can you expand upon your specific use case? Are you truly intending to authenticate the connection to the database itself? Or are you just trying to authenticate the users of your application?

[jaytxrx]

I will be storing the database file in the Webserver. Even though this DB file is not in the nginx www path, I don't want to keep the database file without password protection to read the contents or to edit. Currently I don't have plan to give edit access other users. So user authentication is not needed

[rittneje]

If your concern is someone being able to access the database file directly, you are going to want to encrypt it. Otherwise, a malicious attacker can bypass the SQLite API and directly read the file contents anyway.

If the database file is encrypted, I'm not sure if adding user authentication on top of that actually provides any value. Unfortunately, at this time, this library doesn't support encryption. See #487

If you still wish to use authentication despite the aforementioned caveat, you can configure it via your connection string. https://github.com/mattn/go-sqlite3#user-authentication

[jaytxrx]

This means better than activating user authentication, as a first step implement proper file permissions(read) inside the server. Still if someone gets access to the database file, then encryption is the next option

[dolmen]

Why do you want password authentication? Which problem do you think it would solve? What is your threat model?

[jaytxrx]

Simply to say even if someone gets access to the database file, I want to prevent read access.
From the answers above, encryption is the only option.

[navono]

Maybe sqlcipher can help.

[oSethoum]

I think rqlite is using this library but somehow the database file is encrypted with password.
i think they are using this.
https://github.com/rqlite/go-sqlite3

[otoolep]

rqlite does not encrypt the SQLite database. Why did you think it did?

[oSethoum]

@otoolep I tried to open raft.db file with sqlite browser and it said this isn't a database file which is the usual answer when the file is encrypted with password, I tried opening it with sqlite cypher and it asked me for a password.

[otoolep]

raft.db is not a SQLite database, it a BoltDB instance. rqlite stores its SQLite database in RAM by default.

[oSethoum]

@otoolep Thank you for clarifying 👌.