Configuration error. null value in column "uid" violates not-null constraint.

[mindmazeCH]

Hello,

First of all, thanks for generate this module for Owncloud, it is very appreciated. We are currently testing your module to validate if we can use it or not.

Application version.

• Owncloud version: 10.0.8.5
• Web Server version: Apache/2.4.10
• Authentication module: auth_openidc_module / libapache2-mod-auth-openidc 2.1.6-1

Architecture.

We are successfully using the auth_openidc_module in our system for other purposes with greats results.
For Owncloud, this is our current architecture:
+--------------------------------+                    +-----------------------+
|                   |                     |              |
|    Apache Server    +-----------------------------------+  Apache Server  |
| Module auth_openidc  |   Proxy / Reverse Proxy   |   Owncloud     |
|                    |                      |              |
+-------------------------------+                     +-----------------------+

Our Apache configuration for OpenID login, is exactly the same as you have in the README file of this project, except for the proxy. For the proxy configuration, and following the instructions of the auth_openidc_module, we are setting the right variables to pass all the information to the proxied Apache:

RequestHeader set X-Remote-User %{REMOTE_USER}s RequestHeader set X-Forwarded-Proto https RequestHeader set X-Forwarded-Ssl on RequestHeader set X-Forwarded-Host owncloud.domain.com

    OIDCRedirectURI https://owncloud.domain.com/oidc_callback
    OIDCProviderMetadataURL https://idp.domain.com/auth/realms/myrealm/.well-known/openid-configuration
    OIDCScope "openid profile email"
    OIDCClaimPrefix OIDC_CLAIM_
    OIDCPassClaimsAs environment
    OIDCClientID owncloud-test
    OIDCClientSecret asdfasdf-123456789-adfasdfa
    OIDCClientName owncloud-test
    OIDCCryptoPassphrase 1234567890

    SSLEngine on
    SSLProxyEngine On
    SSLProxyVerify none
    SSLProxyCheckPeerCN off
    SSLProxyCheckPeerName off
    ProxyRequests Off
    ProxyVia Off

    <Proxy *>
            Require all granted
    </Proxy>

    # Special callback Location handled by mod_auth_openidc
    <Location /oidc_callback>
            AuthType openid-connect
            Require valid-user
    </Location>

    # This location handles creation of ownCloud user sessions
    <Location /index.php/apps/user_openidc/login>
             AuthType openid-connect
             Require valid-user
    </Location>

    # This is needed for OIDC claims to be visible in
    # the Admin configuration of attribute mappings
    <Location /index.php/settings/admin>
              AuthType openid-connect
              Require valid-user
    </Location>

    ProxyPreserveHost On
    AllowEncodedSlashes On
    ProxyPass / https://backend.domain.com/ nocanon
    ProxyPassReverse / https://backend.domain.com/`

In the openID client configuration, in our Identity provider (IdP), we added the following mappers with the user information:

• claim_userid
• claim_displayname
• claim_email
• claim_groups

Problem

To configure the plugin, we are using an Owncloud local user without SSO account.

The first weird thing is that all the Attribute mapping configuration is empty, in the sense that there are no values available in the list as shown in the screenshot:

Then, if we try to change the Backend Mode, we always get the error Unsupported setting:

The login using the OpenID plug in is working fine, we are redirected to our IdP, and after a successful login, we access to Owncloud.

In the logs, the only error we got is this one, which looks like is expecting a field for the UID (I did the test adding this entry as a claim, but it did not work):

