In my current project I intend for my Java EE Application to authenticate users against a “security-domain” in the WildFly 10.0.0.Final Application Server. In my scenario both PostgreSQL and WildFly are installed and running on an Ubuntu server on my LAN. This server has no GUI. I have posted my install notes for both PostgreSQL 9.5 and WildFly 10 on an Ubuntu 15.10 server here:

https://gesker.wordpress.com/2016/02/09/postgresql-and-postgis-on-ubuntu-15-10/
https://gesker.wordpress.com/2016/02/09/wildfly-10-on-ubuntu-15-10/

And, I already have “datasources” setup in WildFly that make use of a database (wildflyDb) in my PostgreSQL server. In Postgres the user “wildfly” owns the database “wildflyDb.” The setup notes for these datasources is at:

https://gesker.wordpress.com/2016/02/09/adding-postgresql-9-5-datasources-to-wildfly-10/

These install notes presume you have followed my directions in the URIs above.
From your workstaion…

Launch pgadmin3 and connect to your server that is running PostgreSQL, has the db user “wildfly” and the database “wildflyDb”
After connecting execute some SQL to create some tables and join these tables into a view so that they can be made use of by security domain we are going to setup in WildFly. Now, there are many ways to do this but I tried to keep this example pretty straight forward.
— Table of Application Users
BEGIN;
CREATE TABLE IF NOT EXISTS cfg_user (
id SERIAL PRIMARY KEY,
name VARCHAR(254) NOT NULL,
email VARCHAR(254) UNIQUE NOT NULL, — email address is userName — RFC3696 and RFC5321 indicate 254 characters plus 2 angle
pswd VARCHAR(128) NOT NULL, — SHA-512, Hex encoded in the application = 128 characters
description VARCHAR(254),
enabled BOOLEAN DEFAULT ‘1’ CHECK (enabled = ‘1’ OR enabled = ‘0’),
created TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
modified TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp
);

CREATE TRIGGER cfg_user_modified BEFORE UPDATE ON cfg_user FOR EACH ROW EXECUTE PROCEDURE update_modified_timestamp();
COMMIT;

BEGIN;
INSERT INTO cfg_user (name, email, pswd) VALUES (‘First User’, ‘first@fakecompany.com’, ‘346151f63c549a27c405e302b638b63039172c524a488e5fe01eacbbc7555a6dc7a36bb3bdd58c6f904a863555d12de4dde92244377f1876c56da867a9d64fxb’); — SHA-512 – hex
INSERT INTO cfg_user (name, email, pswd) VALUES (‘Second User’, ‘second@fakecompany.com’, ’61e0ea41fd076f90864293f595f59776f9ef41ed3cc69b652c04e15dab5efdf8e9ea876219e7dcb35610baff37372162e42111f45e1f24fc1cfb840fc6cbb3xd’); — SHA-512 – hex
COMMIT;

— Table of Application Roles
BEGIN;
CREATE TABLE IF NOT EXISTS cfg_role (
id SERIAL PRIMARY KEY,
name VARCHAR(10) UNIQUE NOT NULL,
description VARCHAR(30) UNIQUE NOT NULL,
enabled BOOLEAN DEFAULT ‘1’ CHECK (enabled = ‘1’ OR enabled = ‘0’),
created TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
modified TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp
);

CREATE TRIGGER cfg_role_modified BEFORE UPDATE ON cfg_role FOR EACH ROW EXECUTE PROCEDURE update_modified_timestamp();
COMMIT;

