PAM revisited / PAM revisitado

This article is written in English and Portuguese (original version here)
Este artigo está escrito em Inglês e Português (versão original aqui)

English version:

Introduction
I covered PAM authentication in Informix a few years ago. I'd say most of what is written in those articles is still true. But I recently had to run some tests due to a customer situation, that may render some previous facts as not true anymore. To be more specific, I believe there is the generalized idea that implicit connections are hard or impossible to setup using PAM. This article will show that's not true. Nevertheless there are a few considerations to keep in mind.
In a recent customer situation we faced the following issue. The customer is implementing a third party product that will act as a proxy for the Informix connections. The product is being installed on the database servers and remote clients connect to a TCP port under control of this software (let's imagine 1500 on external TCP interface). The software will make a connection to the real Informix port (let's assume 1501 on localhost). The customer is using implicit (trusted) and explicit (password) connections. For explicit connections this architecture doesn't raise any issue. But for implicit connections there was (3rd party provider apparently already solved it) a big security issue. Informix checks the trust relation against the socket origin. But now, instead of the remote client, the socket is originating in the database server itself by the 3rd party application. So, every implicit connection was being trusted by Informix, independently of the real origin.
This led me to do some tests with PAM. As you know PAM (Plugin Authentication Modules) is a framework that allows applications to use different authentication modules to accomplish the authentication of a user. Different platforms come with different modules already pre-installed. What I'm going to demonstrate was tested with Linux and AIX. But it should work with any other operation system that implements PAM (HP-UX and Solaris). But the catch is that one of the modules may not be installed by default. In the worst case scenario you'd need to grab the module source code and adapt it to your platform, and compile. That work is outside the scope of this article.

Goal
So, what I was trying to achieve was the simulation of the default Informix authentication but using PAM. By default, and depending on the $INFORMIXSQLHOSTS configuration, Informix accepts both password and trusted connections on the TCP ports. In order to achieve that I need to setup a PAM service that uses two different mechanisms. One for password and the other for trusts. The module for password is easy. I think every OS has one to do that. In Linux it's pam_unix.so, in AIX is pam_aix etc. To get the trusted hosts functionality we need another module, and this one will be pam_rhosts.so (on Linux) and pam_rhosts_auth on AIX. I couldn't find a ready module for HP-UX to do this, but it's possible to adapt the module from Linux. There are some changes that must be done, because Linux-PAM has some extensions over the standard PAM framework.
Once we identify the modules we need to define how to set them up.

Configuration
As mentioned in the goal section, the idea is to configure a PAM listener that will accept both types of connections. If you check on previous articles you'll find that PAM modules can be configured with a module type and a control flag.
The module type can be auth, account, session and password.
I explained also that Informix only uses auth and account. For each type we can specify more than one module. Auth module type will check the user identity (is the password correct, is the host trusted) and account will check if the user can access the service, if the user account and/or password is valid and so on.
The control flag defines the role of the module in the overall module stack. Since each module type can use more than one module, we should specify for each module if it's return is critical or not for the overall result. The options we can use are required, requisite, sufficient and optional. LinuxPAM is more feature reach, but you should be able to get information about all these by checking the pam or pam.conf manual on your system.
So, we already know we'll need two modules, the module type, the control flags and finnaly we'll need a service name. The service name is whatever we choose, and makes the bridge between Informix configuration and the PAM framework.
I'll start the configuration in the Informix level and I'll create a new listener port for this. I'll use the name cheetah_pam as the INFORMIXSERVER (DBSERVERALIAS to be more exact). As such I need a new entry in $INFORMIXSQLHOSTS:

cheetah_pam  onsoctcp     primary 1533 s=4,pam_serv=(pam_informix),pamauth=(password)

So, I'm using "pam_informix" as the service name and I'm defining the PAM Informix option "password" as the pamauth option. The values allowed here are "password" and "challenge".
"password" should be used by services where the clients connect to using a user and password and challenge for anything else. But as we shall see, there's more to it than what's explained in the manual
So, after adding it to the $INFORMIXSQLHOSTS we should also add the new name (cheetah_pam) to $INFORMIXDIR/etc/$ONCONFIG option DBSERVERALIAS.

