Documentation

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.

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;
    }
}
Navigation
Home
Parents