BEGIN;
INSERT INTO cfg_role (name, description) VALUES (‘root’, ‘root aka system administrator’);
INSERT INTO cfg_role (name, description) VALUES (‘financial’, ‘financial department’);
INSERT INTO cfg_role (name, description) VALUES (‘manager’, ‘manager’);
INSERT INTO cfg_role (name, description) VALUES (‘supervisor’, ‘supervisor’);
INSERT INTO cfg_role (name, description) VALUES (‘admin’, ‘administrative assistant’);
INSERT INTO cfg_role (name, description) VALUES (‘inspector’, ‘inspector’);
INSERT INTO cfg_role (name, description) VALUES (‘customer’, ‘customer’);
COMMIT;
— Map Application Users to Roles — I have screens in my application that control the contents of this table that only the Administrator can reach
BEGIN;
CREATE TABLE IF NOT EXISTS cfg_user_role_map (
id SERIAL PRIMARY KEY,
cfg_user_id INTEGER REFERENCES cfg_user (id) ON DELETE CASCADE ON UPDATE CASCADE,
cfg_role_id INTEGER REFERENCES cfg_role (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE (cfg_user_id, cfg_role_id),
enabled BOOLEAN DEFAULT ‘1’ CHECK (enabled = ‘1’ OR enabled = ‘0’),
created TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp,
modified TIMESTAMP WITH TIME ZONE DEFAULT current_timestamp
);

CREATE TRIGGER cfg_user_role_map_modified BEFORE UPDATE ON cfg_user_role_map FOR EACH ROW EXECUTE PROCEDURE update_modified_timestamp();
COMMIT;

BEGIN;
— Put FirstUser in ALL Roles — Unlikely but this is just an example
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 1);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 2);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 3);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 4);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 5);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 6);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (1, 7);
— Put SecondUser in ALL Roles — Unlikely but this is just an example
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 1);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 2);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 3);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 4);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 5);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 6);
INSERT INTO cfg_user_role_map (cfg_user_id, cfg_role_id) VALUES (2, 7);
COMMIT;
— Map Application Users to Roles AS a View –> for use in/by the WildFly Login-Module
BEGIN;
CREATE VIEW cfg_user_role_map_view AS
SELECT
cfg_user_role_map.id,
cfg_user.email,
cfg_role.name
FROM cfg_user_role_map
LEFT JOIN cfg_user ON cfg_user.id = cfg_user_role_map.cfg_user_id
LEFT JOIN cfg_role ON cfg_role.id = cfg_user_role_map.cfg_role_id;
COMMIT;

Ok, so our database is all setup. Add sample data as you see fit. Change tables names and columns as you see fit. Just make sure any adaptations you make match up to the code snippets we are going to add to the WildFly server below.

cd /srv/wildfly/standalone/configuration
create the file:
jdbcWildflyDbRealm.properties

Add the contents:
root=root
financial=financial
manager=manager
supervisor=supervisor
admin=admin
inspector=inspector
customer=customer

cp standalone-full.xml standalone-full.xml.backup

Copy the xml snippet below into the <security-domains> section of standalone-full.xml

<security-domain name=”jdbcWildflyDbRealm” cache-type=”default”>
<authentication>
<login-module code=”Database” flag=”required”>
<module-option name=”dsJndiName” value=”java:jboss/datasources/PgDsWildflyDb”/>
<module-option name=”principalsQuery” value=”SELECT pswd FROM cfg_user WHERE email=?”/>
<module-option name=”rolesQuery” value=”SELECT name, ‘Roles’ FROM cfg_user_role_map_view WHERE email=?”/>
<module-option name=”hashAlgorithm” value=”SHA-512″/>
<module-option name=”hashEncoding” value=”hex”/>
<module-option name=”unauthenticatedIdentity” value=”anonymous”/>
</login-module>
<login-module code=”RoleMapping” flag=”required”>
<module-option name=”rolesProperties” value=”file:${jboss.server.config.dir}/jdbcWildflyDbRealm.properties”/>
<module-option name=”replaceRole” value=”false”/>
</login-module>
</authentication>
</security-domain>
Make sure all the files associated with the WildFly server (espcially the new properties file we just created) belong the wildfly user:
chown -R wildfly.wildfly /srv/wildfly*

Finally, the constuction of your web application is outside the scope of this blog post but in order to make use of this new security-domain in the WEB-INF folder of your Java EE application you will need to add the file jboss-web.xml and add the contents:

<?xml version=”1.0″ encoding=”UTF-8″?>
<jboss-web>
<context-root>WhateverYouChoseForYourContextRoot</context-root>
<security-domain>jdbcWildflyDbRealm</security-domain>
</jboss-web>