On Tue, May 04, 2010 at 11:48:20AM +0200, Chris Wilson wrote:
> Hi Mark,
Hi Chris,
>
> On Tue, 4 May 2010, Mark Adams wrote:
>
>> An example of one of the spam mails being sent out shows in the log as
>> follows; note that instead of rejecting the login it just shows as
>> blank.
>>
>> 1O8epJ-0006Ft-Sj <= nlexluon@??? H=(geembr.com)
>> [59.35.97.251] P=esmtpa A=pgsql_login: S=2743
>> id=d5d9f8280c874dbe93f0564fd56a8d83@fb9d2058f7ab4242ae7bbe23bd70bf11
>>
>> I've tested myself with blank user/pass and it shows an authentication
>> failure as it should -- how are they getting through here?? Any help
>> appreciated.
>
> Have you tried a blank username with a password?
Yes I have tested this, it also correctly gives the Incorrect
Authentication detail message.
>> pgsql_login:
>> driver = plaintext
>> public_name = LOGIN
>> server_prompts = "Username:: : Password::"
>> server_condition = \
>> ${if and {{eq{$1}{${lookup pgsql \
>> {SELECT userid || '@' || domain FROM fn_mail_user_get_active_smtp ('${quote_pgsql:$1}')}}}}\
>> {eq{$2}{${lookup pgsql\
>> {SELECT password FROM fn_mail_user_get_active_smtp ('${quote_pgsql:$1}')}}}}}{yes}{no}}
>> server_set_id = $1
>
> It looks like you're using PostgreSQL "table functions" in your queries.
> Without knowing what those functions do, it's difficult to tell what
> might be happening. You might like to avoid them in order to diagnose the
> problem.
This is fn_mail_user_get_active_smtp:
CREATE FUNCTION fn_mail_user_get_active_smtp(character varying) RETURNS
SETOF users
LANGUAGE plpgsql
AS $_$
DECLARE
in_email ALIAS for $1;
usercheck users%ROWTYPE;
BEGIN
SELECT INTO usercheck
*
FROM
users
WHERE
userid || '@' || domain = lower(in_email)
AND
status IN (1,2)
AND
b_isadmin IS FALSE
ORDER BY
userid;
RETURN NEXT usercheck;
END;$_$;
>
> It's possible that these queries return multiple rows in random order.
>
> It's possible that you have a user with no password in your database and
> the spammer somehow got lucky in selecting that user.
There isn't any users that don't have passwords, and if so the log
should show the username being used.
>
> I think it's unsafe to use $1 and $2 because they could be modified by
> matching during the query.
What else should be used instead?
>
> Another user reported a problem with server_set_id last week that might
> be related, you could search the archives for it.
>
> Have you tried a blank username with a non-blank password?
>
> Cheers, Chris.
> --
> _ ___ __ _
> / __/ / ,__(_)_ | Chris Wilson <0000 at qwirx.com> - Cambs UK |
> / (_/ ,\/ _/ /_ \ | Security/C/C++/Java/Perl/SQL/HTML Developer |
> \ _/_/_/_//_/___/ | We are GNU-free your mind-and your software |
