the trait bound *const str: diesel::deserialize::FromSql<diesel::sql_types::Timestamp, diesel::pg::Pg> is not satisfied

[GopherJ]

Setup

Versions

• Rust: rustc 1.33.0-nightly (daa53a52a 2019-01-17)
• Diesel: 1.4.1
• Database: postgresql@10
• Operating System Ubuntu 18.10

Feature Flags

• diesel: "r2d2", "postgres", "chrono", "uuid"

Problem Description

This error message shows when compile.

What is the expected output?

No error output

What is the actual output?

Error message

Are you seeing any additional errors?

##Other useful code

src/schema.rs

#![allow(proc_macro_derive_resolution_fallback)]
table! {
    access_tokens (id) {
        id -> Varchar,
        client_id -> Varchar,
        grant_type -> Varchar,
        issued_at -> Timestamp,
        scope -> Varchar,
        expires_at -> Timestamp,
        user_id -> Varchar,
    }
}

table! {
    acls (id) {
        id -> Varchar,
        allow -> Int4,
        ipaddr -> Nullable<Varchar>,
        username -> Nullable<Varchar>,
        clientid -> Nullable<Varchar>,
        access -> Int4,
        topic -> Varchar,
        created_at -> Timestamp,
        created_by -> Varchar,
    }
}

table! {
    auth_codes (id) {
        id -> Varchar,
        client_id -> Varchar,
        scope -> Varchar,
        expires_at -> Timestamp,
        redirect_uri -> Varchar,
        user_id -> Varchar,
    }
}

table! {
    clients (id) {
        id -> Varchar,
        name -> Varchar,
        secret -> Varchar,
        redirect_uri -> Varchar,
        response_type -> Varchar,
    }
}

table! {
    dashboards (id) {
        id -> Varchar,
        application_namespace -> Varchar,
        config -> Text,
        created_at -> Timestamp,
        user_id -> Varchar,
    }
}

table! {
    grant_types (name) {
        name -> Varchar,
    }
}

table! {
    users (id) {
        id -> Varchar,
        is_superuser -> Bool,
        username -> Varchar,
        password -> Varchar,
        scope -> Varchar,
        avatar -> Varchar,
        created_at -> Timestamp,
    }
}

joinable!(access_tokens -> clients (client_id));
joinable!(access_tokens -> grant_types (grant_type));
joinable!(access_tokens -> users (user_id));
joinable!(acls -> users (created_by));
joinable!(auth_codes -> clients (client_id));
joinable!(auth_codes -> users (user_id));
joinable!(dashboards -> users (user_id));

allow_tables_to_appear_in_same_query!(
    access_tokens,
    acls,
    auth_codes,
    clients,
    dashboards,
    grant_types,
    users,
);

src/models/oauth2.rs

#![allow(proc_macro_derive_resolution_fallback)]
use actix::Message;
use chrono::NaiveDateTime;

use std::convert::From;

use crate::{
    error::ServiceError,
    message::ServiceMessage,
    schema::{access_tokens, auth_codes, clients},
    CONFIG,
};

#[derive(Eq, PartialEq, Debug, Serialize, Deserialize, Queryable)]
pub struct Client {
    pub id: String,
    pub name: String,
    pub secret: String,
    pub response_type: String,
    pub redirect_uri: String,
}

#[derive(Debug, Insertable)]
#[table_name = "clients"]
pub struct NewClient<'a> {
    pub id: &'a str,
    pub name: &'a str,
    pub secret: &'a str,
    pub response_type: &'a str,
    pub redirect_uri: &'a str,
}

#[derive(Eq, PartialEq, Debug, Serialize, Deserialize, Queryable)]
pub struct AuthCode {
    pub id: String,
    pub client_id: String,
    pub expires_at: NaiveDateTime,
    pub scope: String,
    pub redirect_uri: String,
    pub user_id: String,
}

#[derive(Debug, Insertable)]
#[table_name = "auth_codes"]
pub struct NewAuthCode<'a> {
    pub id: &'a str,
    pub client_id: &'a str,
    pub scope: &'a str,
    pub expires_at: NaiveDateTime,
    pub redirect_uri: &'a str,
    pub user_id: &'a str,
}

