Security

Hello friends, I am trying to have a external Generic  SQL DB source of authentication but I am having the folllowing error when finishing my query.

-------------------------------

select CODUSUARIO as UserName, DTNASCIMENTO as user_password from CORPORERM.dbo.PPESSOA where CODUSUARIO =  '%{Authentication:Username}';

-------------------------------

The filter has been saved but has the following error:
Invalid SQL syntax - FATAL: no entry in pg_hba.conf for machine "192.168.1.230" [My CPPM], user "postgres", database "CORPORERM", SSL disabled.

I not sure if this file did exist on my CPPM too and where edit it, I already added the following line on it on my PostGRE server and I have others machine connecting  where is possible to query information from it.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host         all        all        0.0.0.0/0    trust

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             postgres        192.168.1.230/32        trust

any help is appreciated

Thanks.

Still Stuck on this error.
No idea where to look at, didn't find anything on the knowledge base (google rsrs) about this file and clearpass.

Can you please attach the auth source configuration (all tabs)?

Saravanan, bellow is the screenshoots of that you require.

Attachment(s)

problem-postgree.zip   1.81 MB 1 version

The error indicates that table "ppessoa" doesn't exisit and I see the actual table name is "PPESSOA".

The unqouted identifiers are case insensitives (cast to lower case).

https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Can you try something like below and check the status?

SELECT DTNASCIMENTO AS User_Password from "PPESSOA" where CODUSUARIO = '%{Authentication:Username}'; 

or

SELECT "DTNASCIMENTO" AS User_Password from "PPESSOA" where "CODUSUARIO" = '%{Authentication:Username}';

I also noticed the colomun "DTNASCIMENTO" type is integer (numeric). ClearPass may try to validate the User_Password" as string. So, you may need to convert password from Integer to String.

SELECT CAST("DTNASCIMENTO" as VARCHAR(50)) AS User_Password from "PPESSOA" where "CODUSUARIO" = '%{Authentication:Username}';

Ok, now some kind of progress....


in attached is the new error, the red one is still showing up, but on my server side/logs I am not getting any error anymore about syntax(i did learn where to look at).


I adapted the query based on this post here

https://community.arubanetworks.com/t5/AAA-NAC-Guest-Access-BYOD/How-To-Setup-External-SQL-Filter-Queries-for-Authentication-in/ta-p/187252


My deployment is a replica of what I am doing on my client, just to be clear.


I will be wating for help, still stuck, as you can see I even add a new columm as varchar[50] just to be sure.

Attachment(s)

postgres.zip   1.01 MB 1 version

Hi,

The server is reproting that it is unable to find the user and I see the user in the column "CODUSUARIO" is "3.0". You may need to try the matching string.

May I suggest to open a TAC case? This needs debugging, if the username 3.0 is not working.

Saravanan, I tried diferent things, tried 3.0, '3.0' tried put a direct consult like (SELECT "user_password" FROM "CORPORERM"."PPESSOA" WHERE "USERNAME" = 'andre';),  I tried another table with the same settings a in example from above link (username[testuser],pass[mypassword,role[employee]), I tried reinstalling the ClearPass, in the begining I was not getting the red error message but after change the database by mistake I started getting again even when correcting it.

Looking on the show logs, I see that the clearpass is not connecting to the postgres server, I don't how because, when I use a wrong snytax I am able to see that in my postgres logs.

But with the correct syntax you can see on the past attached the logs about Radius driver error unixODBC.

I think this is something on clearpass side, I am using Postgres 10, maybe the driver is different.

Request log details for session: R00000008-01-5ac51e12

Time Message