SqlResolver and Schema

d2b · November 30, 2019, 5:52pm

Hi,

I’m trying to configure a sqlresolver for DB2, but I don’t see a way of specifying a schema.
Is it possible to specify a schema ?

cornelinux · November 30, 2019, 7:18pm

Hi d2d,

welcome to privacyIDEA!

Users in a DB2? What is this? Customers in a backing site?

privacyIDEA uses SQLAlchemy, so you need to take a look at SQLAlchemy dialects: https://docs.sqlalchemy.org/en/13/dialects/

An alternative could be to go via ODBC. I think I saw this 6 years ago, s.o. connecting SQLAlchemy via odbc to DB2.

Hi Community: Anyone reading this with some experience on that and the willingness to share?

d2b · November 30, 2019, 9:34pm

Connecting to DB2 via the SQLAlchemy dialect ibm_db_sa works just fine. The issue is that it cannot find the users table, because there is no field in privacyIdea sqlresolver where to specify the schema.
Even for a standard supported dialect like mysql, I don’t see a schema option in privacyIdea.

PS: Hardcoding the schema in SQLIdResolver.py fixes the issue

plettich · December 2, 2019, 11:11am

What do You mean by schema? Since privacyIDEA does not know the table setup, there is the Mapping field that defines which column in the specified table maps to which attribute in privacyIDEA.

d2b · December 3, 2019, 4:49pm

@plettich I’m referring to Database schema. A database can have multiple schemas. Specifying the table name as “schema.table” doesn’t seem to work (at least for db2)

cornelinux · December 3, 2019, 5:06pm

@d2b honestly we did not encounter this. SQL Resolvers are not used as often as ldap resolvers. Also it looks like other database types are more often used (like mysql, having the schema=database-name) or maybe in some cases there is a default schema.

We could set the schema after the SQLSoup call.

github.com

privacyidea/privacyidea/blob/master/privacyidea/lib/resolvers/SQLIdResolver.py#L549


    # get an engine from the engine registry, using self.getResolverId() as the key,
    # which involves the connect string and the pool settings.
    self.engine = get_engine(self.getResolverId(), self._create_engine)
    # We use ``scoped_session`` to be sure that the SQLSoup object
    # also uses ``self.session``.
    Session = scoped_session(sessionmaker(bind=self.engine))
    # Session should be closed on teardown
    self.session = Session()
    register_finalizer(self.session.close)
    self.session._model_changes = {}
    self.db = SQLSoup(self.engine, session=Session)
    self.db.session._model_changes = {}
    self.TABLE = self.db.entity(self.table)


    return self


def _create_engine(self):
    log.info(u"using the connect string "
             u"{0!s}".format(censor_connect_string(self.connect_string)))
    try:
        log.debug("using pool_size={0!s}, pool_timeout={1!s}, pool_recycle={2!s}".format(

self.db = SQLSoup(self.engine, session=Session)
self.db.schema = self.schema

@d2b If this looks sensible to you, would you mind opening an issue at github? So you will be fine with updates in the future.