#[derive(Eq, PartialEq, Debug, Serialize, Deserialize, Queryable)]
pub struct AccessToken {
    pub id: String,
    pub client_id: String,
    pub grant_type: String,
    pub scope: String,
    pub issued_at: NaiveDateTime,
    pub expires_at: NaiveDateTime,
    pub user_id: String,
}

#[derive(Debug, Insertable)]
#[table_name = "access_tokens"]
pub struct NewAccessToken<'a> {
    pub id: &'a str,
    pub client_id: &'a str,
    pub grant_type: &'a str,
    pub scope: &'a str,
    pub issued_at: NaiveDateTime,
    pub expires_at: NaiveDateTime,
    pub user_id: &'a str,
}

#[derive(Debug, Deserialize)]
pub struct CreateClient {
    pub name: String,
    pub redirect_uri: String,
}

#[derive(Debug, Deserialize)]
pub struct CreateAuthCode {
    pub authorize: bool,
    pub response_type: String,
    pub client_id: String,
    pub redirect_uri: String,
    pub user_id: String,
    pub scope: Option<String>,
    pub state: Option<String>,
}

#[derive(Debug, Deserialize)]
pub struct CreateAccessToken {
    pub grant_type: String,
    pub code: String,
    pub redirect_uri: String,
    pub client_id: String,
}

#[derive(Debug, Deserialize)]
pub struct VerifyAccessToken {
    pub id: String,
}

#[derive(Debug, Deserialize)]
pub struct ClientInfo {
    pub client_id: String,
    pub response_type: String,
    pub redirect_uri: String,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct AccessTokenResponse {
    pub token_type: String,
    pub access_token: String,
    pub expires_in: i64,
    pub scope: String,
}

impl Message for ClientInfo {
    type Result = Result<Client, ServiceError>;
}

impl Message for CreateClient {
    type Result = Result<Client, ServiceError>;
}

impl Message for CreateAuthCode {
    type Result = Result<AuthCode, ServiceError>;
}

impl Message for CreateAccessToken {
    type Result = Result<AccessToken, ServiceError>;
}

impl Message for VerifyAccessToken {
    type Result = Result<ServiceMessage, ServiceError>;
}

impl From<AccessToken> for AccessTokenResponse {
    fn from(AccessToken { id, scope, .. }: AccessToken) -> Self {
        AccessTokenResponse {
            token_type: "Bearer".to_string(),
            expires_in: CONFIG.oauth2.access_token_ttl,
            access_token: id,
            scope,
        }
    }
}

src/handler/oauth2.rs

use actix::Handler;
use chrono::{Duration, Utc};
use diesel::{self, BoolExpressionMethods, ExpressionMethods, QueryDsl, RunQueryDsl};
use uuid::Uuid;

use crate::{
    error::ServiceError,
    message::ServiceMessage,
    models::{
        db::DbExecutor,
        oauth2::{
            AccessToken, AuthCode, Client, ClientInfo, CreateAccessToken, CreateAuthCode,
            CreateClient, NewAccessToken, NewAuthCode, NewClient, VerifyAccessToken,
        },
        user::User,
    },
    CONFIG,
};

impl Handler<ClientInfo> for DbExecutor {
    type Result = Result<Client, ServiceError>;

    fn handle(&mut self, msg: ClientInfo, _: &mut Self::Context) -> Self::Result {
        use crate::schema::clients::dsl::*;

        let conn = &self
            .0
            .get()
            .map_err(|_| ServiceError::InternalServerError)?;

        clients
            .filter(
                &id.eq(&msg.client_id).and(
                    response_type
                        .eq(&msg.response_type)
                        .and(redirect_uri.eq(&msg.redirect_uri)),
                ),
            )
            .first::<Client>(conn)
            .map_or_else(
                |_| Err(ServiceError::BadRequest("Client doesn't exist".to_string())),
                |client| Ok(client),
            )
    }
}

impl Handler<CreateClient> for DbExecutor {
    type Result = Result<Client, ServiceError>;