Next step it to configure the PAM stack for this service. This is a bit different depending if you're on Linux, AIX or others. Basically Linux uses a file for each service in /etc/pam.d. So we would create a file named /etc/pam.d/pam_informix. This file would contain several lines specifying the module type, the control flag, the module location and the module flags.
On other systems like AIX, there is only one file, called /etc/pam.conf and the service name is added as the first column in the file. Although these two ways are very similar, I tend to like better the way Linux works. Why? Because either /etc/pam.conf or /etc/pam.d/* are controlled by root and a sysadmin will not grant you, the Informix DBA, control over the /etc/pam.conf file. But he may allow you to control a single file (called pam_informix) inside the /etc/pam.d folder.
Anyway.... Given what I mentioned above, this is the service configuration:
auth        sufficient  pam_rhosts.so debug
auth        sufficient  pam_unix.so
account     required    pam_unix.so

What am I saying here? As you can see, the "auth" module type included two modules, with the control flag "sufficient". This means that either of them is enough to make the stack accept the identity. If one fails and the other is ok that's enough. If both fail than the auth type will fail.
As for the account, we're using the module pam_unix.so which validates if the account is valid (not blocked etc.). For AIX the following lines would have to be added for /etc/pam.conf
pam_informix auth      sufficient /usr/lib/security/pam_rhosts_auth debug
pam_informix auth      sufficient /usr/lib/security/pam_aix
pam_informix account   sufficient /usr/lib/security/pam_aix

The differences are that we added the service name as the first column, and the modules names are slightly different.

Testing
I have run several tests to check this implementation. I used a virtual machine where I'm running the cheetah_pam service and used the native OS as a client. My client hostname is "PTxxxx" and I created a user with the same name on the Linux box. I also setup an Informix instance on an AIX machine and  used the Linux server as a client in order to test 4GL connectivity.
First scenario is Informix instance on Linux and client on PC (windows). I have two SQL files:
C:\Programas\InformixClientSDK_370_tc7_c86>type *.sql

test_explicit.sql

CONNECT TO 'stores' USER 'PTxxxxxx' USING 'mypassword';
SELECT USER FROM sysmaster:sysdual;

test_implicit.sql

CONNECT TO 'stores';
SELECT USER FROM sysmaster:sysdual;

C:\Programas\InformixClientSDK_370_tc7_c86>

Let's see what happens when I run these both tests:
C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_implicit.sql

 1809: Server rejected the connection.
Error in line 1
Near character position 1

C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_explicit.sql

Connected.

(expression)

PTxxxxxx
1 row(s) retrieved.

Disconnected.

C:\Programas\InformixClientSDK_370_tc7_c86>

Ok. Implicit connections are failing, but user and password are working. This is expected because I didn't create the trusted relation on the Linux machine. Let's check the syslog on that machine:
tail -20 /var/log/secure
[...]
Sep  4 06:52:07 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:07 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:09 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:09 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:12 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:12 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:14 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:14 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:17 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:17 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:32 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx

These lines belong to the implicit attempt and the explicit one (last line). As you can see, for the implicit attempt both modules report failure, but for the explicit attempt only the pam_rhosts reports a failure, because the pam_unix succeeded (and since we used "sufficient", that's enough to accept the connection.
Now, let's configure the trust relation. For that I'll try to add "PTxxxxxx   PTxxxxxx" to /etc/hosts.equiv. Let's repeat the test:
C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_implicit.sql

Connected.

(expression)

PTxxxxxx

1 row(s) retrieved.

Disconnected.

C:\Programas\InformixClientSDK_370_tc7_c86>

and now, I'll remove that line from /etc/hosts.equiv and I'll add it to ~PCxxxxxx/.rhosts
As expected:
C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_implicit.sql

Connected.

(expression)

PCxxxxxx

1 row(s) retrieved.

Disconnected.

C:\Programas\InformixClientSDK_370_tc7_c86>

A final test with this setup: Let's remove the trust and try with a wrong password (changed the test_explicit.sql file):
C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_explicit.sql

 1809: Server rejected the connection.
Error in line 1
Near character position 1

C:\Programas\InformixClientSDK_370_tc7_c86>

So, this proves that with dbaccess we can emulate the native behavior but using PAM. But how about for other connectivity layers, like ODBC, .NET, 4GL etc.?
Well... I tried everything I could think off and only OleDB failed:

• ConnectTestDemo (ok)
  (connectivity test bundled with ClientSDK for windows
• ODBC (ok)
  (normal ODBC manager bundled with Windows)
• VBScript that uses ODBC data source (ok)
• VBScript that uses OleDB (failed!)
• C# client that uses .NET (ok)
• Native 4GL 7.50.UC4 (ok)

As mentioned above, OleDB client failed, because it explicitly does not support PAM. In fact when I try to use it I get the following message in the online.log:

10:35:24  listener-thread: err = -1809: oserr = 0: errstr = Client without PAM support connecting to PAM port: Server rejected the connection.

So this is a known product limitation. I believe this could be removed if we just assumed it would work just for password, and not for challenge. We did that very recently for DRDA and PAM. I suspect, but I cannot be sure, that the limitation comes from the fact that in OleDB clients we can't specify a callback function as we do in JDBC or ESQL/C for example. And that may be the source of this limitation. It would be better to support just the use of password if that's the case.

Conclusions and caveats
This little exercise proves that we can simulate (with some differences mentioned below) that we can simulate the engine native behavior while using PAM. But why would we want to do that? Well, I can list a few different motives:

1. We may want the user/password to validate against a repository different than the native OS (or simply change the encryption mechanism and Informix may be sensitive to that)
2. We may setup the OS to work with LDAP, and although it may work for OS native utilities, it may turn the way Informix validates the users in the OS unusable. Informix uses getpwnam() and crypt(). If these two functions don't work exactly the same way after LDAP configuration, the Informix native authentication will fail.
3. We may want to take advantage of the flexibility of PAM. After setting up these two modules in the PAM configuration there is nothing that prevents us from introducing other modules that implement extra functionality. The only thing to consider is that we can't send challenges to the application from those modules because we setup the pamauth to "password" instead of "challenge"

But as mentioned above, there are a few issues:

1. When using native authentication, if we provide a password, it will validate the password or refuse the connection. In this example, if the password fails, it will try to validate the trust relation. I don't think this is a big issue... typically trust relations are created for users/servers that don't use passwords. And passwords are created for scenarios where the trust relation is not created
2. By configuring the port with pamauth=(password) we give up the ability to use any module that sends a challenge that we would reply with a function setup with the callback mechanism
3. The pam_rhosts module checks the trusts in the files /etc/hosts.equiv and ~utilizador/.rhosts
   As we know, starting with version 11.70, Informix can (and should) use it's own files. It would be possible, and not too complex I believe, to get the source code and change the references to these files (accepting the new ones as module parameters for example) to adapt it to version 11.7
4. On Linux (and this didn't happen on AIX and may not happen on other Linux versions), the name passed by PAM_RHOST to the module pam_rhosts must be resolvable to an IP address. Otherwise the authentication in this module will fail

Versão Portuguesa:
Introdução
Eu abordei a a autenticação via PAM no Informix há uns anos atrás. Diria que a maioria do que está nesses artigos ainda se aplica. Mas recentemente tive de efetuar alguns testes, devido a uma situação num cliente, que podem tornar falsas algumas assunções anteriores. Para ser mais específico, acredito que exista a ideia generalizada que é impossível efetuar ligações implícitas usando PAM (por exemplo em 4GL). Este artigo irá mostrar que isso é falso. Há no entanto algumas considerações a ter em conta.
Numa situação recente ocorrida num cliente enfrentámos a seguinte situação: O cliente está a implementar um produto de terceiros que irá atuar como proxy para as ligações Informix. O produto está a ser instalado nos servidores de bases de dados e os clientes ligam-se a um porto TCP controlado por esse software (imagine o porto 1500 numa interface "externa"). O referido software irá fazer a conexão ao porto real do Informix (imagine 1501 na interface localhost). O cliente está a usar ligações implícitas (trusted connections) e explícitas (utilizador/password). Para ligações explícitas esta arquitetura não levanta qualquer questão. Mas para conexões implícitas existia (aparentemente já terá sido resolvido pelo fornecedor) um grave problema de segurança. O Informix verifica as relações de confiança (trusts) com base na origem do socket estabelecido pelo cliente. Mas agora, em vez de a origem ser a máquina clientes, a origem era a própria máquina da base de dados, feita pelo software que atua como proxy. Assim, todas as tentativas de conexão implícita estavam a ser consideradas como trusted independentemente da origem real.
Isto levou-me  a fazer alguns testes com PAM. Como saberá, o PAM é uma framework que permite às aplicações usar diferentes módulos de autenticação para efetuar a autenticação dos utilizadores. Plataformas diferentes trazem já módulos pré-instalados diferentes. O que vou demonstrar foi testado em Linux e AIX. Mas deverá funcionar com outros sistemas operativos que suportam PAM (HP-UX e Solaris). O problema é que um dos módulos poderá não estar instalado nas configurações base, ou no pior cenário pode não estar disponível para instalação. Nessa situação poderá ser necessário portar o módulo de Linux e adaptar o código fonte. Esse processo está fora do âmbito deste artigo.

Objectivo
Bem, o que se pretende alcançar é a emulação da autenticação nativa do Informix, mas usando PAM. Por omissão, mas dependendo da configuração das opções no $INFORMIXSQLHOSTS, o Informix aceita tanto ligações com utilizador e password como ligações implícitas. Para conseguir o que pretendo com PAM, necessito de configurar um serviço PAM que utilize dois mecanismos diferentes. Um para as relações de confiança e outro para a autenticação mais clássica com utilizador e password. O módulo PAM para utilizador e password é o mais fácil pois existe em todos os sistemas. Em Linux será o pam_unix e em AIX será o pam_aix. Para conseguir a funcionalidade das relações de confiança será o pam_rhosts (Linux) e pam_rhosts_auth (AIX). Não consegui encontrar este módulo para HP-UX, mas é possível adaptar a partir da versão para Linux. Existem algumas alterações que têm de ser feitas, pois o PAM em Linux tem algumas extensões ao PAM standard.
Após identificarmos os módulos temos de definir a sua configuração.

Configuração
Conforme mencionado na secção anterior, a ideia é configurar um listener usando PAM que aceite ambos os tipos de conexões. Se verificar os artigos anteriores, verifica que os módulos PAM podem ser configurados com um tipo e flag de controlo. O tipo pode ser auth, account, session ou password.
Foi também explicado que o Informix só utiliza o account e auth. Para cada tipo podemos especificar mais que um módulo. O tipo auth irá servir para verificar a identidade do utilizador (se a password está correta, se o par máquina/utilizar está trusted etc.) e o tipo account irá validar se a conta está válida etc.
A flag de controlo define o papel de cada módulo no resultado final da pilha de módulos para o mesmo tipo. Como cada tipo pode conter mais que um módulo, conseguimos assim definir por módulo de que forma o seu resultado afeta o resultado final. As opções que podemos usar são requisite, required, sufficient e optional. LinuxPAM é mais rico nas opções, mas irei manter-me no standard. Pode obter mais informações sobre tudo isto consultando as páginas do manual referentes a "pam" e "pam.conf" no seu sistema.
Portanto, já sabemos que necessitamos de módulos, tipo de módulos, flag de controlo e finalmente vamos precisar de um nome de serviço. O nome de serviço é o que quisermos usar e serve como ponte entre a configuração no Informix e no sistema de PAM.
Vou começar com a configuração de Informix e para tal vamos criar um novo porto para um listener. Vou usar o nome "cheetah_pam" como INFORMIXSERVER (ou para ser mais exato como DBSERVERALIAS). Nestes termos, necessitamos de uma nova entrada em $INFORMIXSQLHOSTS:
cheetah_pam  onsoctcp     primary 1533 s=4,pam_serv=(pam_informix),pamauth=(password)

Estou a usar "pam_informix" como nome de serviço e estou a utilizar o tipo de autenticação "password" na opção "pamauth". Os valores possíveis são "password" e "challenge".
"password" deveria ser usado em situações onde os clientes estão preparados para enviar a password e "challenge" para as outras situações (que implicariam a configuração de uma função de callback).
Mas como veremos, há algo mais sobre isto que aquilo que está explicado no manual.
Depois de adicionar o novo listener à configuração no $INFORMIXSQLHOSTS é necessário incluir isto também no $INFORMIXDIR/etc/$ONCONFIG, no parâmetro DBSERVERALIAS.

O próximo passo é configurar o stack PAM para este serviço. Isto é feito de forma ligeiramente diferente conforme estamos em Linux, AIX ou outros. Basicamente em Linux é usado um ficheiro com o nome do serviço criado em /etc/pam.d. Assim criaremos um ficheiro /etc/pam.d/pam_informix. Este ficheiro deverá conter várias linhas indicando o tipo, a flag de controlo, o nome do módulo e respetivos argumentos.
Em outros sistemas como AIX, existe apenas um ficheiro com o nome /etc/pam.conf e o nome de serviço é adicionado como primeira coluna desse ficheiro. Apesar de ambas as formas serem bastante semelhantes, julgo preferir a forma usada em Linux. Porquê? Porque tanto o /etc/pam.conf como os /etc/pam.d/* são controlados por root e um administrador se sistema não irá conceder-lhe a si, administrador de Informix, controlo sobre o ficheiro /etc/pam.conf. Mas poderá conceder controlo sobre um serviço (ficheiro - pam_informix) dentro do diretório /etc/pam.d
Enfim... Dado o que referi acima, esta será a configuração do serviço:
auth      sufficient  pam_rhosts.so debug
auth      sufficient  pam_unix.so
account   required    pam_unix.so

O que estou aqui a dizer? Como se pode ver, o tipo auth incluí dois módulos, ambos usando a flag de controlo sufficient. Isto significa que qualquer deles são suficientes para fazer o stack aceitar a autenticação. Se um falhar e o outro validar será suficiente. Se ambos falharem, então a autenticação falha.
Em relação ao account, estamos a usar o módulo pam_unix que valida se a conta é válida (não está bloqueada etc.). Para AIX as linhas seguintes teriam de ser adicionadas ao /etc/pam.conf:
pam_informix auth    sufficient /usr/lib/security/pam_rhosts_auth debug
pam_informix auth    sufficient /usr/lib/security/pam_aix
pam_informix account sufficient /usr/lib/security/pam_aix

TA diferença é que adicionámos o nome do serviço como primeira coluna do ficheiro, e os módulos são ligeiramente diferentes no nome.

Testes
Executei vários testes para validar esta implementação. Utilizei uma máquina virtual onde estou a correr a instância onde adicionei o listener cheetah_pam, e utilizei o SO nativo como cliente. O nome do meu cliente é "PTxxxxxx" e criei um utilizador com o mesmo nome no ambiente Linux. Também configurei uma instância numa máquina AIX e usei o Linux como cliente para executar alguns testes com conectividade 4GL.
O primeiro cenário é a instância Informix no Linux e o cliente no PC (Windows). Tenho dois ficheiros SQL:
C:\Programas\InformixClientSDK_370_tc7_c86>type *.sql

test_explicit.sql

CONNECT TO 'stores' USER 'PCxxxxxx' USING 'mypassword';
SELECT USER FROM sysmaster:sysdual;

test_implicit.sql

CONNECT TO 'stores';
SELECT USER FROM sysmaster:sysdual;

C:\Programas\InformixClientSDK_370_tc7_c86>

Vejamos o que acontece quando executo ambos os scripts:
C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_implicit.sql

 1809: Server rejected the connection.
Error in line 1
Near character position 1

C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_explicit.sql

Connected.

(expression)

PTxxxxxx
1 row(s) retrieved.

Disconnected.

C:\Programas\InformixClientSDK_370_tc7_c86>

Ok.As ligações implícitas estão a falhar, mas as que usam utilizador e password estão a funcionar. Isto é esperado porque não criei as relações de confiança na máquina Linux. Verifiquemos o syslog dessa máquina:
tail -20 /var/log/secure
[...]
Sep  4 06:52:07 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:07 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:09 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:09 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:12 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:12 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:14 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:14 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:17 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxx
Sep  4 06:52:17 primary oninit: pam_unix(pam_informix:auth): authentication failure; logname= uid=1002 euid=0 tty= ruser=PTxxxxxx rhost=192.168.142.1  user=PTxxxxxx
Sep  4 06:52:32 primary oninit: pam_rhosts(pam_informix:auth): denied access to PTxxxxxx@192.168.142.1 as PTxxxxxxAs primeiras pertencem à tentativa implícita e a última à explícita. Como pode ver, para a ligação implícita ambos os módulos reportam falha na autenticação, mas para a explícita apenas o pam_rhosts reporta falha. O pam_unix teve sucesso e como usamos sufficient isso basta para aceitar a conexão.
Agora vamos configurar a relação de confiânça. Para tal vou adicionar"PTxxxxxx   PTxxxxxx" ao ficheiro /etc/hosts.equiv.Vamos repetir o teste:

C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_implicit.sql

Connected.

(expression)

PCxxxxxx

1 row(s) retrieved.

Disconnected.

C:\Programas\InformixClientSDK_370_tc7_c86>

e agora vamos remover essa linha do /etc/hosts.equiv e adicioná-la ao ~PTxxxxxx/.rhosts
Conforme esperado:

C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_implicit.sql

Connected.

(expression)

PTxxxxxx

1 row(s) retrieved.

Disconnected.

C:\Programas\InformixClientSDK_370_tc7_c86>

Um teste final com este ambiente: Vamos remover a relação de confiança e tentar com uma password errada (modificando o ficheiro test_explicit.sql file):

C:\Programas\InformixClientSDK_370_tc7_c86>dbaccess - test_explicit.sql

 1809: Server rejected the connection.
Error in line 1
Near character position 1

C:\Programas\InformixClientSDK_370_tc7_c86>

Portanto, isto prova que no dbaccess podemos emular o comportamento nativo usando PAM. Mas e em relação às outras camadas de conectividade, como ODBC, .NET, 4GL etc.?
Bom... Tentei com tudo o que me ocorreu e só o OleDB falhou:

• ConnectTestDemo (ok)
  (ferramenta de conectividade fornecida com o ClientSDK para Windows
• ODBC (ok)
  (o habitual gestor de ODBC fornecido com o Windows)
• VBScript que usa um data source ODBC (ok)
• VBScript que usa OleDB (failhou!)
• Ferramenta Java que usa JDBC (ok)
• Cliente C# que usa  .NET (ok)
• 4GL 7.50.UC4 nativo (ok)

Como referido acima, o cliente OleDB falhou, porque explicitamente não suporta PAM. Na verdade quando se tenta usar obtemos a seguinte mensagem no online.log:

10:35:24  listener-thread: err = -1809: oserr = 0: errstr = Client without PAM support connecting to PAM port: Server rejected the connection.

Portanto isto é declaradamente uma limitação no produto (atualmente). Acredito que isto poderia ser removido se assumirmos que passa a funcionar em modo de password e não em challenge. Fizémos isso muito recentemente para DRDA com PAM. Suspeito, mas não consigo ter a certeza, que a limitação deriva do facto de os clientes OleDB não podere especificar uma função de callback como fazemos em JDBC ou ESQL/C. E isso pode ser a fonte desta limitação. Seria melhor limitar o uso de PAM com OleDB a configurações exclusivamente com password.

Conclusões e problemas
Este pequeno exercício prova que podemos simular (com algumas diferenças mencionadas abaixo) o comportamento nativo usando PAM. Mas porque quereríamos fazer isso? Bom, consigo listar alguns motivos:

1. Podemos querer validar o utilizador/password contra um repositório diferente do SO nativo (ou mais simplesmente mudar o mecanismo de encriptação e o Informix pode ser sensível a isso)
2. Podemos configurar o SO para trabalhar com LDAP, e ainda que isso possa funcionar bem com utilitários nativos, pode quebrar a forma como o Informix valida os utilizadores no SO. O Informix utiliza as funções getpwnam() e crypt(). Se estas funções não funcionarem exatamente da mesma forma que antes da configuração para LDAP a autenticação Informix pode falhar
3. Podemos querer aproveitar a flexibilidade do PAM. Depois de configurar estes dois módulos PAM, não há nada que nos impeça de introduzir outros módulos que forneçam outras funcionalidades. A única coisa que temos de manter em mente é que não podemos aceitar "challenges" desses módulos, dado que configurámos o serviço com a opção pamauth=(password) em vez de "challenge"

Mas como escrevi antes, existem alguns problemas:

1. Ao usar a autenticação nativa, se fornecermos uma password, ira validar a password ou recusar a conexão. Neste exemplo, se a validação cotnra a password falhar vai tentar verificar a relação de confiança. Não me parece que isto seja um grande problema... Normalmente as relações de confiança são criadas para utilizadores que não têm password. E vice-versa, os utilizadores que usam passwords não costumam ter relações de confiança criadas
2. Ao configurar o porto com pamauth=(password) abdicamos da possibilidade de usar algum módulo que envie um challenge, ao qual poderíamos responder com uma função configurada com o mecanismo de callback
3. O módulo pam_rhosts valida as relações de confiança nos ficheiros /etc/hosts.equiv e ~utilizador/.rhosts
   Como sabemos, a partir da versão 11.70 o Informix pode (e deve) usar os seus próprios ficheiros. Seria possível e não demasiado complicado obter o código fonte do módulo, alterar estes ficheiros (aceitando-os como parâmetro do módulo por exemplo) para o adaptar à versão 11.7
4. Em Linux (e isto não aconteceu em AIX e pode não acontecer noutras versões de Linux), o nome que é passado no PAM_RHOST ao módulo pam_rhosts tem de ser passível de resolução para endereço IP ou a autenticação neste módulo falhará

Google's two factor authenticator / Autenticação com 2 factores da Google

This article is written in Portuguese and English (original article here)
Este artigo está escrito em Inglês e Português (artigo original aqui)


English version:

Introduction

It should not be a surprise to anyone that I'm a big fan of PAM (plugable authentication modules). I've written several articles about it in this blog. This time I'll pick up a Google project and show you how to glue it together with Informix to achieve more security for your connections. And this happens thanks to PAM obviously.
Google authenticator is a project that implements one time password (OTP) generators for several mobile platforms. In practice you'll use your smartphone as a token generator. This avoids the need of a specific piece of hardware and has the advantages of being open source and making available a server side PAM library. Meaning you can integrate it with anything that supports PAM. This means your SSH daemon and naturally your favorite database server software.
Google authenticator can be seen and used as a component of a multi-factor authentication mechanism. This implies that a user must present (and I quote) "two or more of the three authentication factors: a knowledge factor ("something the user knows"), a possession factor ("something the user has"), and an inherence factor ("something the user is"). This article will show you how to configure Informix for two factor authentication. In our scenario we'll use a traditional password (something the user knows) and Google's authenticator as the second factor (something the user has). In the future, if the rumors about the introduction of biometric readers (like finger print readers) on mobile phones becomes a reality, it may be possible to extend this to three factors (something the user is).
It's possible that we'll see more services using this kind of technology. Just recently Twitter introduces two factor authentication by sending a request to their app when you try to login in their web site. The user will need to authorize that connection by using the twitter app on an authorized phone. Essentially this implements the same concept, but in an easier way.

The code

To start with this, we need to get the code for the PAM library. We can find it in Google's authenticator website. So I downloaded the package into a Linux VM, and uncompressed it:
[root@kimball google_authenticator]# pwd;ls -lia;tar -xjvf libpam-google-authenticator-1.0-source.tar.bz2
/usr/local/google_authenticator
total 44
1671484 drwxr-xr-x  2 root root  4096 Aug  6 11:43 .
1605654 drwxr-xr-x 14 root root  4096 Aug  6 09:46 ..
 327870 -rw-r--r--  1 root root 32708 Aug  6 11:40 libpam-google-authenticator-1.0-source.tar.bz2
libpam-google-authenticator-1.0/base32.c
libpam-google-authenticator-1.0/demo.c
libpam-google-authenticator-1.0/google-authenticator.c
libpam-google-authenticator-1.0/hmac.c
libpam-google-authenticator-1.0/pam_google_authenticator.c
libpam-google-authenticator-1.0/pam_google_authenticator_unittest.c
libpam-google-authenticator-1.0/sha1.c
libpam-google-authenticator-1.0/base32.h
libpam-google-authenticator-1.0/hmac.h
libpam-google-authenticator-1.0/sha1.h
libpam-google-authenticator-1.0/totp.html
libpam-google-authenticator-1.0/Makefile
libpam-google-authenticator-1.0/FILEFORMAT
libpam-google-authenticator-1.0/README
libpam-google-authenticator-1.0/utc-time/
libpam-google-authenticator-1.0/utc-time/app.yaml
libpam-google-authenticator-1.0/utc-time/utc-time.py

After this, following very simple instructions, I type "make install":

[root@kimball libpam-google-authenticator-1.0]# make install
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o google-authenticator.o google-authenticator.c
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o base32.o base32.c
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o hmac.o hmac.c
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o sha1.o sha1.c
gcc -g   -o google-authenticator google-authenticator.o base32.o hmac.o sha1.o  -ldl
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o pam_google_authenticator.o pam_google_authenticator.c
gcc -shared -g   -o pam_google_authenticator.so pam_google_authenticator.o base32.o hmac.o sha1.o -lpam
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o demo.o demo.c
gcc -DDEMO --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o pam_google_authenticator_demo.o pam_google_authenticator.c
gcc -g   -rdynamic -o demo demo.o pam_google_authenticator_demo.o base32.o hmac.o sha1.o  -ldl
gcc -DTESTING --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden        \
              -o pam_google_authenticator_testing.o pam_google_authenticator.c
gcc -shared -g   -o pam_google_authenticator_testing.so pam_google_authenticator_testing.o base32.o hmac.o sha1.o -lpam
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o pam_google_authenticator_unittest.o pam_google_authenticator_unittest.c
gcc -g   -rdynamic -o pam_google_authenticator_unittest pam_google_authenticator_unittest.o base32.o hmac.o sha1.o -lc  -ldl
cp pam_google_authenticator.so /lib64/security
cp google-authenticator /usr/local/bin
[root@kimball libpam-google-authenticator-1.0]#

and we can verify that the two components were installed correctly:

[root@kimball libpam-google-authenticator-1.0]# ls -lia /lib64/security/pam_google* /usr/local/bin/google*
1212428 -rwxr-xr-x 1 root root 116777 Aug  6 11:50 /lib64/security/pam_google_authenticator.so
1613197 -rwxr-xr-x 1 root root  55498 Aug  6 11:50 /usr/local/bin/google-authenticator
[root@kimball libpam-google-authenticator-1.0]#


Setup

We have the PAM library and the google-authenticator binary that we'll use to generate a secret key for our user. So this is the next step. I'll not do this with user "informix", because the engine will always ignore PAM for informix user when connecting locally. But before, let's check the other fundamental component of the solution: Your smartphone. Currently I use Android, but the app is available to iOS and Blackberry. You should know how to find and install the app. For Android devices it's available on GooglePlay store. The app for Android and iOS supports both reading a QR code or manual introduction of the secret key generated by the google-authenticator binary. For Blackbery, according to the Google Authenticator website, it only supports manual introduction.

Assuming the app is properly installed, we can proceed with the server side configuration. As mentioned above, the next step is to generate the secret key between the server account and the mobile app. For that we use the google-authenticator binary installed before. When we run it it outputs the information needed and also makes some questions. I will not dig into those as you can find out more in the documentation:

-bash-3.2$ google-authenticator

Do you want authentication tokens to be time-based (y/n) y
https://www.google.com/chart?chs=200x200&chld=M|0&cht=qr&chl=otpauth://totp/fnunes@kimball%3Fsecret%3D6LWH24ZIUDY46HJU
Your new secret key is: 6LWH24ZIUDY46HJU
Your verification code is 649019
Your emergency scratch codes are:
  38268905
  24335468
  11497220
  81653596
  89796862

Do you want me to update your "/opt/fnunes/.google_authenticator" file (y/n) y

Do you want to disallow multiple uses of the same authentication
token? This restricts you to one login about every 30s, but it increases
your chances to notice or even prevent man-in-the-middle attacks (y/n) n

By default, tokens are good for 30 seconds and in order to compensate for
possible time-skew between the client and the server, we allow an extra
token before and after the current time. If you experience problems with poor
time synchronization, you can increase the window from its default
size of 1:30min to about 4min. Do you want to do so (y/n) n

If the computer that you are logging into isn't hardened against brute-force
login attempts, you can enable rate-limiting for the authentication module.
By default, this limits attackers to no more than 3 login attempts every 30s.
Do you want to enable rate-limiting (y/n) y
-bash-3.2$

It shows us a URL and the key. If we have a library installed (libqrencode) it will also show a QR code that we can use directly with the phone. If we open the URL in a browser it will show a QR code that we can use in the app to install the key. Otherwise we need to introduce the key manually. Either way it's pretty simple and in a minute you'll have a token (or one time password) generator on your mobile phone:

After this we need to go and configure our informix instance to take advantage of this. For that I'll create a new listener port using PAM. As you probably already know, this is done by altering the $INFORMIXSQLHOSTS file and add a line similar to this:

tpch_pam     onsoctcp             kimball.onlinedomus.net     1527   s=4,pam_serv=(ids_pam_service),pamauth=(challenge)


Field by field:

• tpch_pam
  The INFORMIXSERVER name for this alias
• onsoctcp
  The protocol to be used
• kimball.onlinedomus.net
  The hostname
• 1527
  The unique TCP port number for this listener
• s=4,pam_serv=(ids_pam_service),pamauth=(challenge)
  options field. s=4 forces PAM usage. The PAM service name is "ids_pam_service" and the PAM mode will be "challenge" 

After the $INFORMIXSQLHOSTS we must make sure that the name "ids_pam_service" is configured in the PAM configuration. Being Linux, this means having a file called ids_pam_service in /etc/pam.d. The content of the file will be:

auth       required     pam_unix.so
auth       required     pam_google_authenticator.so
account    required     pam_unix.so

This will be used for our "basic by the book" configuration. Later I'll explain some issues with it, and will show you a better way to use it. As mentioned in other articles we just need "auth" and "account" configuration lines. And we're "stacking" modules (Google and pam_unix) so that we achieve the "two" factor authentication. First we'll test the unix password and then the Google's authenticator token or code (the one time password)

Next we need to make sure "tpch_pam" is configured in the $INFORMIXDIR/etc/$ONCONFIG file in the parameter DBSERVERALIAS and after that we can start the listener with:

onmode -P start tpch_pam


Testing

So, if we have everything setup, it should work. Let's test it with dbaccess:

tpch@kimball:fnunes-> id;hostname;echo $INFORMIXSERVER
uid=1002(fnunes) gid=1002(fnunes) groups=1002(fnunes)
kimball
tpch
tpch@kimball:fnunes-> dbaccess - -
> CONNECT TO 'sysmaster@tpch_pam' user 'fnunes';
   ENTER PASSWORD:
Password:
Verification code:

Connected.

> SELECT USER, * FROM syscfgtab WHERE cf_name LIKE 'DBSERVER%';



(expression)  fnunes
cf_id         2
cf_name       DBSERVERNAME
cf_flags      0
cf_original   tpch
cf_effective  tpch
cf_default    kimball

(expression)  fnunes
cf_id         77
cf_name       DBSERVERALIASES
cf_flags      0
cf_original   tpch_pam
cf_effective  tpch_pam
cf_default

2 row(s) retrieved.

> tpch@kimball:fnunes->

Firts impression is good. It works. But there's something weird. If you take a close look you'll see that once I do a CONNECT, dbaccess asks me for "ENTER PASSWORD:". Then it asks me for "Password:" and finally for "Verification code:". Three prompts... I hope you understand two of them but not the repeated request for password. Let me explain. By default, whenever we try to CONNECT dbaccess will ask for a password. That's the first prompt. Actually, with this configuration (challenge mode) we can enter whatever we want here... it will be ignored. Than we start the PAM stack layer. And the first module is pam_unix.so. Since we're not sending it any password (more about this later) it asks us for the password ("Password:" prompt). Here you have to write the system user password. After that we move to the second module, the Google authenticator module and it behaves the same way. Since it doesn't have a password it asks for one ("Verification code:" prompt). And here we need to take a look into our smartphone and copy the current generated token (or one time password). After that we're logged in. Both passwords were verified (one for each module in the stack).
This behavior justifies the "challenge" mode. The module sends a challenge back to the engine, and the engine send it back to the client (dbaccess in our case) and the client must have registered a callback function to handle the challenges and user responses. Basically what it does in dbaccess is to echo the challenge and read the response, and finally sending it back to the engine which sends it back to the module for verification.
Although it works, it's a bit ackward and needs a client side function to handle the challenges. This means we could possibly have to change the application. dbaccess already knows how to handle it, but other clients wouldn't know what to do.
Informix APIs have functions to register a callback function. But again, code changes are never welcome. So, let's see what we can do...

Improvement

As you probably know (I mentioned it in previous PAM articles), Informix pam can be configured in "password" or "challenge" mode. The documentation sometimes leads us to think that for this kind of usage we need to use challenge. But in fact it really depends on the modules you use and the options they provide. In our case I noticed that Google's authenticator module supports two interesting options:

• try_first_pass
  makes it check the PAM framework for a previously supplied password
• forward_pass
  makes it smart... if you provide a password composed by the concatenation of the system password and the verification token, it will try to split them, verify the code and send the rest through the PAM stack of modules

I also noticed pam_unix.so supports both try_first_pass and use_first_pass. So, what all this suggests is that assuming we have a system password like "mypasswd" and a verification code like "096712" we could use a composed password "mypasswd096712", give it to the dbaccess password request, and don't be bothered by each module prompts. let's change the configuration and test again. The file /etc/pam.d/ids_pam_service becomes this:

auth       required     pam_google_authenticator.so try_first_pass forward_pass
auth       required     pam_unix.so use_first_pass
account    required     pam_unix.so

And in INFORMIXSQLHOSTS we'll change "challenge" for "password":

tpch_pam     onsoctcp             kimball.onlinedomus.net     1527   s=4,pam_serv=(ids_pam_service),pamauth=(password)

And now let's restart the listener and repeat the test:

tpch@kimball:root-> onmode -P restart tpch_pam

tpch@kimball:fnunes-> dbaccess - -
> CONNECT TO 'sysmaster@tpch_pam' user 'fnunes';
   ENTER PASSWORD:

Connected.

> SELECT USER, * FROM syscfgtab WHERE cf_name LIKE 'DBSERVER%';

(expression)  fnunes
cf_id         2
cf_name       DBSERVERNAME
cf_flags      0
cf_original   tpch
cf_effective  tpch
cf_default    kimball

(expression)  fnunes
cf_id         77
cf_name       DBSERVERALIASES
cf_flags      0
cf_original   tpch_pam
cf_effective  tpch_pam
cf_default

2 row(s) retrieved.

> tpch@kimball:fnunes->

And voilá.... The first module now is Google authenticator. It gets the double password from the stack, extracts it's part (it knows it's the last 6 digits), verifies it, and sends the rest to the second module (pam_unix) that validates the password in the system

Now... we've seen that dbaccess does some magic... Because it knows we're dealing with a PAM port. So, to be absolutely sure this is transparent for the applications, let's try an external JDBC connection that has no knowledge that it's a PAM enabled port:

Success!
There not much more we can do. It's basic and very simple to setup. It shows Informix flexibility. And because it's PAM you can of course enrich it with additional modules if you like

Considerations

There are many things to note about this subject. First we could wonder about the usage cases for something like this. A few ideas come to mind:

• added security for privilege users. You could assume that applications only connect trough  a safer network, using normal authentication, but DBSAs may need to connect from the "external" world and it requires added security
• You can use it to construct a "double" password and have part of if available to the application code and let the user introduce the verification code. This would prevent a user to authenticate from outside the application (because the user would never know first password components, but on the other hand an application manager would not be able to impersonate the user even if he got to know the user password)
• Other extended uses could be achieved by tweaking the module code.

It's important to keep in mind that as with any security related component you should consider carefully what you need and discuss the possibilities with security conscious people. I did a sort of brain storming with two ex-Informix DBAs that now work in the security team and some interesting points were raised. Among them, here are a few:

1. This sort of token generator as opposed to specialized hardware like RSA tokens
• Possible advantages of this method:
• You probably notice you lost your smartphone faster than if you lost a specialized token
• It's cheaper
• If the device needs renewal, this method looks simpler (a user can do it once he gets the new phone)
• You don't depend on any external supplier
• Possible disadvantages of this method:
• It seems easier to remotely "hack" a smartphone than to compromise the security of  a specialized hardware token
• The application could provide some security measure to prevent unauthorized access to the generated codes to anybody who has physical access to the phone. Note this also happens on the hardware token, and with a phone you could always protect it with PIN or pattern code. This is not exactly a disadvantage comparing to hardware tokens, but could be something to improve
2.  This would be hard to use for non-interactive processes. Unless, and I believe this could be a possibility, that we work the other way around... Meaning we have the code generator inside the applications servers, and that we setup a callback function to answer the module challenge. This would possibly avoid the usage of the application user from outside the application server environment
3. A generic issue with any two factor authentication mechanism is that ideally the second factor should use a different communication channel from the first. That doesn't happen here, and this allows for man in the middle attacks
4. Another point, which can be related to the one before, is the possibility to use, or not, the same token in a certain time interval (even a short one). The codes generated by Google authenticator are valid by default during 30 seconds (to allow some time for the user to introduce the code and also to compensate for small clock differences between client and server). The module allows that the code before and after the correct one to be used, so the time interval becomes 1m30s. But all this can be configured, and we even have the possibility of not allowing the same code to be used twice. This however will limit the ability of making more than one login each 30 seconds

Issues found

During the preparation and testing for this article I've faced two main issues:

1. On first attempts I got the "Invalid verification code" error from the module. As the documentation says this is usually caused by clock synchronization issues between the server side and the mobile app side. After some checking I've found that I was mixing clock time with timezone offsets and it caused too much difference (the module allows configuration for some small difference)
2. As usual with PAM and PAM modules the hardest part was debugging. Most modules tend to be very quiet about the errors. Not sure why, but on first attempts of concatenating user password with the verification code I was attempting the wrong order (code + password) instead of the proper order (password + code). I ended up looking into the code and changing it to be much more verbose.

Another possible issue is that at the moment, there is no App for Windows phones. And apparently they are grwoing in the market (according to recent studies, they shipped more units than Blackberry)

Acknowledgements

It's not unusual that I discuss some aspects while working on new articles. I recall having some help from several IBM colleagues, some of them already mentioned here once or twice. In this case I had the pleasure to discuss this subject with two ex-Informix DBAs, with whom I worked for several years on a customer team. Now they're working in the security team and the first time I heard about Google authenticator was during a chat with one of them. During the writing of this article and before it was published we interacted a couple of times. Several aspects of the post should be credited to them. So for that, and for the good time we spent working together (we still do occasionally because they have a long history in this customer and there are always subjects where we can work together) a big "Thank you!" to Daniel Valente and Rui Mourão.


Versão Portuguesa:

Introducão 

Não será uma surpresa para ninguém se disser que sou um grande fã do PAM (plugable authentication modules). Já escrevi vários artigos sobre o tema aqui no blog. Desta vez vou partir de um projeto da Google e mostrar como o usar em conjunto com o Informix para aumentar a segurança das conexões. E tudo graças ao PAM naturalmente.
O Google authenticator é um projeto que implementa um gerador de senhas descartáveis (one time passwords - OTP) para várias plataformas móveis. Na prática, transforma um smartphone num gerador se senhas (tokens). Isto evita a necessidade de uma peça física e tem as vantagens de ser open source e disponibilizar um módulo PAM sob a forma de uma biblioteca dinâmica. Isto permite que seja integrável com qualquer sistema que suporte PAM. Tanto pode ser um servidor de SSH como a sua base de dados favorita.
O Google authenticator pode ser visto como um componente de um mecanismo de autenticação multifatorial. Traduzindo, isto significa que o utilizador deve apresentar (e cito traduzindo) "dois ou mais dos três fatores de autenticação: um fator de conhecimento ("algo que o utilizador sabe"), um fator de posse ("algo que o utilizador tem") e um fator de inerência ("algo que o utilizador é"). Este artigo mostrará como configurar o Informix para uma autenticação de dois fatores. No nosso cenário usaremos uma senha tradicional (algo que o utilizador sabe) e o Google authenticator como segundo fator (algo que o utilizador tem). No futuro, se os rumores sobre a introdução de leitores de dados biométricos (impressão digital por exemplo) em dispositivos móveis se tornar uma realidade, poderá ser possível estender para três fatores (algo que o utilizador é).
É possível que vejamos mais serviços a usar este tipo de tecnologia. Ainda recentemente o Twitter introduziu a autenticação de dois fatores, usando o envio de um pedido à sua App quando tenta fazer login no seu site. O utilizador tem de autorizar a ligação usando a aplicação do Twitter num telefone autorizado. Na essência é o mesmo conceito, mas talvez de uma forma ainda mais fácil para o utilizador.

O código

Para começar, necessitamos da biblioteca PAM. Podemos encontrá-la no website do Google authenticator. Fiz a transferência do pacote e coloquei-o numa máquina virtual Linux, tendo de seguida descomprimido:
[root@kimball google_authenticator]# pwd;ls -lia;tar -xjvf libpam-google-authenticator-1.0-source.tar.bz2
/usr/local/google_authenticator
total 44
1671484 drwxr-xr-x  2 root root  4096 Aug  6 11:43 .
1605654 drwxr-xr-x 14 root root  4096 Aug  6 09:46 ..
 327870 -rw-r--r--  1 root root 32708 Aug  6 11:40 libpam-google-authenticator-1.0-source.tar.bz2
libpam-google-authenticator-1.0/base32.c
libpam-google-authenticator-1.0/demo.c
libpam-google-authenticator-1.0/google-authenticator.c
libpam-google-authenticator-1.0/hmac.c
libpam-google-authenticator-1.0/pam_google_authenticator.c
libpam-google-authenticator-1.0/pam_google_authenticator_unittest.c
libpam-google-authenticator-1.0/sha1.c
libpam-google-authenticator-1.0/base32.h
libpam-google-authenticator-1.0/hmac.h
libpam-google-authenticator-1.0/sha1.h
libpam-google-authenticator-1.0/totp.html
libpam-google-authenticator-1.0/Makefile
libpam-google-authenticator-1.0/FILEFORMAT
libpam-google-authenticator-1.0/README
libpam-google-authenticator-1.0/utc-time/
libpam-google-authenticator-1.0/utc-time/app.yaml
libpam-google-authenticator-1.0/utc-time/utc-time.py

Depois disso, seguindo instruções muito simples basta fazer "make install":

[root@kimball libpam-google-authenticator-1.0]# make install
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o google-authenticator.o google-authenticator.c
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o base32.o base32.c
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o hmac.o hmac.c
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o sha1.o sha1.c
gcc -g   -o google-authenticator google-authenticator.o base32.o hmac.o sha1.o  -ldl
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o pam_google_authenticator.o pam_google_authenticator.c
gcc -shared -g   -o pam_google_authenticator.so pam_google_authenticator.o base32.o hmac.o sha1.o -lpam
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o demo.o demo.c
gcc -DDEMO --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o pam_google_authenticator_demo.o pam_google_authenticator.c
gcc -g   -rdynamic -o demo demo.o pam_google_authenticator_demo.o base32.o hmac.o sha1.o  -ldl
gcc -DTESTING --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden        \
              -o pam_google_authenticator_testing.o pam_google_authenticator.c
gcc -shared -g   -o pam_google_authenticator_testing.so pam_google_authenticator_testing.o base32.o hmac.o sha1.o -lpam
gcc --std=gnu99 -Wall -O2 -g -fPIC -c  -fvisibility=hidden  -o pam_google_authenticator_unittest.o pam_google_authenticator_unittest.c
gcc -g   -rdynamic -o pam_google_authenticator_unittest pam_google_authenticator_unittest.o base32.o hmac.o sha1.o -lc  -ldl
cp pam_google_authenticator.so /lib64/security
cp google-authenticator /usr/local/bin
[root@kimball libpam-google-authenticator-1.0]#

e podemos verificar que os dois componentes foram instalados corretamente:

[root@kimball libpam-google-authenticator-1.0]# ls -lia /lib64/security/pam_google* /usr/local/bin/google*
1212428 -rwxr-xr-x 1 root root 116777 Aug  6 11:50 /lib64/security/pam_google_authenticator.so
1613197 -rwxr-xr-x 1 root root  55498 Aug  6 11:50 /usr/local/bin/google-authenticator
[root@kimball libpam-google-authenticator-1.0]#


Configuração

Temos a biblioteca PAM e o binário google-authenticator que iremos usar para gerar uma chave secreta para cada utilizador. Portanto este será o próximo passo. Não irei usar o utilizador "informix" porque o motor ignora sempre o PAM para conexões locais desse utilizador. Mas antes vamos apenas verificar o outro componente fundamental da solução: O seu smartphone. Atualmente utilizo Android, mas a aplicação está também disponível para iOS e Blackberry. Deverá saber como encontrar e instalar a App. Para dispositivos Android está disponível na loja Google Play. A App para Android e iOS suporta a introdução manual da chave secreta bem como a leitura de um código QR. Para Blackberry, segundo o website apenas suporta a introdução manual.
Assumindo que a App está devidamente instalada, podemos prosseguir com a configuração do lado do servidor. Como mencionado anteriormente o próximo passo é a geração da chave secreta que será partilhada entre o servidor e a aplicação móvel. Para isso usamos o binário google-authenticator instalado acima. Quando o executamos mostra a informação necessária e coloca também algumas questões. Não me vou debruçar sobre as mesmas, pois a documentação tem explicações completas:
-bash-3.2$ google-authenticator

Do you want authentication tokens to be time-based (y/n) y
https://www.google.com/chart?chs=200x200&chld=M|0&cht=qr&chl=otpauth://totp/fnunes@kimball%3Fsecret%3D6LWH24ZIUDY46HJU
Your new secret key is: 6LWH24ZIUDY46HJU
Your verification code is 649019
Your emergency scratch codes are:
  38268905
  24335468
  11497220
  81653596
  89796862

Do you want me to update your "/home/fnunes/.google_authenticator" file (y/n) y

Do you want to disallow multiple uses of the same authentication
token? This restricts you to one login about every 30s, but it increases
your chances to notice or even prevent man-in-the-middle attacks (y/n) n

By default, tokens are good for 30 seconds and in order to compensate for
possible time-skew between the client and the server, we allow an extra
token before and after the current time. If you experience problems with poor
time synchronization, you can increase the window from its default
size of 1:30min to about 4min. Do you want to do so (y/n) n

If the computer that you are logging into isn't hardened against brute-force
login attempts, you can enable rate-limiting for the authentication module.
By default, this limits attackers to no more than 3 login attempts every 30s.
Do you want to enable rate-limiting (y/n) y
-bash-3.2$

Como vemos mostra tanto um URL como a chave. Se tivermos uma biblioteca instalada (libqrencode) irá mostrar logo um código QR que podemos usar directamente com o telefone.
Se abrirmos o URL num browser a página obtida mostrará o mesmo código QR, que podemos usar na aplicação móvel para instalar a chave secreta. Alternativamente podemos introduzi-la manualmente. Qualquer das formas é bastante fácil e num instante terá um gerador de senhas descartáveis no seu telefone móvel:

Depois disto temos de configurar a nossa instância Informix para tirar proveito do mecanismo. Para tal irei criar um listener que use PAM. Isto é feito adicionando uma linha semelhante a esta ao $INFORMIXSQLHOSTS:

tpch_pam     onsoctcp             kimball.onlinedomus.net     1527   s=4,pam_serv=(ids_pam_service),pamauth=(challenge)

Campo por campo:

• tpch_pam
  O INFORMIXSERVER para este alias
• onsoctcp
  O protocolo de comunicação a usar
• kimball.onlinedomus.net
  O nome da máquina
• 1527
  O porto TCP único que será usado por este listener
• s=4,pam_serv=(ids_pam_service),pamauth=(challenge)
  Campos de opções: s=4 indica a utilização de PAM. O nome do serviço PAM será "ids_pam_service" e o modo de funcionamento PAM será "challenge" 

Depois de alterado o $INFORMIXSQLHOSTS  temos de configurar um serviço PAM na máquina com o nome "ids_pam_service". Sendo Linux isto é feito criando um ficheiro com o mesmo nome em /etc/pam.d/ cujo conteúdo será:

auth       required     pam_unix.so
auth       required     pam_google_authenticator.so
account    required     pam_unix.so

Isto será usado para a nossa configuração "básica seguindo as regras". Mais tarde vou explicar alguns problemas deste método e mostrarei uma melhor forma de configurar. Como já mencionei noutros artigos apenas precisamos das linhas de configuração para "auth" e "account". Estamos a "empilhar módulos" (Google e pam_unix) para que tenhamos os "dois" fatores de autenticação. Primeiro iremos testar a senha Unix e depois o código de verificação Google (a senha descartável)

De seguida temos de garantir que "tpch_pam" está configurado no ficheiro $INFORMIXDIR/etc/$ONCONFIG no parâmetro DBSERVERALIAS e após isso podemos iniciar o listener com:

onmode -P start tpch_pam


Testes

Portanto, se tudo estiver bem configurado deverá funcionar. Vamos testar com o dbaccess:

tpch@kimball:fnunes-> id;hostname;echo $INFORMIXSERVER
uid=1002(fnunes) gid=1002(fnunes) groups=1002(fnunes)
kimball
tpch
tpch@kimball:fnunes-> dbaccess - -
> CONNECT TO 'sysmaster@tpch_pam' user 'fnunes';
   ENTER PASSWORD:
Password:
Verification code:

Connected.

> SELECT USER, * FROM syscfgtab WHERE cf_name LIKE 'DBSERVER%';



(expression)  fnunes
cf_id         2
cf_name       DBSERVERNAME
cf_flags      0
cf_original   tpch
cf_effective  tpch
cf_default    kimball

(expression)  fnunes
cf_id         77
cf_name       DBSERVERALIASES
cf_flags      0
cf_original   tpch_pam
cf_effective  tpch_pam
cf_default

2 row(s) retrieved.

> tpch@kimball:fnunes->

A primeira impressão é boa. Funciona. Mas há algo estranho. Se olharmos com cuidado vemos que assim que fazemos o CONNECT, o dbaccess pede-me para "ENTER PASSWORD:". Depois pede "Password:" e finalmente pede "Verification code:". Três pedidos... Espero que dois deles sejam óbvios, mas não o pedido de password repetido. Deixe-me explicar. Por omissão, sempre que tentamos um CONNECT o dbaccess pede uma senha. Esse é o primeiro pedido. Na verdade, com esta configuração (challenge mode) podemos introduzir o que quisermos... será ignorado. Depois entramos na pilha de módulos PAM. E o primeiro módulo que temos configurado é o pam_unix.so. Como não lhe estamos a passar nenhuma senha "internamente" (mais sobre isto adiante), ele pede-nos uma senha (pedido "Password:"). Aqui temos de inserir a senha de sistema do utilizador. Depois passamos para o segundo módulo, o do Google authenticator, que se comporta da mesma forma. Como não lhe é disponibilizada uma senha pede-a (pedido "Verification code:"). Nesta fase temos de olhar para o smartphone e transcrever o número que foi gerado para esta conta (a nossa senha descartável). Após isto, completa-se o login. Ambas as senhas foram validadas (uma por cada módulo).
Este comportamento justifica o challenge mode. O(s) módulo envia um desafio ao motor que o encaminha para o cliente (no nosso caso o dbaccess) e o cliente tem de ter uma função para lidar com estes desafios e as respostas do utilizador. Essa função designa-se por função de callback. No caso do dbaccess a função genérica apenas recebe o desafio, imprime-o, lê a resposta do utilizador e envia-a para o motor que depois responde ao módulo para verificação.
Embora isto funcione, é um pouco estranho e necessita que o cliente tenha uma função que lide com os desafios. Isto quer dizer que poderá ser necessário alterar a aplicação. O dbaccess já sabe o que fazer, mas outros clientes não saberiam.
As APIs do Informix têm funções para registar a função de callback. Mas de qualquer forma, alterações nunca são bem vindas... Vejamos o que podemos fazer.

Melhorias

Como já saberá (já o mencionei noutros artigos sobre PAM), o Informix pode ser configurado em modo "password" ou "challenge". A documentação por vezes leva-nos a pensar que para este tipo de implementação temos de usar challenge. Mas na realidade isto depende dos módulos que vamos usar e das opções que disponibilizam. No nosso caso verifiquei que o Google authenticator suporta duas opções interessantes:

• try_first_pass
  obriga-o a verificar na infra-estrutura PAM se foi passada anteriormente uma senha
• forward_pass
  torna-o inteligente... Se fornecermos uma senha que seja a concatenação da senha de sistema com o código de verificação, o módulo vai separá-los, verifica a parte dele, e passa o resto para os módulos seguintes

Notei igualmente que o módulo pam_unix.so também suporta as opções try_first_pass e use_first_pass. Portanto o que tudo isto sugere é que se tivermos uma senha de sistema "MinhaPass" e um código como "096712" poderia-mos usar uma senha composta "MinhaPass096712", dá-la como senha ao dbaccess e não nos preocupamos mais com os pedidos (desafios) de cada módulo. Vamos mudar a configuração e testar novamente. O ficheiro /etc/pam.d/ids_pam_service passa a ter:

auth       required     pam_google_authenticator.so try_first_pass forward_pass
auth       required     pam_unix.so use_first_pass
account    required     pam_unix.so

E no ficheiuro $INFORMIXSQLHOSTS mudamos "challenge" para "password":

tpch_pam     onsoctcp             kimball.onlinedomus.net     1527   s=4,pam_serv=(ids_pam_service),pamauth=(password)

E vamos re-iniciar o listener e testar:

tpch@kimball:root-> onmode -P restart tpch_pam

tpch@kimball:fnunes-> dbaccess - -
> CONNECT TO 'sysmaster@tpch_pam' user 'fnunes';
   ENTER PASSWORD:

Connected.

> SELECT USER, * FROM syscfgtab WHERE cf_name LIKE 'DBSERVER%';

(expression)  fnunes
cf_id         2
cf_name       DBSERVERNAME
cf_flags      0
cf_original   tpch
cf_effective  tpch
cf_default    kimball

(expression)  fnunes
cf_id         77
cf_name       DBSERVERALIASES
cf_flags      0
cf_original   tpch_pam
cf_effective  tpch_pam
cf_default

2 row(s) retrieved.

> tpch@kimball:fnunes->

E voilá....  O primeiro módulo agora passou a ser o Google authenticator. Recebe a senha "dupla" internamente pelo PAM (inicializado pelo motor), extrai a parte que lhe diz respeito (últimos seis caracteres), verifica esse código como senha descartável, e envia o resto para o segundo módulo (pam_unix) que valida a senha no sistema.
Entretanto... nós vimos que o dbaccess faz alguma mágica... Porque sabe que está a lidar com um porto PAM. Para ficarmos absolutamente seguros que este método é transparente para a aplicação, vamos tentar uma ligação externa com JDBC, que não terá qualquer noção que está a falar com um porto PAM:

Sucesso!
E não há muito mais a fazer. É básico e muito simples de configurar. Mostra a flexibilidade do Informix. E como usa PAM, podemos enriquecer a solução adicionando outros módulos se desejarmos

Considerações

Existem muitos aspetos a considerar neste assunto. Primeiro podemos imaginar os cenários de utilização para isto. Vêm-me algumas ideias à cabeça:

• Segurança adicional para utilizadores priveligiados. Podemos assumir que as aplicações se conectam apenas por uma rede mais segura, utilizando a autenticação normal, mas os DBSAs podem ter de se ligar através de uma interface mais exposta que por isso justifique segurança adicional
• Pode usar isto para trabalhar com uma senha "dupla" em que parte esteja disponível internamente na aplicação e os utilizadores apenas fornecem o código de verificação. Isto impediria os utilizadores de se autenticarem fora da aplicação (porque o utilizador não teria conhecimento sobre o outro componente da senha, ao mesmo tempo que o gestor aplicacional não poderia assumir a identidade do utilizador pois não teria o código de verificação)
• Podem criar-se outros cenários bem mais complexos se estivermos dispostos a mexer no código do módulo

É importante ter sempre presente que como em qualquer assunto relacionado com segurança, temos de avaliar cuidadosamente as necessidades e discutir as possibilidades com pessoas que entendam bem os mecanismos de segurança. Tive uma espécie de brain storming com dois ex-DBAs Informix que agora trabalham em segurança e alguns pontos interessantes foram levantados. Entre eles aqui ficam alguns:

1. Este tipo de gerador de senhas descartáveis em oposição aos dispositivos especializados como os tokens RSA
• Possíveis vantagens deste método:
• Provavelmente mais rapidamente se apercebe da perda de um telemóvel que de um equipamento especializado
• É mais barato
• Se o dispositivo necessitar de substituição, este método parece mais simples (o próprio utilizador pode fazê-lo após ter um novo telefone na sua posse)
• Não existe dependência de um fornecedor externo
• Possíveis desvantagens deste método:
• Parece mais fácil atacar remotamente um smartphone que comprometer a segurança de um aparelho específico
• A própria App poderia disponibilizar algum mecanismo para dificultar o acesso não autorizado aos códigos por parte de quem tenha acesso físico ao aparelho. Note-se que isto também é um problema para os dispositivos específicos, e num telefone este pode ser protegido por um PIN ou pela introdução de um padrão de autenticação. Não será necessariamente uma desvantagem deste método, mas algo que poderia ser melhorado
• Um telefone está mais sujeito a problemas que afetem o funcionamento do gerador que um dispositivo específico
2. Seria difícil utilizar este método em processos não interativos. A menos, e acredito que isto fosse possível, que se trabalhe ao contrário... Ou seja, que tenhamos o gerador de códigos dentro dos servidores aplicacionais, e que se configure uma função de callback que responda ao desafio do módulo. Isto poderia evitar a utilização do utilizador aplicacional fora do ambiente dos servidores aplicacionais
3. Um problema genérico da autenticaçao de dois fatores é que idealmente o segundo fator deverá utilizar um canal diferente do primeiro. Tal não acontece aqui e isso permite que um ataque do tipo man in the middle possa ser feito.
4. Outra questão que pode estar relacionada com a de cima é a possibilidade ou não de reutilizar o mesmo código (senha descartável) num determinado período de tempo ainda que curto. As senhas geradas pelo Google authenticator têm por omissão uma duração de 30 segundos (para permitir que o utilizador tenha tempo de a introduzir e também para compensar algum desfazamento de relógios). O módulo permite ainda usar o código imediatemente antes e depois, passando o intervalo para 1m30. Mas tudo isto pode ser controlado, e inclusivamente podemos limitar cada código a apenas uma utilização. Isto no entanto limitará o utilizador a não fazer mais que um login em cada 30 segundos

Problemas encontrados

Durante a preparação e testes destes artigo encontrei dois problemas principais:

1. Nas primeiras tentativas obtive o erro "Invalid verification code" gerado pelo módulo. Como é referido na documentação, isto é habitualmente causado por problemas de sincronização de relógios entre o servidor e o lado da aplicação móvel. Após algumas verificações percebi que estava a fazer confusão entre a hora do relógio e a timezone. Isto causava uma diferença de relógios demasiado grande (o módulo permite alguma configuração para pequenas diferenças)
2. Como é habitual com PAM e os seus módulos, a parte mais difícil é perceber os erros. A maioria dos módulos tendem a ser bastante "secretos" em relação às causas dos erros. Por exemplo, e não sei bem porquê, nas primeiras tentativas de concatenar as duas senhas, assumi que a ordem era a contrária (código + senha de sistema) em vez da correta (senha de sistema + código). Naturalmente não funcionava mas foi difícil perceber porquê. Acabei por alterar o código do módulo para introduzir mensagens que permitissem o debug

Outro possível problema é que actualmente não há versão da App para telefones Windows que aparentemente estão em crescimento no mercado (segundo números recentes venderam-se recentemente mais telefones Windows que Blackberry)

Agradecimentos

Não é inédito que eu discuta alguns aspetos enquanto escrevo estes artigos. Lembro-me de já ter tido ajuda de vários colegas da IBM, alguns já aqui mencionados algumas vezes. Neste caso tive o prazer de discutir este assunto com dois ex-DBAs Informix, com quem já trabalhei durante vários anos numa equipa de um cliente. Agora ambos trabalham na equipa de segurança e a primeira vez que o tema do Google authenticator me chamou a atenção foi durante uma conversa com um deles. Enquanto escrevia este artigo interagi com eles várias vezes. Vários aspetos deste artigo devem-se a essas conversas. Portanto, por isso e pelos bons tempos em que trabalhámos juntos (ocasionalmente ainda o fazemos, pois ambos têm uma longa história nesse cliente e é frequente os assuntos cruzarem-se), um grande "Obrigado!" ao Daniel Valente e Rui Mourão

Informix authentication and connections

The idea for this post comes from a recent discussion on c.d.i and also from checking the URL referrers that bring people here.
Most of this blog visits comes from Google or other search engines. And apparently there are a lot of questions about how Informix authenticates and establishes user connections.

I will try go give a generic overview on these subjects. I'll point out a few less known aspects of Informix authentication.
This article in no way tries to substitute the official documentation, but I hope this can be used as a concentrated information resource about the subject.


Authentication versus privileges

First, we have to create a distinction between two steps involved in a connection establishment.
As we all know, IDS and other databases have several privileges required for doing any action in the database.
At the first level we have Database level privileges. These are CONNECT, RESOURCE and DBA. Then we have object level privileges. For tables we have INSERT, DELETE, ALTER, REFERENCE and for column level we have SELECT and UPDATE
The list above is not exhaustive. The privileges relevant for this article are the database level ones. In particular the CONNECT privilege. Without it we will not be able to connect.
Privileges can be given using the SQL stament GRANT and removed with the REVOKE. Object level privileges can be GRANT'ed to individual users or to ROLEs. You can imagine ROLEs as groups and you can GRANT a role to individual users.

But let's get back on track. The purpose here is not to explain the privilege infra-structure, but to explain how do we connect to the database. I just explained the privileges because we need to go through two steps for establishing a connection:

1. First Informix has to make sure we are who we pretend to be. This is called the authentication phase
   
2. Second, Informix will check if the user we are defining in the connection has the required privileges to establish the connection.
   It cannot do the second step without first completing the authentication.
   To be able to connect we need at least CONNECT privilege on the database level. This privilege can be granted specifically to the user or to the special role PUBLIC which means "anyone"
   

User repositories

One very important aspect of Informix is that it doesn't use the concept of database only users. Any user in an Informix database must be recognized by the underlying OS (up to the current version at the time of writing which is IDS 11.50.xC3).
It's essential to know this in order to understand how it works. For example, if you want to create a user for Informix Dynamic Server usage, you have to create it in the OS or other repository, but you must make the OS aware of that user. And for users created in the OS, if you need to change their password you must do it using the OS tools.

Some people may consider this a big limitation, but the subject is a bit more complex than what it may look at first.
There are reasons for this requirement:

• In an Informix stored procedure, you can call an OS command or script. This is done using the SYSTEM() SPL statement. This command will be run with the user identity that was used to establish the database connection.
  Other databases execute these commands as a specifically configured user or as the user running the database software. The way Informix works can give you a lot of flexibility, but the price for that is the need to have the user recognized at the OS level
• When we ask the database server to print out the explain of a query using the SET EXPLAIN SQL instruction, Informix will create a file on the database server. The information is written by the database instance processes, so it has to be written on the server running the database.
  This files are written with the user ID used for the connection. The file is written on the current client directory for connections local to the database server and on the users $HOME for remote connections. Again, this requires a user created in the OS
• The SET DEBUG FILE/TRACE ON SPL instructions have the same behavior and requirements
  

The above doesn't necessarily means we need to have the users in /etc/passwd (Unix/Linux). It means that getpwnam() must be able to retrieve the user info. This will happen transparently if you configure your system to use LDAP or NIS authentication.
So you can have a central user management infra-structure like LDAP, MS Active Directory or NIS. Informix also doesn't require the user to be able to establish a session (ssh or telnet for example) on the database server at the OS level. You can for example set the users Shell to /bin/false or use other means to prevent the users to connect to the OS.
To complicate things a little bit more, we should not confuse the need to have the user id recognized in the OS with the authentication mechanism. Since 9.40 Informix can use PAM for authentication. This gives you complete freedom to implement complex and sophisticated authentication methods. You can use whatever PAM modules you desire to implement the authentication, which means your Informix authentication doesn't have to be the same as your OS authentication.
But the user has to be known by the OS due to the reasons presented before. As a quick example, you can create your users with random passwords in the OS (not known to anyone) and configure IDS to authenticate your users based on files, or any remote process using PAM.

The fact that the user has to be known in the OS is generally accepted as an inconvenience, and as such I believe it's safe to assume that in a future version Informix will relax these requirements. It could probably accept connections from users not recognized by the OS (either authenticated internally or with PAM or both) and have some parameter to define a default user for the situations where it needs a user id.


Types of connections

Informix implements the following types of connections:

• Trusted connections
  These connections are based on trusted relations between hosts. Basically the database server host is configured to trust the user if he connects from a specific host or list of hosts.
  As such, this connections can be established without providing a password or any other authentication mechanism. The way it works is exactly the same as the "r" services (rshell, rcmd, rlogin).
  The configuration is done using the network connectivity files, /etc/hosts.equiv and ~/.rhosts
  
  
• Non trusted connections
  These connections are the standard type of connections. Typically we provide a user and a password that is used to check the identity. The password is checked against the user's stored password
  
  
• Challenge/response based authentication using PAM modules
  Connections made to PAM configured ports (DBSERVERALIAS). PAM stands for Plugin Authentication Modules and can be used in any Unix/Linux system (AIX, HP-UX, Linux and Solaris)
  
  
• Distributed query connections
  These connections can be considered trusted connections. But they're established implicitly when a client connected to instance "A" sends a query referencing instance "B".
  In these situation the IDS server "A" will make an implicit connection on behalf of the user, to IDS server "B". The connection authentication will follow the rules for implicit connections, or will use a different authentication mechanism if server "B" is setup with PAM authentication (more on this later)
  

Non trusted connections

We use non-trusted connections when we give a user id and a token (typically a password) that guarantees our identity (only the user should know it's personal authentication secret or password) . Meaning we have not only the user id, but also it's secret key.
These connections are used mostly in applications that use JDBC, ODBC, .NET, PHP (PDO), Perl (DBI) etc. These APIs require a connection string or URL, or use specific API connection functions. So we define the password in the string, or we provide is as an argument for the API connection function.
Here's an example of a JDBC URL to connect to an Informix instance:

jdbc:informix-sqli://ids_server_machine:9088/my_database;user=username;password=secret

In ESQL/C (embedded SQL/C - which is basically C language with SQL commands) or Informix 4GL we can use the CONNECT instruction:

CONNECT TO database@ids_instance USER <"username"|variable_user> USING <variable_password>

One important note, that allows me to show the two steps in a connection establishment: authentication and database open:
Typically in 4GL we use a "database test_auth" instruction. This, by default, makes the two steps by trying to make a trusted connection using the owner of the process.
But let's create a simple 4GL program to show the difference between authentication and the database opening phase. Here's the code (don't worry if you don't know 4GL, because these lines are self explanatory):

DEFINE username,password char(20)
DEFINE c CHAR

MAIN
PROMPT "Insert your username: " FOR username
PROMPT "Insert your password: " FOR password ATTRIBUTE ( INVISIBLE )
CONNECT TO "@cheetah2" USER username USING password
PROMPT "We are authenticated, but not yet connected to a database..." FOR CHAR c
DATABASE test_auth

PROMPT "Now we have an opened database!" FOR CHAR c
END MAIN

So, we're asking the user's name and password. Than we're CONNECTing to the server. Note that I didn't use a database name. This is a supported syntax, although not very frequently used.
After the connect, we then issue the usual "DATABASE" instruction. Let's see what happens when we run the program above. First the status of the IDS instance showing the existing sessions:

cheetah2@PacMan.onlinedomus.net:informix-> onstat -u

IBM Informix Dynamic Server Version 11.50.UC2     -- On-Line -- Up 09:52:57 -- 88064 Kbytes

Userthreads
address  flags   sessid   user     tty      wait     tout locks nreads   nwrites
4780c018 ---P--D 1        informix -        0        0    0     69       559
4780c5f0 ---P--F 0        informix -        0        0    0     0        1032
4780cbc8 ---P--F 0        informix -        0        0    0     0        596
4780d1a0 ---P--- 5        informix -        0        0    0     0        0
4780d778 ---P--B 6        informix -        0        0    0     48       0
4780e328 ---P--- 17       informix -        0        0    1     315      224
4780e900 ---P--D 9        informix -        0        0    0     3        0
4780eed8 ---P--- 16       informix -        0        0    1     1286     1093
4780f4b0 ---P--- 15       informix -        0        0    1     189      3
47810060 Y--P--D 21       informix -        440cfe28 0    0     0        0
10 active, 128 total, 18 maximum concurrent

So... Only informix user system sessions. Now let's run the program (I'll do it as root):

cheetah2@pacman.onlinedomus.net:root-> ./test.4ge
Insert your username: fnunes

Insert your password: [......][ENTER]

We are authenticated, but not yet connected to a database...

The program is now holding at the first "PROMPT" instruction after the connect. Let's see the list of sessions:

cheetah2@PacMan.onlinedomus.net:informix-> onstat -u

IBM Informix Dynamic Server Version 11.50.UC2     -- On-Line -- Up 09:57:30 -- 88064 Kbytes

Userthreads
address  flags   sessid   user     tty      wait     tout locks nreads   nwrites
4780c018 ---P--D 1        informix -        0        0    0     69       559
4780c5f0 ---P--F 0        informix -        0        0    0     0        1032
4780cbc8 ---P--F 0        informix -        0        0    0     0        596
4780d1a0 ---P--- 5        informix -        0        0    0     0        0
4780d778 ---P--B 6        informix -        0        0    0     48       0
4780e328 ---P--- 17       informix -        0        0    1     315      224
4780e900 ---P--D 9        informix -        0        0    0     3        0
4780eed8 ---P--- 16       informix -        0        0    1     1286     1093
4780f4b0 ---P--- 15       informix -        0        0    1     189      3
4780fa88 Y--P--- 61       fnunes   4        485bff38 0    0     0        0
47810060 Y--P--D 21       informix -        440cfe28 0    0     0        0
11 active, 128 total, 18 maximum concurrent

cheetah2@PacMan.onlinedomus.net:informix-> onstat -g ses 61

IBM Informix Dynamic Server Version 11.50.UC2     -- On-Line -- Up 09:57:39 -- 88064 Kbytes

session           effective                            #RSAM    total      used       dynamic
id       user     user      tty      pid      hostname threads  memory     memory     explain
61       fnunes   -         4        15267    pacman.o 1        40960      37280      off

tid      name     rstcb    flags    curstk   status
85       sqlexec  4780fa88 Y--P---  5728     cond wait  netnorm   -

Memory pools    count 1
name         class addr     totalsize freesize #allocfrag #freefrag
61           V     483c3028 40960     3680     68         7

name           free       used           name           free       used
overhead       0          1672           scb            0          96
opentable      0          784            filetable      0          192
misc           0          64             log            0          16512
temprec        0          1608           gentcb         0          1232
ostcb          0          2632           sqscb          0          8192
sql            0          40             hashfiletab    0          280
osenv          0          1720           sqtcb          0          2208
fragman        0          48

sqscb info
scb      sqscb    optofc   pdqpriority sqlstats optcompind  directives
488a18a0 48558018 0        0           0        2           1

Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain
61         -              -                  -   Not Wait   0    0    9.29  Off

So what do we see? We have a session, on behalf of user "fnunes" but the "Current Database" is not defined. So we've just gone through the authentication process with success.
Now, if I press ENTER on the program I'll force the program to execute the DATABASE statement and wait on the second PROMPT:

cheetah2@pacman.onlinedomus.net:root-> ./test.4ge
Insert your username: fnunes

Insert your password: [...] [ENTER]

We are authenticated, but not yet connected to a database... [ENTER]
Now we have an opened database!

ok... now let's look at the database session again:

IBM Informix Dynamic Server Version 11.50.UC2     -- On-Line -- Up 10:09:32 -- 88064 Kbytes

session           effective                            #RSAM    total      used       dynamic
id       user     user      tty      pid      hostname threads  memory     memory     explain
61       fnunes   -         4        15267    pacman.o 1        49152      45544      off

tid      name     rstcb    flags    curstk   status
85       sqlexec  4780fa88 Y--P---  5728     cond wait  netnorm   -

Memory pools    count 1
name         class addr     totalsize freesize #allocfrag #freefrag
61           V     483c3028 49152     3608     83         6

name           free       used           name           free       used
overhead       0          1672           scb            0          96
opentable      0          1792           filetable      0          352
misc           0          64             log            0          16512
temprec        0          1608           gentcb         0          1232
ostcb          0          2632           sqscb          0          13712
sql            0          40             rdahead        0          832
hashfiletab    0          280            osenv          0          1720
sqtcb          0          2792           fragman        0          208

sqscb info
scb      sqscb    optofc   pdqpriority sqlstats optcompind  directives
488a18a0 48558018 0        0           0        2           1

Sess       SQL            Current            Iso Lock       SQL  ISAM F.E.
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain
61         -              test_auth          CR  Not Wait   0    0    9.29  Off

So, now we have a current database, in the same session.
This ends the description of non trusted connections using username and passwords. Pretty simple, just like in any other database server.


Challenge/response connections using PAM

Another kind of non trusted connections are connections established through PAM configured DBSERVERALIAS (specific ports where the instance will listen for connections).
I will not go into details about this, because I already wrote an article about it. In http://informix-technology.blogspot.com/2007/11/informix-user-authentication-pam-for.html I explain how to setup and give examples of challenge/response scenarios.

In summary, a non-trusted connection in Informix is similar to connections in other RDBMS. You supply a username and a token that certifies you're entitled to connect as that user.
Typically this token is a password. But with PAM this can be a much more complex item.
In IDS 11.50 we can also use single sign on with kerberos. Due to the complexity of this scenario I've left it out in this article.
Non trusted connections are used mostly in more recent applications, like Web based applications, Windows client applications, or script languages (PHP, Perl, Ruby etc.) languages.

Trusted connections

These connections are made without any token to confirm the user identity. It's assumed that the user is already identified on the client system and we configure the database server to trust that identification. Trusted connections are mostly used in applications created using Informix 4GL and Informix ESQL/C.
The definition of the trusts are done by configuration of the so called network security files. These files are /etc/hosts.equiv and the ~/.rhosts (.rhosts created in each users's home dir).
Before we dig into these files content, and what it means I have to make a parenthesis to deal with something I'd almost call a myth surrounding Informix trusted connections. If you look around on the Internet you'll easy find a lot of information about how insecure is to use these files. In particular, you may found explicit instructions to not use the ~/.rhosts file.
The main reasons for these are two facts:

1. These files are not specific for Informix, and in fact they were created to configure a group of services, sometimes called the "r" services. These services are rexec (allows remote execution of a command) on TCP port 512, rlogin (allows remote login) on TCP port 513 and rcmd (all remote shell execution) on TCP port 514.
2. Many of these services are considered insecure and have a long history of security flaws. The most famous worm also took advantage of some of these security flaws, and also from very relaxed security setup on many of the networks connected to the Internet at the time.

Why did I referrer before the "myth"? Well, many customers have the idea that since IDS uses the same files for trusted connections authentication that you need to be running the above services for it to work. This is completely false. I cannot stress this enough. Even in the official documentation, the administrator guide, is written:

To determine whether a client is trusted, execute the following statement on the client computer: rlogin hostname If you log in successfully without receiving a password prompt, the client is a trusted computer.

Although this is not wrong, it misses the point, and gives the idea that the rlogin service should be running. The above sentence, saying that if rlogin works the user is trusted, is true. But you can create a trust for an Informix connection without having the service(s) running. In this case you'll get a "can't connect error" instead of the password prompt when you run rlogin. And it doesn't mean it won't work with Informix.
I strongly believe Informix should not use these same files for defining it's trust relationships. But the concern that this usage raises is not really an issue. If you're concerned about security, you probably will not use the "r" services. Most of the sites which take security seriously have replaced them with ssh or something similar. So the truth is that these files should be useless in a modern system. And in this scenario, the fact that they're used by Informix should not be considered too critical, since nothing else should depend on them.


I hope this contributes to the disappearance of the "myth". Now let's see how we have to change the network security files in order to create the trusts.
The contents of the files should be similar to how you would configure "r" services trusts. Let's see the two files, because there some slight but very important differences.

• /etc/hosts.equiv
  Each line in this file defines a trust relation. You can specify a host only, or a host and a user. This file is a system file. No user other than system administrator should have permission to change it.
  If you specify only the host name, you're saying that any user from the remote host is trusted to connect as the same user in the local host. If you specify a "+" (plus sign) this means "any host" and you should never do that...
  There is a significant difference between how Informix and "r" services interpret the file if you specify a hostname followed by a user name. For "r" services, this means that you trust the remote user on the remote system to connect as ANY user in the local system (except root). This is obviously a very serious issue... And by the way, "+ +" means any user from anywhere can login locally as any user! (don't try this even at home :) )
  Informix on the other hand interprets this as a way to specify user by user and not all the users. The remote user cannot connect as another local user without providing a username and password.
• ~/.rhosts
  This file also includes a trust relation in each non comment line. This file, being in the user home directory, means that you allow the user on the local host to define who he trusts. Many systems verify that the file has no write permissions to the "world". In this case it ignores the settings.
  A line with a host and a username means that the specified username on the specified remote host can connect as the local user owning the .rhosts file. So, for "r" services, if you specify a host and user in /etc/hosts.equiv it means that user can connect as any local user. If you do the same in ~/.rhosts you're reducing the scope of local users that the remote user can authenticate as, to only the file owner
  For Informix, the interpretation is the same: The remote user can authenticate on the local machine, but without changing it's identity. It means that for example, if the local user is luser and it's ~/.rhosts contains "remotehost ruser", then this entry is useless because Informix doesn't have a way to specify the identity change in a connection attempt.
  

Some additional notes on these files:

• You can specify negative entries. For example you could use the following in /etc/hosts.equiv:
  
  pacman -informix
  pacman
  
  This would allow all users from host "pacman" to connect except informix
• An entry with a host name means "trust all users except root". A trust relation for root user has to be defined in it's .rhosts file.
• The order of the entries is relevant. If we exchange the order of the entries in the example above, even informix will be allowed to connect. That's because a positive entry will be found first

Let's see some examples. I have two hosts (pacman and ids1150srvr). I'm running IDS on the ids1150srvr host. If I want to allow the user informix to connect to this instance from the pacman host I can do it in the following ways (these file entries are on the ids1150srvr host):

• /etc/hosts.equiv:
  
  pacman
  pacman informix
  
  The first entry allows all users (except root) to connect from pacman. The second will allow only informix. But be aware that if you're running "r" services you're saying that informix on pacman can connect as any user (again, except root) on ids1150srvr.
  
  
• ~informix/.rhosts
  
  pacman
  

Distributed query connections

We've seen a client/server trusted connection. But we have another situation where we must establish trusts between systems. That's the case when you need to join data from two Informix instances. The SQL syntax to do this will look like this:

SELECT
local_table.column, remote_table.column
FROM
table1 local_table, remote_database@remote_ids_instance:table2 remote_table
WHERE
local_table.join_column = remote_table.join_column;

Assuming we are connected to a "local_ids_instance" on ServerA, and that "remote_ids_instance" is running on ServerB, this query will establish a connection on "remote_ids_instance" from the "local_ids_instance". Informix will establish this connection on behalf of our user on "local_ids_instance". As such, our user must be trusted on ServerB when connecting from ServerA. Note that the trust is not done between our client and the ServerB. It's between ServerA and ServerB. If the "remote_ids_instance" is accessed through a normal Informix port, the authentication mechanism used is the same as for trusted connections.

The situation will be different if the "remote_ids_instance" port is configured with PAM. In this situation the trust configuration is done entirely through SQL instructions. IDS versions that support PAM also have a new system database. It's called sysuser. In it we have a table called sysauth with the following schema:

{ TABLE "informix".sysauth row size = 322 number of columns = 4 index size = 295              }
create table "informix".sysauth
(
username char(32) not null ,
groupname char(32),
servers varchar(128) not null ,
hosts varchar(128) not null ,
check (groupname IS NULL )
);
revoke all on "informix".sysauth from "public" as "informix";


create unique index "informix".sysauth_idx on "informix".sysauth     (username,servers,hosts) using btree ;

This table is used to define trusts for distributed queries when the destination Informix instance is configured for PAM authentication.
We must specify the username, the hostname and the IDS server from where the connection is made. Note that this not only avoids the usage of the network security files but also provides more control. With the network security files we cannot specify the Informix instance originating the connection.

So, picking up the query above, and assuming the username is "fnunes" we would need the following configuration on ServerB for it to work:

• Using /etc/hosts.equiv one of the following lines:
  
  

  ServerA
  # this would create a trust for all users from ServerA
  ServerA fnunes
  # This would restrict the trust in Informix for user fnunes.
  # But for "r" services it would allow fnunes to login as any user except root on ServerB

  
  
• Using ~fnunes/.rhosts
  

  ServerA

  
  
• Using the sysuser:sysauth table (if the port was configured for PAM):
  

  INSERT INTO sysauth ( username, groupname, servers, hosts) VALUES ('fnunes', NULL, 'local_ids_instance', 'ServerA');

Additional notes about sysauth:

• Although the field names "servers" and "hosts" suggest you could use a list of servers or hosts the documentation says we should create one entry per user/server/host
• The field "groupname" is not used currently as expected from the check constraint
  

Additional notes

There are a few less none configuration details about connections in Informix. Let's check them.

• There are some options that we can specify in the sqlhosts file that control the way IDS deals with trusted connection attempts (for the specific ALIAS of the sqlhosts line).The options I'm referring to are "s=X". "s" stands for security on the server side. The following values are supported:
• s=0
  Disables both hosts.equiv and rhosts lookup from the database server side (only incoming connections with passwords are accepted).
• s=1
  Enables only the hosts.equiv lookup from the database server side.
• s=2
  Enables only the rhosts lookup from the database server side.
• s=3
  Enables both hosts.equiv and rhosts lookup on the database server side (default setting for the database server side).
• s = 6
  Marks the port/ALIAS as a replication only (HDR or ER) port
  If you use the option s=6 on sqlhosts options field, than you can create a file called hosts.equiv in $INFORMIXDIR/etc, owned by informix:informix and only modifiable by user informix. This makes the informix user independent of the system administrator for configuring the trusts, and additionally doesn't interfere with "r" services if they're in use
• There is another security option that defines if the client uses or not the .netrc file. I haven't yet mentioned the .netrc file... You can use this file so specify a user and password, per host that you want to connect. If you setup the file and try to make a trusted connection you'll instead make a non-trusted connection. An example of this file content would be:
  
  ids1150srvr login fnunes password mysecret
  
  This would connect you as user "fnunes" using password "mysecret" if you attempt to make a trusted connection to an Informix instance running on host "ids1150srvr". You can have several lines, one for each host you connect to.
  There are obvious security issues derived from having a password in a file...
  The option that specifies if the client libraries will look and use this file is the "r=X" settings on the sqlhosts options (client side):
• r=0 it ignores the .netrc file
• r=1 it looks at the .netrc file when a client program tries to make a connection without using username and password

Windows specific information

In MS Windows, the network security files are used in a similar way, but they're in different locations. /etc/hosts.equiv becomes %SYSTEMROOT%\system32\drivers\etc\hosts.equiv.
The .rhosts and .netrc should exist in the users HOME

Common error messages

The following is not an exhaustive list of connection errors, but a small list of the more common ones with a very brief comment. For more information about each one consult the documentation or use "finderr errorcode":

• -908 Attempt to connect to database server (servername) failed.
  Usually means some problem on the TCP level. Either your server is not running or you have your client SQLHOSTS points to the wrong server or port
  
• -930 Cannot connect to database server servername.
  Typically the client cannot obtain the server IP address. Check your DNS configuration.
  
• -951 Incorrect password or user user@host is not known on the database
  You can get this error on the server side (online.log) and on the client side.
  On the server side it means the user does not exist.
  On the client side you'll see these error in three situations.. The same error is sent to the client side on different situations in case someone is trying to guess a user or password. The database system administrator can see the real problem. The situations where the error is sent to the client are:
• The user does not exists on the database server
• The user's password cannot be validated, although the user exists
• The user exists, but you're trying to make a trusted connection and the trust is not setup correctly
  
• -952 User ()'s password is not correct for the database server.
  This is the error given on the server side when the user password did not validate successfully
  
• -956 Client client-name or user is not trusted by the database server.
  Server side error when a trusted connection fails because it's not properly setup
  
• -1809 Server rejected the connection.
  This is the only error you'll see when a connection to a PAM configured server happens. In order to understand what went wrong you have to check the PAM stack modules messages
  
• -25596 The INFORMIXSERVER value is not listed in the sqlhosts file or the Reg
  The INFORMIXSERVER you're trying to connect is not properly defined in you configured SQLHOSTS

Summary

I really hope this article covers most practical aspects of connection establishment in Informix. There are several aspects I left out for simplicity. These include:

• Single sign-on (introduced in IDS 11.50)
• Password encryption
• SSL connections

These topics may be covered in future articles. You can find more information about Informix connection setup in the Administrator Guide and the Security Guide.