sqlitegopassword-protectiongo-sqlite3

Using/setting up user authentication for sqlite3 in golang


I have to make my database password protected as a task in my school. For example if anyone tries to access my database it will ask the password.
I am trying to use go-sqlite3 package and I have tried reading the official guide.
First step is to use go build --tags <FEATURE>.
It gaves me an error build .: cannot find module for path .
I dont know why and what are we building in the first place. I tried searching for practical examples also and didnt found any.

Can you explain to me how I can setup user authentication for my database using the golangs go-sqlite3 package?
Link to the package


Solution

  • You need to replace <FEATURE> in that instruction by extension name(s) you want to enable from table below (Seems there's an error in README and it has sqlite_ prefix stripped in example; build tag is indeed sqlite_userauth).

    So, to enable user authentication that will be go build -tags "sqlite_userauth".

    In your project with go-sqlite3 module dependency just make sure that you build with -tags sqlite_userauth.

    Here is minimal example showing how you would work with this in your project:

    mkdir sqlite3auth
    cd sqlite3auth
    go mod init sqlite3auth
    touch main.go
    

    main.go:

    package main
    
    import (
            "database/sql"
            "log"
    
            "github.com/mattn/go-sqlite3"
    )
    
    func main() {
            // This is not necessary; just to see if auth extension is enabled
            sql.Register("sqlite3_log", &sqlite3.SQLiteDriver{
                    ConnectHook: func(conn *sqlite3.SQLiteConn) error {
                            log.Printf("Auth enabled: %v\n", conn.AuthEnabled())
                            return nil
                    },
            })
    
            // This is usual DB stuff (except with our sqlite3_log driver)
            db, err := sql.Open("sqlite3_log", "file:test.db?_auth&_auth_user=admin&_auth_pass=admin")
            if err != nil {
                    log.Fatal(err)
            }
            defer db.Close()
    
            _, err = db.Exec(`select 1`)
            if err != nil {
                    log.Fatal(err)
            }
    }
    
    go mod tidy
    go: finding module for package github.com/mattn/go-sqlite3
    go: found github.com/mattn/go-sqlite3 in github.com/mattn/go-sqlite3 v1.14.10
    # First build with auth extension (-o NAME is just to give binary a name)
    go build -tags sqlite_userauth -o auth .
    # then build without it
    go build -o noauth .
    
    ./auth
    2022/01/27 21:47:46 Auth enabled: true
    ./noauth
    2022/01/27 21:47:46 Auth enabled: false