    fn handle(&mut self, msg: CreateClient, _: &mut Self::Context) -> Self::Result {
        use crate::schema::clients::dsl::*;

        let conn = &self
            .0
            .get()
            .map_err(|_| ServiceError::InternalServerError)?;

        let new_client = NewClient {
            id: &Uuid::new_v4().to_string(),
            name: &msg.name,
            redirect_uri: &msg.redirect_uri,
            response_type: "code",
            secret: &Uuid::new_v4().to_string(),
        };

        diesel::insert_into(clients)
            .values(new_client)
            .get_result::<Client>(conn)
            .map_err(|err| ServiceError::from(err))
    }
}

impl Handler<CreateAuthCode> for DbExecutor {
    type Result = Result<AuthCode, ServiceError>;

    fn handle(&mut self, msg: CreateAuthCode, _: &mut Self::Context) -> Self::Result {
        if !msg.authorize {
            return Err(ServiceError::BadRequest(
                "Failed to create auth code, authorization is required".to_string(),
            ));
        }

        use crate::schema::auth_codes::dsl::auth_codes;
        use crate::schema::clients::dsl::{
            clients, id as client_id, redirect_uri as client_redirect_uri,
            response_type as client_response_type,
        };
        use crate::schema::users::dsl::{id as user_id, users};

        let conn = &self
            .0
            .get()
            .map_err(|_| ServiceError::InternalServerError)?;

        let user = users
            .filter(&user_id.eq(&msg.user_id))
            .first::<User>(conn)
            .map_err(|_| {
                ServiceError::BadRequest("User stored in session is invalid".to_string())
            })?;

        let client = clients
            .filter(
                &client_id.eq(&msg.client_id).and(
                    client_response_type
                        .eq(&msg.response_type)
                        .and(client_redirect_uri.eq(&msg.redirect_uri)),
                ),
            )
            .first::<Client>(conn)
            .map_err(|_| {
                ServiceError::BadRequest(
                    "client_id, redirect_uri doesn't match any application".to_string(),
                )
            })?;

        let new_auth_code = NewAuthCode {
            id: &Uuid::new_v4().to_string(),
            expires_at: Utc::now()
                .naive_utc()
                .checked_add_signed(Duration::seconds(CONFIG.oauth2.auth_code_ttl))
                .ok_or(ServiceError::InternalServerError)?,
            client_id: &msg.client_id,
            redirect_uri: &msg.redirect_uri,
            scope: normalize_scope(&user.scope, &msg.scope),
            user_id: &msg.user_id,
        };

        Ok(diesel::insert_into(auth_codes)
            .values(&new_auth_code)
            .get_result::<AuthCode>(conn)
            .map_err(|err| ServiceError::from(err))?)
    }
}

impl Handler<CreateAccessToken> for DbExecutor {
    type Result = Result<AccessToken, ServiceError>;

    fn handle(&mut self, msg: CreateAccessToken, _: &mut Self::Context) -> Self::Result {
        if msg.grant_type != "authorization_code" {
            return Err(ServiceError::BadRequest(
                "Currently Acs supports only authorization_code as grant_type".to_string(),
            ));
        }

        let conn = &self
            .0
            .get()
            .map_err(|_| ServiceError::InternalServerError)?;

        use crate::schema::auth_codes::dsl::*;

        let code = auth_codes
            .filter(
                &id.eq(&msg.code)
                    .and(client_id.eq(&msg.client_id))
                    .and(redirect_uri.eq(&msg.redirect_uri)),
            )
            .first::<AuthCode>(conn)
            .map_err(|_| ServiceError::BadRequest("Code not found".to_string()))?;

        if code.expires_at.timestamp() > Utc::now().naive_utc().timestamp() {
            use crate::schema::access_tokens::dsl::*;

            let new_access_token = NewAccessToken {
                id: &Uuid::new_v4().to_string(),
                user_id: &code.user_id,
                grant_type: "authorization_code",
                scope: &code.scope,
                client_id: &code.client_id,
                issued_at: Utc::now().naive_utc(),
                expires_at: Utc::now()
                    .naive_utc()
                    .checked_add_signed(Duration::seconds(CONFIG.oauth2.access_token_ttl))
                    .ok_or(ServiceError::InternalServerError)?,
            };

            Ok(diesel::insert_into(access_tokens)
                .values(&new_access_token)
                .get_result::<AccessToken>(conn)
                .map_err(|err| ServiceError::from(err))?)
        } else {
            Err(ServiceError::BadRequest(
                "Code has expired, please create a new auth code".to_string(),
            ))
        }
    }
}

impl Handler<VerifyAccessToken> for DbExecutor {
    type Result = Result<ServiceMessage, ServiceError>;

