backends.sqlite

sqlite

The SQLite backend allows Radiator to authenticate and authorize users against an SQLite database. SQLite is a lightweight, embedded database that requires no separate server process.

Example

Example configuration of an SQLite backend:

sqlite "SQL_DATABASE_USERS_INTERNAL" {
    # Database URL
    # url "sqlite:users-internal.sqlite";

    # Alternatively, configure a database filename
    filename "/var/lib/radiator/db/users-internal/users-internal.sqlite";

    # SQL query named "FIND_USER"
    query "FIND_USER" {
        # SQL statement
        statement "SELECT USERID, PASSWORD FROM USERIDS WHERE USERID = ?";

        # Query argument binding in order
        bindings {
            aaa.identity;
        }

        # Result value mapping
        mapping {
            user.username = USERID;
            user.password = PASSWORD;
        }
    }

    # SQL query named "USER_GROUPS"
    query "USER_GROUPS" {
        # SQL statement
        statement "SELECT groupname FROM groups INNER JOIN group_memberships ON groups.id = group_memberships.group_id INNER JOIN users ON group_memberships.user_id = users.id WHERE username = ?";

        # Query argument binding in order
        bindings {
            aaa.identity;
        }

        # Result values mapping
        mapping {
            user.group += groupname;
        }
    }
}

Configuration Options

url

This attribute specifies the connection string used to establish a connection with the SQLite database.

Example:

url "sqlite:users-internal.sqlite";

filename

Alternative to url for specifying the SQLite database file path directly.

Example:

filename "/var/lib/radiator/db/users-internal.sqlite";

query

This clause defines an SQL query operation that retrieves data from the database. The query clause is configured with a name.

The query result determines the backend action result: if no rows are returned, the result is reject. If one or more rows are returned, the result is accept.

Example configuration of a query:

query "FIND_USER" {
    # SQL statement
    statement "SELECT USERID, PASSWORD FROM USERIDS WHERE USERID = ?";

    # Query argument binding in order
    bindings {
        aaa.identity;
    }

    # Result value mapping
    mapping {
        user.username = USERID;
        user.password = PASSWORD;
    }
}

statement

This clause defines an SQL statement for write operations (INSERT, UPDATE, DELETE) that do not return result rows. Unlike query, the statement clause has no mapping section.

The statement result is always accept on success. If the statement fails, an error is raised.

Example configuration of a statement:

statement "UPDATE_LOGIN_TIME" {
    # SQL statement with parameter placeholders (?)
    statement "UPDATE users SET last_login = datetime('now') WHERE username = ?";

    # Statement argument binding in order
    bindings {
        aaa.identity;
    }
}

Filter expressions in mappings

Column mappings support filter expressions to transform values. This is useful for parsing JSON columns:

query "FIND_USER" {
    statement "SELECT username, password, reply_attrs FROM users WHERE username = ?";
    bindings {
        aaa.identity;
    }
    mapping {
        user.username = username;
        user.password = password;
        vars.ip = reply_attrs | json | jsonpath("$.framed_ip_address");
    }
}

For nullable JSON columns, use | default("{}") before | json to handle NULL values gracefully.

Parents