{"reqId":"asdfasdfasdfadf","level":3,"time":"2018-05-07T15:34:57+00:00","remoteAddr":"10.10.10.1","user":"--","app":"index","method":"GET","url":"\/index.php\/apps\/user_openidc\/login?requesttoken=MyToken","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\NotNullConstraintViolationException\",\"Message\":\"An exception occurred while executing 'INSERT INTO \\\"oc_failed_login_attempts\\\" (\\\"ip\\\", \\\"uid\\\", \\\"attempted_at\\\") VALUES(?, ?, ?)' with params [\\\"10.10.10.1\\\", null, asdfadfasdf]:\\n\\nSQLSTATE[23502]: Not null violation: 7 ERROR: null value in column \\\"uid\\\" violates not-null constraint\\nDETAIL: Failing row contains (10.10.10.1, null, 1525707297).\",\"Code\":0,\"Trace\":\"#0 \\\/var\\\/www\\\/owncloud\\\/lib\\\/composer\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/var\\\/www\\\/owncloud\\\/lib\\\/composer\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Connection.php(1015): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOPgSql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'INSERT INTO \\\"oc...', Array)\\n#2 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Connection.php(210): Doctrine\\\\DBAL\\\\Connection->executeUpdate('INSERT INTO \\\"oc...', Array, Array)\\n#3 \\\/var\\\/www\\\/owncloud\\\/lib\\\/composer\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Query\\\/QueryBuilder.php(208): OC\\\\DB\\\\Connection->executeUpdate('INSERT INTO \\\"oc...', Array, Array)\\n#4 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/DB\\\/QueryBuilder\\\/QueryBuilder.php(141): Doctrine\\\\DBAL\\\\Query\\\\QueryBuilder->execute()\\n#5 \\\/var\\\/www\\\/owncloud\\\/apps\\\/security\\\/lib\\\/Db\\\/DbService.php(71): OC\\\\DB\\\\QueryBuilder\\\\QueryBuilder->execute()\\n#6 \\\/var\\\/www\\\/owncloud\\\/apps\\\/security\\\/lib\\\/Throttle.php(74): OCA\\\\Security\\\\Db\\\\DbService->addFailedLoginAttempt(NULL, '10.10.10.1')\\n#7 \\\/var\\\/www\\\/owncloud\\\/apps\\\/security\\\/lib\\\/Hooks.php(90): OCA\\\\Security\\\\Throttle->addFailedLoginAttempt(NULL, '10.10.10.1')\\n#8 \\\/var\\\/www\\\/owncloud\\\/apps\\\/security\\\/lib\\\/Hooks.php(73): OCA\\\\Security\\\\Hooks->failedLoginCallback(NULL)\\n#9 [internal function]: OCA\\\\Security\\\\Hooks->OCA\\\\Security\\\\{closure}(NULL)\\n#10 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Hooks\\\/EmitterTrait.php(99): call_user_func_array(Object(Closure), Array)\\n#11 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Hooks\\\/PublicEmitter.php(33): OC\\\\Hooks\\\\BasicEmitter->emit('\\\\\\\\OC\\\\\\\\User', 'failedLogin', Array)\\n#12 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/User\\\/Session.php(494): OC\\\\Hooks\\\\PublicEmitter->emit('\\\\\\\\OC\\\\\\\\User', 'failedLogin', Array)\\n#13 \\\/var\\\/www\\\/owncloud\\\/lib\\\/public\\\/Events\\\/EventEmitterTrait.php(50): OC\\\\User\\\\Session->OC\\\\User\\\\{closure}(Array)\\n#14 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/User\\\/Session.php(516): OC\\\\User\\\\Session->emittingCall(Object(Closure), Array, 'user', 'login')\\n#15 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/User\\\/Session.php(316): OC\\\\User\\\\Session->loginWithPassword(*** sensitive parameters replaced ***)\\n#16 \\\/var\\\/www\\\/owncloud\\\/apps2\\\/user_openidc\\\/lib\\\/Controller\\\/LoginController.php(76): OC\\\\User\\\\Session->login(*** sensitive parameters replaced ***)\\n#17 [internal function]: OCA\\\\UserOpenIDC\\\\Controller\\\

Feedback

What are we doing wrong? I try to change the proxy configuration and the apache configuration, creating new claims and modifying the openID client to check if there were any option that was blocking the access to the owncloud, but I did not have success. So, what could we do to make it work, please?

• We are not passing the right claims in the openID TOKEN?
• A miss configuration in the Apache regarding the openIDc module?
• Is it due to the Proxy configuration?
• The module was not properly installed?

Thanks a lot in advance,
Daniel Cano

[mirekys]

Hello, thanks for testing the app and reporting the issues. The problem here seems to be that the proxy doesn't pass the required SERVER environment variables ($_SERVER['OIDC_CLAIM_*']) to the ownCloud's Apache.

This app requires these environment variables, which are being set directly by the mod_auth_openidc module (after it parses & validates the openID token), not the token itself. I would suggest moving the auth_openidc module with corresponding configuration to Apache server, where the owncloud is being served, if it is possible. Otherwise you will need to somehow pass all the OIDC env variables set on Proxy to the backend.

Please let me know how that worked.
Thanks

[mindmazeCH]

Hello,

Thanks for your answer, it was really useful. Indeed I was missing a parameter to set in the main proxy, and after that I can properly configure the new module.

But after the configuration, I got a new error. I am setting the Backend Mode in User Provisioning. I tried the following scenarios and I always get the same error:

1. The username does not exist in the owncloud DB backend but it exists in our OpenID provider.
2. The username exist in the owncloud DB backend and it exists with the same username in our OpenID provider.

In any case, I got this error:

Exception: {\"Exception\":\"InvalidArgumentException\",\"Message\":\"Returned account has different backend to the requested backend for sync\",\"Code\":0,\"Trace\":\"#0 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/User\\\/Manager.php(247): OC\\\\User\\\\SyncService->createOrSyncAccount('myuser', Object(OCA\\\\UserOpenIDC\\\\UserBackend))\\n#1

Thanks again for your support.
Cheers,

Daniel Cano

[mirekys]

Hello,

this one is a bit tougher because of the strict user backend checking on login and account synchronizations against a backend stored in the account, that was introduced in the latest ownCloud version.

The problem is that this app creates users using ownCloud core functions. This results
in 'OC\User\Database' being stored as an account's backend. But when the user logs in using this backend, it results in backend mismatch and the error you got.

This was solved by changing account's backend to 'OCA\UserOpenIDC\UserBackend' after the account is created in provisioning mode. But for any existing accounts or accounts created by another way, you will need to run the following OCC CLI command so users can log in using this app:

user_openidc:enablelogin [-u|--userid USERID] [-a|--all]

Please be warned that this has implications that any further account synchronization (updates of e-mail address, displayname,...) will be possible using this app only.