backends.postgresql

postgres

The PostgreSQL backend allows Radiator to authenticate and authorize users against PostgreSQL databases.

Example

postgres "POSTGRES_USERS" {
    # Database connection URL
    url "postgresql://radiator:password@localhost:5432/radiator_db";

    # SQL query named "FIND_USER"
    query "FIND_USER" {
        # SQL statement using PostgreSQL-specific features
        statement "SELECT username, password_hash, user_data::jsonb FROM users WHERE username = $1 AND active = true";

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

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

    # SQL query for user roles with PostgreSQL array support
    query "USER_ROLES" {
        # SQL statement using array aggregation
        statement "SELECT array_agg(r.rolename) as roles FROM roles r INNER JOIN user_roles ur ON r.id = ur.role_id INNER JOIN users u ON ur.user_id = u.id WHERE u.username = $1";

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

        # Result values mapping
        mapping {
            user.roles = roles;
        }
    }
}

Configuration Options

url

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

PostgreSQL URL format:

postgresql://[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 with PostgreSQL features:

query "ADVANCED_USER_LOOKUP" {
    # SQL statement with PostgreSQL-specific syntax
    statement "SELECT u.username, u.password_hash, u.preferences::jsonb, array_agg(g.groupname) as groups FROM users u LEFT JOIN user_groups ug ON u.id = ug.user_id LEFT JOIN groups g ON ug.group_id = g.id WHERE u.username = $1 AND u.active = true GROUP BY u.id";

    # Query argument binding (PostgreSQL uses $1, $2, etc.)
    bindings {
        aaa.identity;
    }

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

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 ($1, $2, etc.)
    statement "UPDATE users SET last_login = NOW() WHERE username = $1";

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

Filter expressions in mappings

Column mappings support filter expressions to transform values.

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

Example using PostgreSQL's native JSON extraction:

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