Security

Reply
Contributor II

Problem conneting to a external Postgre SQL

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.

 

Contributor II

Re: Problem conneting to a external Postgre SQL

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.

Aruba Employee

Re: Problem conneting to a external Postgre SQL

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


Thank you,
Saravanan Rajagopal

**Did something you read in the Community solve a problem for you? If so, click "Accept as Solution" in the post.
Contributor II

Re: Problem conneting to a external Postgre SQL

Saravanan, bellow is the screenshoots of that you require.

 

 

Aruba Employee

Re: Problem conneting to a external Postgre SQL

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}';


Thank you,
Saravanan Rajagopal

**Did something you read in the Community solve a problem for you? If so, click "Accept as Solution" in the post.
Contributor II

Re: Problem conneting to a external Postgre SQL

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.

 

 

Aruba Employee

Re: Problem conneting to a external Postgre SQL

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.


Thank you,
Saravanan Rajagopal

**Did something you read in the Community solve a problem for you? If so, click "Accept as Solution" in the post.
Contributor II

Re: Problem conneting to a external Postgre SQL

 

 

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

Time Message

2018-04-04 15:48:50,728[Th 12 Req 8 SessId R00000008-01-5ac51e12] INFO RadiusServer.Radius - rlm_service: Starting Service Categorization - 9:205:f0d7aa9d2f09
2018-04-04 15:48:50,732[RequestHandler-1-0x7fa189f6e700 r=psauto-1522863313-18 h=127 r=R00000008-01-5ac51e12] INFO Core.ServiceReqHandler - Service classification result = TEST_radius-POSTGRES
2018-04-04 15:48:50,733[Th 12 Req 8 SessId R00000008-01-5ac51e12] INFO RadiusServer.Radius - Service Categorization time = 5 ms
2018-04-04 15:48:50,733[Th 12 Req 8 SessId R00000008-01-5ac51e12] INFO RadiusServer.Radius - rlm_service: The request has been categorized into service "TEST_radius-POSTGRES"
2018-04-04 15:48:50,733[Th 12 Req 8 SessId R00000008-01-5ac51e12] INFO RadiusServer.Radius - rlm_sql: searching for user testuser in Sql:192.168.1.14
2018-04-04 15:48:50,753[Th 12 Req 8 SessId R00000008-01-5ac51e12] ERROR RadiusServer.Radius - rlm_sql_unixodbc: sql state - 07005, sql error - unixODBC]Bindings were not allocated properly.
2018-04-04 15:48:50,753[Th 12 Req 8 SessId R00000008-01-5ac51e12] ERROR RadiusServer.Radius - rlm_sql_unixodbc: SQL down 07005 unixODBC]Bindings were not allocated properly.
2018-04-04 15:48:50,753[Th 12 Req 8 SessId R00000008-01-5ac51e12] INFO RadiusServer.Radius - rlm_sql_unixodbc: rlm_sql will attempt to reconnect
2018-04-04 15:48:50,770[Th 12 Req 8 SessId R00000008-01-5ac51e12] ERROR RadiusServer.Radius - rlm_sql_unixodbc: sql state - HY010, sql error - unixODBC]Driver Manager]Function sequence error
2018-04-04 15:48:50,770[Th 12 Req 8 SessId R00000008-01-5ac51e12] ERROR RadiusServer.Radius - rlm_sql_unixodbc: SQL down HY010 unixODBC]Driver Manager]Function sequence error
2018-04-04 15:48:50,770[Th 12 Req 8 SessId R00000008-01-5ac51e12] INFO RadiusServer.Radius - rlm_sql_unixodbc: rlm_sql will attempt to reconnect
2018-04-04 15:48:50,770[Th 12 Req 8 SessId R00000008-01-5ac51e12] ERROR RadiusServer.Radius - rlm_sql (authsrc_3001): failed after re-connect
2018-04-04 15:48:50,770[Th 12 Req 8 SessId R00000008-01-5ac51e12] ERROR RadiusServer.Radius - rlm_sql_unixodbc: sql state - HY010, sql error - unixODBC]Driver Manager]Function sequence error
2018-04-04 15:48:50,770[Th 12 Req 8 SessId R00000008-01-5ac51e12] ERROR RadiusServer.Radius - rlm_sql_unixodbc: SQL down HY010 unixODBC]Driver Manager]Function sequence error
2018-04-04 15:48:50,770[Th 12 Req 8 SessId R00000008-01-5ac51e12] INFO RadiusServer.Radius - rlm_auth_check: Auth-Type not set.
2018-04-04 15:48:50,770[Th 12 Req 8 SessId R00000008-01-5ac51e12] ERROR RadiusServer.Radius - rlm_auth_check: Auth-Type not set or authentication methods have not been configured. Rejecting it.
Search Airheads
cancel
Showing results for 
Search instead for 
Did you mean: