backends.mysql

mysql

The MySQL backend allows Radiator to authenticate and authorize users against MySQL or MariaDB databases.

Example

mysql "MYSQL_USERS" {
    # Database connection URL
    url "mysql://radiator:password@localhost:3306/radiator_db";

    # SQL query named "FIND_USER"
    query "FIND_USER" {
        # SQL statement
        statement "SELECT username, password_hash FROM users WHERE username = ? AND active = 1";

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

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

    # SQL query for user groups
    query "USER_GROUPS" {
        # SQL statement with JOIN
        statement "SELECT g.groupname FROM groups g INNER JOIN user_groups ug ON g.id = ug.group_id INNER JOIN users u ON ug.user_id = u.id WHERE u.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 MySQL database.

MySQL URL format:

mysql://[username[:password]@][host][:port][/database][?param1=value1&...]

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 "AUTHENTICATE_USER" {
    # SQL statement with parameter placeholders (?)
    statement "SELECT id, username, password_hash, email FROM users WHERE username = ? AND active = 1";

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

    # Result value mapping
    mapping {
        user.id = id;
        user.username = username;
        user.password = password_hash;
        user.email = email;
    }
}

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 = NOW() WHERE username = ?";

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

Filter expressions in mappings

Column mappings support filter expressions to transform values.

Note: MySQL supports native JSON column types. For best performance, use MySQL's native JSON functions (JSON_EXTRACT, ->, ->>) in your SQL queries to extract values directly in the database rather than fetching entire JSON documents and parsing them client-side.

Example using MySQL's native JSON extraction:

query "FIND_USER" {
    statement "SELECT username, password, reply_attrs->>'$.framed_ip_address' AS framed_ip FROM users WHERE username = ?";
    bindings {
        aaa.identity;
    }
    mapping {
        user.username = username;
        user.password = password;
        vars.ip = framed_ip;
    }
}