Authentication with DBGate

Table of Contents

• Introduction
• Authentication Schemas
• HTTP and HTTPS
• Authenticating Users
• Sample of Authenticating Using Stored Procedures

Introduction

DBGate creates endpoints for each connection defined in the appsettings file.

Authentication is required if the connection string includes the user and pass placeholders.

To authenticate, DBGate prompts for a username and password, replaces the placeholders with the actual values, and attempts to connect to the database.

For example, consider the following appsettings.json:

{
  "ConnectionStrings": {
    "mssql": {
      "ProviderName": "System.Data.SqlClient",
      "ConnectionString": "Data Source=.\\SQLEXPRESS;Initial Catalog=master;User ID=user;Pwd=pass"
    },
    "mssql-023": {
      "ProviderName": "System.Data.SqlClient",
      "ConnectionString": "Data Source=mssql.savetodb.com;Initial Catalog=AzureDemo100;User ID=sample02_user3;Pwd=Usr_2011#_Xls4168"
    }
  }
}

In this example, the mssql endpoint requires authentication, while the mssql-023 endpoint does not.

Authentication Schemas

DBGate supports:

• Basic authentication, as specified in RFC2617
• JWT authentication

You can select the authentication schema using the Auth setting in the appsettings file.

The JWT schema can also use Basic authentication.

Both schemas are secure when used over HTTPS.

For unauthorized requests, DBGate returns error 401 "Unauthorized" for Basic authentication and error 403 "Forbidden" for unauthorized requests to protected resources using JWT authentication.

HTTP and HTTPS

Avoid using DBGate over HTTP, except for localhost, as browsers transmit usernames and passwords in plain text.

Always enable HTTPS and redirect HTTP traffic to HTTPS.

Authenticating Users

DBGate supports two methods for verifying a user's login and password:

1. Using the database login and password.
2. Using stored procedures.

The first method is the default. DBGate replaces the user and pass placeholders in the connection string and attempts to connect to the database.

If the connection is successful, DBGate loads the model and processes user requests. If it fails, it returns a connection error.

In the second method, DBGate calls a specified stored procedure, passing the username and password.

The procedure must validate the user credentials and return an empty message for success or a non-empty message for failed authentication.

This traditional approach allows managing users in a database without creating separate database logins.

Sample of Authenticating Using Stored Procedures

Let's assume we have a marketplace database to serve buyers and sellers.

The user table contains fields like id, uid, username, email, password_hash, role, and seller_id.

We will check user credentials using the usp_sign_in procedure and execute subsequent requests with specific logins for the buyer or seller roles, passing the acquired user or seller ID.

Different logins help manage object permissions based on user roles, ensuring buyers see buyer objects and sellers see seller objects.

Here’s a sample configuration:

"marketplace": {
  "ProviderName": "MySqlConnector",
  "ConnectionString": "Server=localhost;Password=pass;User ID=user;Database=marketplace",
  "SignIn": "marketplace.usp_sign_in",
  "AuthContextParams": "auth_user_id auth_seller_id",
  "RoleUsers": {
    "auth": {
      "Username": "marketplace_auth",
      "Password": "Usr_2011#_Xls4168"
    },
    "default": {
      "Username": "marketplace_buyer",
      "Password": "Usr_2011#_Xls4168"
    },
    "buyer": {
      "Username": "marketplace_buyer",
      "Password": "Usr_2011#_Xls4168"
    },
    "seller": {
      "Username": "marketplace_seller",
      "Password": "Usr_2011#_Xls4168"
    }
  }
}

Refer to the appsettings file for a complete field description.

Important Notes:

1. SignIn specifies the procedure name for credential validation.
2. RoleUsers must include the auth section with credentials to execute the sign-in procedure.
3. RoleUsers should have a default section for credentials when the user role is undefined.
4. RoleUsers must include buyer and seller sections with credentials for their respective roles.
5. AuthContextParams lists the fields returned from the sign-in procedure, which are passed as parameters to subsequent procedure calls.

Here’s a sample stored procedure in MySQL:

DROP PROCEDURE IF EXISTS usp_sign_in;

DELIMITER //
CREATE DEFINER=marketplace_dev@localhost PROCEDURE usp_sign_in(email varchar(50), password varchar(50))
BEGIN

DECLARE user_id int;
DECLARE uid varchar(50);
DECLARE role varchar(50);
DECLARE seller_id int;
DECLARE matched tinyint;

SELECT
    u.id, u.uid, u.role, u.seller_id, CASE WHEN get_password_hashed(password, u.password_hash) = u.password_hash THEN 1 ELSE 0 END AS matched
INTO
    user_id, uid, role, seller_id, matched
FROM
    user u
WHERE
    u.email = LOWER(email) OR u.username = LOWER(email)
LIMIT 1;

SELECT
    CASE WHEN matched = 1 THEN user_id ELSE NULL END AS auth_user_id,
    CASE WHEN matched = 1 THEN uid ELSE NULL END AS uid,
    CASE WHEN matched = 1 THEN role ELSE NULL END AS role,
    CASE WHEN matched = 1 THEN seller_id ELSE NULL END AS auth_seller_id,
    CASE
        WHEN matched = 1 THEN NULL
        WHEN user_id IS NOT NULL THEN 'Password not matched'
        ELSE 'User not found'
    END AS message;
END
//

DELIMITER ;

GRANT EXECUTE ON PROCEDURE usp_sign_in TO 'marketplace_auth'@'localhost';

Key Points:

1. The procedure must accept two parameters for username and password. The order matters, but the names do not.
2. You should have a single parameter for username, allowing users to log in with either an email or username. Just check both.
3. Return an empty message for success or an error description in the message field for failures.
4. Grant EXECUTE permission on the procedure to the user defined in the RoleUsers:auth section.