1 #if defined(DB_PGSQL) && defined(DB)
4 Writen by Devrim SERAL(devrim@tef.gazi.edu.tr)
5 For PostgreSQL Authentication And Accounting
7 This program protected with GPL License.
15 #define AUTHSQL "SELECT %s FROM %s WHERE %s='%s'"
16 #define ACCTSQL "INSERT INTO %s (usern,s_name,c_name,elapsed_time,bytes_in,bytes_out,fin_t) VALUES ('%s','%s','%s',%s,%s,%s,NOW())"
21 int pgsql_db_verify(user, users_passwd, db_user, db_password,
22 db_hostname,db_name, db_table, dbfield_name, dbfield_passwd)
25 char *user, *users_passwd; /* Username and gived password */
26 char *db_user; /* db's parameters */
41 if (debug & DEBUG_AUTHEN_FLAG)
42 report(LOG_DEBUG, "PGSQL: verify %s", user);
44 /* Connect database server */
46 conn=PQsetdbLogin(db_hostname,NULL,NULL,NULL,db_name,db_user,db_password);
48 if ( PQstatus(conn) == CONNECTION_BAD )
50 if (debug & DEBUG_AUTHEN_FLAG)
51 report(LOG_DEBUG, "PGSQL: Connection to database %s failed", db_name);
55 /* Check select string length */
57 sql_len=strlen(dbfield_passwd)+strlen(dbfield_name)+strlen(db_table)+strlen(user)+strlen(AUTHSQL);
59 if ( sql_len> SQLCMDL )
61 if (debug & DEBUG_AUTHEN_FLAG)
62 report(LOG_DEBUG, "PGSQL: Sql cmd exceed alowed limits");
66 /* Prepare select string */
68 pgsqlcmd=(char *) malloc(sql_len);
72 if (debug & DEBUG_AUTHEN_FLAG)
73 report(LOG_ERR, "pgsql_db_verify: pgsqlcmd malloc error");
77 sprintf(pgsqlcmd,AUTHSQL,dbfield_passwd,db_table,dbfield_name,user);
80 res=PQexec(conn,pgsqlcmd);
82 if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
84 if (debug & DEBUG_AUTHEN_FLAG) {
85 report(LOG_DEBUG, "PGSQL: cannot query database ");
86 report(LOG_DEBUG, "PGSQL: Error message->%s", PQerrorMessage(conn) );
89 exit_nicely(conn,res);
95 if( nrow=PQntuples(res)!=1)
97 if (debug & DEBUG_AUTHEN_FLAG)
98 report(LOG_DEBUG, "PGSQL: Have we got more than one password!!");
99 exit_nicely(conn,res);
103 if ( PQgetisnull(res,0,PQfnumber(res,dbfield_passwd)) )
105 if (debug & DEBUG_AUTHEN_FLAG)
106 report(LOG_DEBUG, "PGSQL: DB passwd entry is NULL");
107 exit_nicely(conn,res);
111 /* Allocate memory for real_passwd */
112 real_passwd=(char *) malloc(PWLEN+1);
113 strncpy(real_passwd,PQgetvalue(res,0,PQfnumber(res,dbfield_passwd)),PWLEN);
114 real_passwd[PWLEN]='\0';
116 exit_nicely(conn,res);
118 if (debug & DEBUG_AUTHEN_FLAG)
119 report(LOG_DEBUG, "PGSQL: verify password '%s' to DES encrypted string '%s'", users_passwd, real_passwd);
121 /* Try to verify the password */
122 if (!des_verify(users_passwd, real_passwd))
127 return (1); /* Return 1 if verified, 0 otherwise. */
130 /* PGSQL ACCOUNTING function */
132 int pgsql_db_acct(db_user,db_password,db_hostname,db_name,db_table,s_name,c_name,a_username,elapsed_time,bytes_in,bytes_out)
134 char *db_user; /* db's parameters */
139 char *s_name, *c_name,*a_username,*elapsed_time,*bytes_in,*bytes_out;
146 if (debug & DEBUG_ACCT_FLAG)
147 report(LOG_DEBUG, "PGSQL: Accounting for %s begin", a_username);
149 /* Connect database server */
151 conn=PQsetdbLogin(db_hostname,NULL,NULL,NULL,db_name,db_user,db_password);
153 if ( PQstatus(conn) == CONNECTION_BAD )
155 if (debug & DEBUG_ACCT_FLAG) {
156 report(LOG_DEBUG, "PGSQL: Connection to database %s failed", db_name);
157 report(LOG_DEBUG, "PGSQL: Error message->%s", PQerrorMessage(conn) );
162 /* Check select string length */
164 sql_len=strlen(db_table)+strlen(a_username)+strlen(s_name)+strlen(c_name)+strlen(elapsed_time)+strlen(bytes_in)+strlen(bytes_out)+strlen(ACCTSQL);
166 if ( sql_len> SQLCMDL )
168 if (debug & DEBUG_ACCT_FLAG)
169 report(LOG_DEBUG, "PGSQL: Sql cmd exceed alowed limits");
173 /* Prepare select string */
175 pgsqlcmd=(char *) malloc(sql_len);
179 if (debug & DEBUG_ACCT_FLAG)
180 report(LOG_ERR, "pgsql_db_verify: pgsqlcmd malloc error");
184 sprintf(pgsqlcmd,ACCTSQL,db_table,a_username,s_name,c_name,elapsed_time,bytes_in,bytes_out);
187 res=PQexec(conn,pgsqlcmd);
189 if (!res || PQresultStatus(res) != PGRES_COMMAND_OK )
191 if (debug & DEBUG_ACCT_FLAG) {
192 report(LOG_DEBUG, "PGSQL: cannot establish database query");
193 report(LOG_DEBUG, "PGSQL: Error message->%s", PQerrorMessage(conn) );
196 exit_nicely(conn,res);
202 /* Flush all result and close connection */
203 exit_nicely(conn,res);
205 if (debug & DEBUG_ACCT_FLAG)
206 report(LOG_DEBUG, "PGSQL: Accounting for %s finished", a_username);
208 return (1); /* Return 1 if verified, 0 otherwise. */
212 exit_nicely(PGconn *cn,PGresult *r)