Password replay from a database

This page shows how to configure PingGateway to get credentials from a database. This example is tested with H2 1.4.197.

The following figure illustrates the flow of requests when PingGateway uses credentials from a database to log a user in to the sample application:

PingGateway intercepts the browser’s HTTP GET request.
The PasswordReplayFilter confirms that a login page is required, and passes the request to the SqlAttributesFilter.
The SqlAttributesFilter uses the email address to look up credentials in H2, and stores them in the request context attributes map.
The PasswordReplayFilter retrieves the credentials from the attributes map, builds the login form, and performs the HTTP POST request to the sample app.
The sample application validates the credentials, and responds with a profile page.

Before you start, prepare PingGateway and the sample application as described in the Quick install.

Set up the database:
1. On your system, add the following data in a comma-separated value file:
  Linux
  
  Windows
  
  /tmp/userfile.txt
  
  C:\Temp\userfile.txt
  username,password,fullname,email george,C0stanza,George Costanza,george@example.com kramer,N3wman12,Kramer,kramer@example.com bjensen,H1falutin,Babs Jensen,bjensen@example.com demo,Ch4ng31t,Demo User,demo@example.com kvaughan,B5ibery12,Kirsten Vaughan,kvaughan@example.com scarter,S9rain12,Sam Carter,scarter@example.com
2. Download the H2 database, unpack it, and start it:
  $ sh /path/to/h2/bin/h2.sh
  H2 starts, listening on port 8082, and opens the H2 Console in a browser.
3. In the H2 Console, select the following options, and then select Connect to access the console:
  - Saved Settings : Generic H2 (Server)
  - Setting Name : Generic H2 (Server)
  - Driver Class: org.h2.Driver
  - JDBC URL: jdbc:h2:~/ig-credentials
  - User Name: sa
  - Password : password
    
    If you have run this example before but can’t access the console now, try deleting your local ~/ig-credentials files and starting H2 again.
4. In the console, add the following text, and then run it to create the user table:
  DROP TABLE IF EXISTS USERS; CREATE TABLE USERS AS SELECT * FROM CSVREAD('/tmp/userfile.txt');
5. In the console, add the following text, and then run it to verify that the table contains the same users as the file:
  SELECT * FROM users;
6. Add the .jar file /path/to/h2/bin/h2-*.jar to the PingGateway configuration:
  - Create the directory $HOME/.openig/extra, where $HOME/.openig is the instance directory, and add .jar files to the directory.

Set up PingGateway:

Set up PingGateway for HTTPS, as described in Configure PingGateway for TLS (server-side).
Set an environment variable for the database password, and then restart PingGateway:
```
$ export DATABASE_PASSWORD='cGFzc3dvcmQ='
```
The password is retrieved by a SystemAndEnvSecretStore, and must be base64-encoded.

Add the following route to PingGateway to serve the sample application .css and other static resources:

Linux
Windows

$HOME/.openig/config/routes/00-static-resources.json

%appdata%\OpenIG\config\routes\00-static-resources.json

{
  "name" : "00-static-resources",
  "baseURI" : "http://app.example.com:8081",
  "condition": "${find(request.uri.path,'^/css') or matchesWithRegex(request.uri.path, '^/.*\\\\.ico$') or matchesWithRegex(request.uri.path, '^/.*\\\\.gif$')}",
  "handler": "ReverseProxyHandler"
}

Add the following route to PingGateway:

Linux
Windows

$HOME/.openig/config/routes/03-sql.json

%appdata%\OpenIG\config\routes\03-sql.json

{
  "heap": [
    {
      "name": "SystemAndEnvSecretStore-1",
      "type": "SystemAndEnvSecretStore"
    },
    {
      "name": "JdbcDataSource-1",
      "type": "JdbcDataSource",
      "config": {
        "driverClassName": "org.h2.Driver",
        "jdbcUrl": "jdbc:h2:tcp://localhost/~/ig-credentials",
        "username": "sa",
        "passwordSecretId": "database.password",
        "secretsProvider": "SystemAndEnvSecretStore-1"
      }
    }
  ],
  "name": "sql",
  "condition": "${find(request.uri.path, '^/profile')}",
  "handler": {
    "type": "Chain",
    "baseURI": "http://app.example.com:8081",
    "config": {
      "filters": [
        {
          "type": "PasswordReplayFilter",
          "config": {
            "loginPage": "${find(request.uri.path, '^/profile/george') and (request.method == 'GET')}",
            "credentials": {
              "type": "SqlAttributesFilter",
              "config": {
                "dataSource": "JdbcDataSource-1",
                "preparedStatement":
                "SELECT username, password FROM users WHERE email = ?;",
                "parameters": [
                  "george@example.com"
                ]
              }
            },
            "request": {
              "method": "POST",
              "uri": "http://app.example.com:8081/login",
              "form": {
                "username": [
                  "${contexts.sqlAttributes.row.USERNAME}"
                ],
                "password": [
                  "${contexts.sqlAttributes.row.PASSWORD}"
                ]
              }
            }
          }
        }
      ],
      "handler": "ReverseProxyHandler"
    }
  }
}

Notice the following features of the route:

The route matches requests to /profile.
The PasswordReplayFilter specifies a loginPage page property:
When a request is an HTTP GET, and the request URI path is /profile/george, the expression resolves to true. The request is directed to a login page.

The SqlAttributesFilter specifies the data source to access, a prepared statement to look up the user’s record, and a parameter to pass into the statement.

The request object retrieves the username and password from the context, and replaces the browser’s original HTTP GET request with an HTTP POST login request, containing the credentials to authenticate.

The request is for username, password, but H2 returns the fields as USERNAME and PASSWORD. The configuration reflects this difference.
For other requests, the expression resolves to false. The request passes to the ReverseProxyHandler, which directs it to the profile page of the sample app.

Test the setup:
1. In your browser’s privacy or incognito mode, go to https://ig.example.com:8443/profile.
  
  If you see warnings that the site isn’t secure, respond to the warnings to access the site.
  
  Because the property loginPage resolves to false, the PasswordReplayFilter passes the request directly to the ReverseProxyHandler. The sample app returns the login page.
2. Go to https://ig.example.com:8443/profile/george.
  
  Because the property loginPage resolves to true, the PasswordReplayFilter processes the request to obtain the login credentials. The sample app returns the profile page for George.

PingGateway 2024.9

Password replay from a database