Use Security JDBC for login by user/email and password

Question

Can I use quarkus.security.jdbc.principal-query.sql for check login by both username/password and email/password. I try query belows but doesn't work :

quarkus.security.jdbc.principal-query.sql=SELECT u.password, u.role FROM test_user u WHERE u.email=? or u.username = ?

I have this in database :

CREATE TABLE test_user (
    id INT,
    username VARCHAR(255),
    email VARCHAR(255),
    password VARCHAR(255),
    role VARCHAR(255)
);

INSERT INTO test_user (id, username,email , password, role) 
VALUES (1, 'admin','admin@admin.com', 'admin', 'admin');

So when test 2 url belows will work correctly :

curl -i -X GET -u admin:admin http://localhost:8080/api/admin
curl -i -X GET -u admin@admin.com:admin http://localhost:8080/api/admin

First if you want to check user and pass I advise `Select 1 From user Where (user.id = :id OR user.mail = :mail) And user.pass = :pass`. Keep the password the less time possible, and if you can cypher it. — Zorglube, Sep 28 '21 at 14:41

S. Kadakov · Accepted Answer · 2021-10-01T07:30:53.163

Yes, you can do it easily. Just specify in your settings:

# Configure mysql connection
quarkus.datasource.db-kind=mysql
quarkus.datasource.username=root
quarkus.datasource.password=password
quarkus.datasource.jdbc.url=jdbc:mysql://localhost:3306/users

# Configure JDBC realm
quarkus.security.jdbc.enabled=true
quarkus.security.jdbc.principal-query.clear-password-mapper.enabled=true 
quarkus.security.jdbc.principal-query.clear-password-mapper.password-index=1
quarkus.security.jdbc.principal-query.attribute-mappings.0.index=2 
quarkus.security.jdbc.principal-query.attribute-mappings.0.to=groups

quarkus.security.jdbc.principal-query.sql=SELECT u.password, u.role FROM test_user u, ( SELECT @login := ? ) AS var WHERE u.email=@login or u.username=@login

Then add @RolesAllowed annotation to your resource:

package com.stack;

import java.security.Principal;
import javax.annotation.security.RolesAllowed;
import javax.inject.Inject;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

@Path("/hello")
@RolesAllowed({ "admin" })
public class GreetingResource {

    @Inject
    Principal principal;

    @GET
    @Produces(MediaType.TEXT_PLAIN)
    public String hello() {
        return "Hello, " + principal.getName() + "!";
    }
}

Make sure to add dependencies:

<dependency>
  <groupId>io.quarkus</groupId>
  <artifactId>quarkus-jdbc-mysql</artifactId>
</dependency>
<dependency>
  <groupId>io.quarkus</groupId>
  <artifactId>quarkus-elytron-security-jdbc</artifactId>
</dependency>

You may use the same approach for almost all kinds of DB. For example for PostgreSQL use WITH clause: How to declare a variable in a PostgreSQL query

Yeah, thank you, my friend. It works fine in postgres with below query : quarkus.security.jdbc.principal-query.sql=WITH loginName AS (VALUES (?)) SELECT u.password, u.role FROM test_user u WHERE u.email=(table loginName) or u.username=(table loginName) — Pham Hung, Oct 01 '21 at 23:41
@PhamHung if this answer solves your problem you should consider mark it as the answer for your question so everyone can see it as solved. — EAmez, Oct 13 '21 at 14:21

Use Security JDBC for login by user/email and password

1 Answers1