Configuring keycloak backend db which is postgreSQL with nuxeo as user/group directory

Ravindra_Nalavd — Mon, 01 Jul 2019 05:12:13 GMT

I wanted to point nuxeo to keycloak's backend db which is PostgreSQL for user/group directory For this I defined below extensions

sql datasource

<?xml version="1.0"?>
 <component name="postgre.keycloak.datasource.config" version = "1.0">
 <require>org.nuxeo.runtime.datasource.server.contrib</require>
 <extension target="org.nuxeo.runtime.datasource"
    point="datasources">
	<datasource name="jdbc/testdb"
          driverClassName="org.postgresql.Driver"
          maxPoolSize="20" minPoolSize="5" blockingTimeoutMillis="10000">
          <property name="url">jdbc:postgresql://<host>:<port>/testdb
          </property>
          <property name="username">user</property>
          <property name="password">*****</property>
        </datasource>
	</extension>
 </component>

SQL directory configuration for user as well group

<?xml version="1.0"?>
<component name="custom.directory.sql.config" version = "1.0">
	<require>org.nuxeo.ecm.directory.sql.SQLDirectoryFactory</require>
	<require>postgre.keycloak.datasource.config</require>
	<extension target="org.nuxeo.ecm.directory.sql.SQLDirectoryFactory" point="directories">
		<directory name="demoUserSQLDirectory">
			<schema>user</schema>
			<dataSource>testdb</dataSource>
			<table>keycloak_user</table>
			<idField>username</idField>
			<autoincrementIdField>false</autoincrementIdField>
			<dataFile></dataFile>
			<createTablePolicy>never</createTablePolicy>
			<querySizeLimit>15</querySizeLimit>
			<references>
				<inverseReference directory="demoGroupSQLDirectory" dualReferenceField="members" field="groups"/>
			</references>
		</directory>
		<directory name="demoGroupSQLDirectory">
			<schema>group</schema>
			<dataSource>testdb</dataSource>
			<table>keycloak_group</table>
			<idField>groupname</idField>
			<dataFile></dataFile>
			<createTablePolicy>never</createTablePolicy>
			<autoincrementIdField>false</autoincrementIdField>
			<references>
				<tableReference dataFile="" directory="demoUserSQLDirectory" field="members" schema="user2group" sourceColumn="groupId" table="keycloak_user_group" targetColumn="userId"/>
				<!--<tableReference directory="demoGroupSQLDirectory" field="subGroups" schema="group2group" sourceColumn="child_role" table="composite_role" targetColumn="composite"/><inverseReference directory="demoGroupSQLDirectory" dualReferenceField="subGroups" field="parentGroups"/> -->
			</references>
		</directory>
	</extension>
	<extension target="org.nuxeo.ecm.platform.usermanager.UserService" point="userManager">
		<userManager>
			<users>
				<directory>demoUserSQLDirectory</directory>
			</users>
			<groups>
				<directory>demoGroupSQLDirectory</directory>
			</groups>
			<defaultAdministratorId>administrator</defaultAdministratorId>
			<defaultGroup>custom_group</defaultGroup>
		</userManager>
	</extension>
</component>

Here I tried to execute search query on user using username, I faced below exception org.postgresql.util.PSQLException: ERROR: column "firstName" does not exist
And when I went through the code of org.nuxeo.ecm.directory.sql.SQLSQLDirectory, nuxeo looks for schema attributes in database table (e.g select username, email, firstName, lastName from ....). But as here, the column names are different in keycloak from nuxeo schema attributes, the search is failing.

Can any please suggest how I handle the above scenario where schema of user/group differs in nuxeo and database tables?

P.S.

In ldap directory configuration, the mapping between ldap attribute and nuxeo schema attribute can be added using tag <fieldMapping>
e.g for username, <fieldMapping name="username">cn</fieldMapping> Such tag is not supported in SQLDirectoryDescriptor

<users>
	<directory>demoUserSQLDirectory</directory>
</users>
<groups>
	<directory>demoGroupSQLDirectory</directory>
</groups>

I overrode the user and group schema using contributions and the schema is getting overridden successfully
But during initialization of nuxeo, the SQLDirectory is picking up the default directory definition and failing as default schema is changed

topic Configuring keycloak backend db which is postgreSQL with nuxeo as user/group directory in Nuxeo Forum

Configuring keycloak backend db which is postgreSQL with nuxeo as user/group directory