    fn handle(&mut self, msg: VerifyAccessToken, _: &mut Self::Context) -> Self::Result {
        use crate::schema::access_tokens::dsl::*;

        let conn = &self
            .0
            .get()
            .map_err(|_| ServiceError::InternalServerError)?;

        let access_token = access_tokens
            .filter(&id.eq(&msg.id))
            .first::<AccessToken>(conn)
            .map_err(|_| ServiceError::BadRequest("Token not found".to_string()))?;

        if access_token.expires_at.timestamp() >= Utc::now().naive_utc().timestamp() {
            Ok(ServiceMessage::True)
        } else {
            Ok(ServiceMessage::False)
        }
    }
}

fn normalize_scope(scope_of_user: &str, scope_of_req: &Option<String>) -> &'static str {
    if scope_of_user.is_empty() || scope_of_req.is_none() {
        ""
    } else {
        let scope_iter = scope_of_user.split(',').filter(|y| !y.is_empty());

        &scope_of_req
            .unwrap()
            .split(',')
            .filter(|x| !x.is_empty() && scope_iter.any(|y| &y == x))
            .collect::<Vec<&str>>()
            .join(",")
    }
}

Checklist

• I have already looked over the issue tracker for similar issues.
• This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)

[weiznich]

The field order of your AccessToken struct does not match the order of columns returned by your query. For queries without explicit select clause that's all columns in that order that is given by your table! definition.

The following definition of AccessToken fixes your problem.

#[derive(Eq, PartialEq, Debug, Serialize, Deserialize, Queryable)]
pub struct AccessToken {
    pub id: String,
    pub client_id: String,
    pub grant_type: String,
    pub issued_at: NaiveDateTime,
    pub scope: String,
    pub expires_at: NaiveDateTime,
    pub user_id: String,
}

Note that this behaviour is explicitly documented for our Queryable derive.

(Closed because that's nothing that is actionable by the diesel core team)

[GopherJ]

@weiznich Thank you so much for saving my day! Sorry for my mistake, I didn't notice that.

[videni]

Apparent the fields order are the same, but still has this error.

#[derive(Debug, Queryable, Identifiable)]
#[primary_key(user_id)]
#[column_name(user_id)]
#[table_name = "user"]
pub struct User {
    pub user_id: Uuid,
    pub username: String,
    pub password: String,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
    pub email: String,
    pub failures_num: i32,
    pub first_failed_at: Option<NaiveDateTime>,
    pub lock_expires_at: Option<NaiveDateTime>,
    pub rp_token: Option<String>,
    pub rp_token_created_at: Option<NaiveDateTime>,
    pub enabled: bool,
    pub salt: Option<String>,
}

table! {
    user (user_id) {
        user_id -> Uuid,
        username -> Varchar,
        password -> Varchar,
        created_at -> Timestamp,
        updated_at -> Timestamp,
        email -> Varchar,
        failures_num -> Int4,
        first_failed_at -> Nullable<Timestamp>,
        lock_expires_at -> Nullable<Timestamp>,
        rp_token -> Nullable<Varchar>,
        rp_token_created_at -> Nullable<Timestamp>,
        enabled -> Bool,
        salt -> Nullable<Varchar>,
    }
}

[weiznich]

@videni Our issue tracker is the wrong place to ask for support. It's for tracking bugs and coordinating work between contributors. Please use the forum or the gitter channel to ask this kind of questions. You also may want to include more context, because otherwise it's not possible to give you an helpful answer.

[videni]

@weiznich ok, thanks

[abdoullaye]

the problem is with the order.
after the id, created_at, updated_at or updated_at, created_at.
here are examples.

table! {
user (user_id) {
user_id -> Uuid,
created_at -> Timestamp,
updated_at -> Timestamp,
username -> Varchar,
password -> Varchar,
email -> Varchar,
failures_num -> Int4,
first_failed_at -> Nullable,
lock_expires_at -> Nullable,
rp_token -> Nullable,
rp_token_created_at -> Nullable,
enabled -> Bool,
salt -> Nullable,
}