On Tue Jan 6, 2009 2:42 pm Victor Duchovni wrote:
> I know that it is not a good model for the way postfix requires the
> query,
More stronly, an SQL schema in which multiple data items are stored
concatenated in a single table element is a poor schema regardless
of the application. This violates fundamental principles of database
schema design. Multi-valued elements are stored in separate tables
that
are joined to the main table. This is not a Postfix requirement, it is
basic database design.
> but from a logical standpoint, I am trying to this about it
> as which addresses is a user allowed to send from.
The correct design has a address table which links address objects
to user
objects, and a user table which holds attribtues of users. To discover
the addresses of a user, join the two tables. Implement appropriate
indices and forein keys to make the expected queries efficient.
> This makes sense
> when you think about a user having e-mail service through my server,
> and using that to send e-mail from another unrelated valid address
then
> he/she owns. (validating that is also important, but not in the
scope
> of this issue).
> So I want to associate valid from addresses with the user, not the
other
> way around.
In a correct SQL schema the question is moot, SQL stores relation
tuples,
there is no notion of which is the primary element in a relation.
x R y <=> row (x,y) is present in table R
> I understand fully the point of view that postfix takes, asking
who is
> allowed to send from this e-mail address (more or less masquerade as
> this e-mail identity), which makes sense also.
Yes, but your schema is poorly designed. A good schema will naturally
support a variety of queries that select ether the "x" values for
which "x R y" is true for a given "y" or just as easily the converse.
> There may be better schema for answering the question I am asking,
> but it sounds from your response (I am assuming, possibly
dangerously)
> That there is no way to do what I want to do without creating
another
> table in the database, indexed by from address with resuting lists
of
> users. Is that correct?
I am claiming that what you can't be done with your existing schema,
perhaps it can. I am claiming that you should change your schema
as soon as you can.
> Why does postfix reject the mail when the mysql query return one or
> several valid SASL users?
To solve such questions, divide and conquer:
- Use a simple indexed file to store the sasl_sender_login table.
Make this work, resolving any issues that you find.
- Once SASL sender validation is working without SQL, create
an SQL query that given verbatim keys from the indexed file,
returns the identical RHS values. Verify with multiple "postmap -q"
tests. Once this works, use SQL in the Postfix configuration.
The above approach allows you to identify and solve the right problem.
--
Viktor.
Viktor:
Thank you. I agree with all your comments about the database schema,
which I have not yet built. I am in testing mode and want to make sure
this kind of validation will work as expected and give me the desired
results. So if I may, I'd like to focus on getting this to work.
I did the testing you suggested before I posted. Here are the tests:
I created a hash table (text file then used postmap hash:). The text
file had lines like this:
sender_address1 sasl_owner
sender_address2 sasl_owner2, sasl_owner3, sasl_owner4
and so on, for the several addresses I tested.
I then used postmap -q to verify that the lookup returned the correct
values. As speficied in the documentation for smtpd_sender_login_maps,
it returned one single SASL user name when only one user is authorized
to send from that from address and a string of comma-separated names
when multiple users are authorized to send from that from address.
I then added the line:
smtpd_sender_login_maps=hash:/path/to/map
to my main.cf and send a few messages. Postfix correctly allowed and
rejected all of the test messaages, and the logs showed the correct
reason for the rejection, and no log entries showing a successful match.
I then, again just to test, added a field to my virtual users table to
hold authorized addresses, and wrote the query noted above into a
mysql_sender_login_maps.cf file.
I used postmap -q to verify that the results of the query were correct
and were exactly the same as the results received when I used postmap -
q on the hash: file. They all were identical for the same lookup keys.
I then changed the main.cf line to:
smtpd_sender_login_maps=hash:/path/to/mysql_sender_login_maps.cf
and tried the same series of test messages.
All of the test messages failed. The ones where the lookup produced
the same comma-separated line as with the hash: file, and the ones
where the lookup produced a single result. All were rejected (as noted
in the log entry I posted previously) with the reason that the from
address was not authorized for that sasl user (not postfix's words, my
paraphrase).
I then changed the query to produce a multiple row result (rather than
a comma-separated list) and ran the same tests. Same result - all
messages were rejected.
So, I am at a loss as to how to make this work with a mysql query.
Thus my questions:
- Does postfix expect mysql to return a multiple-row result or a text
string with a comma-(or whitespace-)separated list of valid sasl users
for that from address?
- I am correct in my reading that the lookup key is the from (MAIL
FROM) address?
- I don't know what to ask about the queries that produced a single
result - these are failing, but other than the log entry showing the
failure, I don't know why.
So, while I am not quite an expert in database design, I understand
and will take your suggestions when I build the live database. Right
now, I want to make sure I can get this to work, and it's not working
as I'd expect.
I'm reasonable sure I'm missing something, but I don't know what.
Please let me know if there is further information that would point to
a solution.
Thank you so much for taking the time to help!
--Jeff
