restriction class and mysql lookup

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

restriction class and mysql lookup

Zhang Huangbin
Hi, all.

I'm confused about restriction class and the mysql lookup file format.
What i should use in 'query'?

Thanks very much. :)

MySQL table structure:
----cut<<----
+-------------------+--------------+------+-----+---------------------+-------+
| Field             | Type         | Null | Key | Default             |
Extra |
+-------------------+--------------+------+-----+---------------------+-------+
| username          | varchar(255) | NO   |     |                    
|       |
| restriction_class | varchar(255) | NO   |     |                    
|       |
| restricteddomain  | varchar(255) | NO   |     |                    
|       |
| created           | datetime     | NO   |     | 0000-00-00 00:00:00
|       |
| expired           | datetime     | NO   |     | 9999-12-31 00:00:00
|       |
| modified          | datetime     | NO   |     | 0000-00-00 00:00:00
|       |
+-------------------+--------------+------+-----+---------------------+-------+
----cut<<----

I have this record in table 'restrictions':
----cut<<----
mysql> select username,restriction_class,restricteddomain from restrictions;
+----------+-----------------------+------------------+
| username | restriction_class     | restricteddomain |
+----------+-----------------------+------------------+
| [hidden email] | internal_deliver_only | b.cn             |
+----------+-----------------------+------------------+
----cut<<----

In Postfix main.cf:
----cut<<----
smtpd_restriction_classes = internal_deliver_only, internal_recipient_only
internal_deliver_only = check_recipient_access
mysql:/etc/postfix/internal_deliver_only.mysql, reject
internal_recipient_only = check_sender_access
mysql:/etc/postfix/internal_recipient_only.mysql, reject
----cut<<----

File: /etc/postfix/internal_deliver_only.mysql:
----cut<<----
user            = vmail
password        = 20396$5849@15339!24393&16544*4832)12715
hosts           = 127.0.0.1
port            = 3306
dbname          = vmail
query           = SELECT restricteddomain FROM restrictions WHERE
username='%s' AND restriction_class='internal_deliver_only'
----cut<<----


Output of 'postconf -n':
----<<----
alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases
allow_min_user = no
broken_sasl_auth_clients = yes
command_directory = /usr/sbin
config_directory = /etc/postfix
content_filter = smtp-amavis:[127.0.0.1]:10024
daemon_directory = /usr/libexec/postfix
debug_peer_level = 2
delay_warning_time = 4h
enable_original_recipient = no
header_checks = pcre:/etc/postfix/header_checks
home_mailbox = Maildir/
html_directory = no
inet_interfaces = all
local_recipient_maps = $alias_maps $virtual_alias_maps $virtual_mailbox_maps
mail_name = iRedMail
mail_owner = postfix
mail_version = 0.2-rc4
mailbox_command = /usr/libexec/dovecot/deliver
mailbox_size_limit = 15728640
mailq_path = /usr/bin/mailq.postfix
manpage_directory = /usr/share/man
maximal_backoff_time = 4000s
message_size_limit = 15728640
mydestination = $myhostname, localhost, localhost.localdomain,
localhost.$myhostname, mysql:/etc/postfix/virtual_domains.mysql
mydomain = r6.iredmail.org
myhostname = r6.iredmail.org
mynetworks = 127.0.0.0/8
myorigin = r6.iredmail.org
newaliases_path = /usr/bin/newaliases.postfix
queue_directory = /var/spool/postfix
readme_directory = /usr/share/doc/postfix-2.3.14/README_FILES
receive_override_options = no_address_mappings
recipient_bcc_maps = mysql:/etc/postfix/recipient_bcc_maps_domain.mysql,
mysql:/etc/postfix/recipient_bcc_maps_user.mysql
relay_domains = $mydestination
sample_directory = /usr/share/doc/postfix-2.3.14/samples
sender_bcc_maps = mysql:/etc/postfix/sender_bcc_maps_domain.mysql,
mysql:/etc/postfix/sender_bcc_maps_user.mysql
sendmail_path = /usr/sbin/sendmail.postfix
setgid_group = postdrop
smtpd_data_restrictions = reject_unauth_pipelining
smtpd_enforce_tls = no
smtpd_helo_required = yes
smtpd_helo_restrictions = permit_mynetworks,permit_sasl_authenticated,
check_helo_access pcre:/etc/postfix/helo_access.pcre
smtpd_recipient_restrictions = check_sender_access
mysql:/etc/postfix/sender_access.mysql, check_recipient_access
mysql:/etc/postfix/sender_access.mysql, permit_mynetworks,
reject_unknown_sender_domain, reject_unknown_recipient_domain,
reject_non_fqdn_sender, reject_non_fqdn_recipient,
permit_sasl_authenticated, reject_unauth_destination,
reject_non_fqdn_helo_hostname, reject_invalid_helo_hostname,
check_policy_service inet:127.0.0.1:10031
smtpd_reject_unlisted_recipient = yes
smtpd_reject_unlisted_sender = yes
smtpd_restriction_classes = internal_deliver_only, internal_recipient_only
smtpd_sasl_auth_enable = yes
smtpd_sasl_authenticated_header = no
smtpd_sasl_local_domain =
smtpd_sasl_path = private/auth
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = dovecot
smtpd_sender_login_maps = mysql:/etc/postfix/sender_login_maps.mysql
smtpd_sender_restrictions = reject_authenticated_sender_login_mismatch,
reject_sender_login_mismatch,
reject_unauthenticated_sender_login_mismatch, permit_sasl_authenticated,
permit_mynetworks
smtpd_tls_cert_file = /etc/postfix/certs/postfixCert.pem
smtpd_tls_key_file = /etc/postfix/certs/postfixKey.pem
smtpd_tls_loglevel = 0
smtpd_tls_security_level = may
tls_random_source = dev:/dev/urandom
transport_maps = mysql:/etc/postfix/transport_maps.mysql
unknown_local_recipient_reject_code = 550
virtual_alias_maps = mysql:/etc/postfix/virtual_alias_maps.mysql
virtual_gid_maps = static:2000
virtual_mailbox_base = /home/vmail
virtual_mailbox_maps = mysql:/etc/postfix/virtual_mailbox_maps.mysql
virtual_minimum_uid = 2000
virtual_transport = dovecot
virtual_uid_maps = static:2000
internal_deliver_only = check_recipient_access
mysql:/etc/postfix/internal_deliver_only.mysql, reject
internal_recipient_only = check_sender_access
mysql:/etc/postfix/internal_recipient_only.mysql, reject
----<<----
Reply | Threaded
Open this post in threaded view
|

Re: restriction class and mysql lookup

mouss-2
Zhang Huangbin wrote:

> Hi, all.
>
> I'm confused about restriction class and the mysql lookup file format.
> What i should use in 'query'?
>
> Thanks very much. :)
>
> MySQL table structure:
> ----cut<<----
> +-------------------+--------------+------+-----+---------------------+-------+
>
> | Field             | Type         | Null | Key | Default             |
> Extra |
> +-------------------+--------------+------+-----+---------------------+-------+
>
> | username          | varchar(255) | NO   |     |                    
> |       |
> | restriction_class | varchar(255) | NO   |     |                    
> |       |
> | restricteddomain  | varchar(255) | NO   |     |                    
> |       |
> | created           | datetime     | NO   |     | 0000-00-00 00:00:00
> |       |
> | expired           | datetime     | NO   |     | 9999-12-31 00:00:00
> |       |
> | modified          | datetime     | NO   |     | 0000-00-00 00:00:00
> |       |
> +-------------------+--------------+------+-----+---------------------+-------+
>
> ----cut<<----
>
> I have this record in table 'restrictions':
> ----cut<<----
> mysql> select username,restriction_class,restricteddomain from
> restrictions;
> +----------+-----------------------+------------------+
> | username | restriction_class     | restricteddomain |
> +----------+-----------------------+------------------+
> | [hidden email] | internal_deliver_only | b.cn             |
> +----------+-----------------------+------------------+
> ----cut<<----
>
> In Postfix main.cf:
> ----cut<<----
> smtpd_restriction_classes = internal_deliver_only, internal_recipient_only
> internal_deliver_only = check_recipient_access
> mysql:/etc/postfix/internal_deliver_only.mysql, reject
> internal_recipient_only = check_sender_access
> mysql:/etc/postfix/internal_recipient_only.mysql, reject
> ----cut<<----
>
> File: /etc/postfix/internal_deliver_only.mysql:
> ----cut<<----
> user            = vmail
> password        = 20396$5849@15339!24393&16544*4832)12715
> hosts           = 127.0.0.1
> port            = 3306
> dbname          = vmail
> query           = SELECT restricteddomain FROM restrictions WHERE
> username='%s' AND restriction_class='internal_deliver_only'
> ----cut<<----

so your check_recipient_access returns 'b.cn', which is not a valid action.

what are you trying to do? you cannot implement checks based on multiple
fields at a time, so your table design is suspicious (username and
restricteddomain are independent).


>
>
> Output of 'postconf -n':
> ----<<----
> alias_database = hash:/etc/aliases
> alias_maps = hash:/etc/aliases
> allow_min_user = no
> broken_sasl_auth_clients = yes
> command_directory = /usr/sbin
> config_directory = /etc/postfix
> content_filter = smtp-amavis:[127.0.0.1]:10024
> daemon_directory = /usr/libexec/postfix
> debug_peer_level = 2
> delay_warning_time = 4h
> enable_original_recipient = no
> header_checks = pcre:/etc/postfix/header_checks
> home_mailbox = Maildir/
> html_directory = no
> inet_interfaces = all
> local_recipient_maps = $alias_maps $virtual_alias_maps
> $virtual_mailbox_maps
> mail_name = iRedMail
> mail_owner = postfix
> mail_version = 0.2-rc4
> mailbox_command = /usr/libexec/dovecot/deliver
> mailbox_size_limit = 15728640
> mailq_path = /usr/bin/mailq.postfix
> manpage_directory = /usr/share/man
> maximal_backoff_time = 4000s
> message_size_limit = 15728640
> mydestination = $myhostname, localhost, localhost.localdomain,
> localhost.$myhostname, mysql:/etc/postfix/virtual_domains.mysql
> mydomain = r6.iredmail.org
> myhostname = r6.iredmail.org
> mynetworks = 127.0.0.0/8
> myorigin = r6.iredmail.org
> newaliases_path = /usr/bin/newaliases.postfix
> queue_directory = /var/spool/postfix
> readme_directory = /usr/share/doc/postfix-2.3.14/README_FILES
> receive_override_options = no_address_mappings
> recipient_bcc_maps = mysql:/etc/postfix/recipient_bcc_maps_domain.mysql,
> mysql:/etc/postfix/recipient_bcc_maps_user.mysql
> relay_domains = $mydestination
> sample_directory = /usr/share/doc/postfix-2.3.14/samples
> sender_bcc_maps = mysql:/etc/postfix/sender_bcc_maps_domain.mysql,
> mysql:/etc/postfix/sender_bcc_maps_user.mysql
> sendmail_path = /usr/sbin/sendmail.postfix
> setgid_group = postdrop
> smtpd_data_restrictions = reject_unauth_pipelining
> smtpd_enforce_tls = no
> smtpd_helo_required = yes
> smtpd_helo_restrictions = permit_mynetworks,permit_sasl_authenticated,
> check_helo_access pcre:/etc/postfix/helo_access.pcre
> smtpd_recipient_restrictions = check_sender_access
> mysql:/etc/postfix/sender_access.mysql, check_recipient_access
> mysql:/etc/postfix/sender_access.mysql, permit_mynetworks,
> reject_unknown_sender_domain, reject_unknown_recipient_domain,
> reject_non_fqdn_sender, reject_non_fqdn_recipient,
> permit_sasl_authenticated, reject_unauth_destination,
> reject_non_fqdn_helo_hostname, reject_invalid_helo_hostname,
> check_policy_service inet:127.0.0.1:10031
> smtpd_reject_unlisted_recipient = yes
> smtpd_reject_unlisted_sender = yes
> smtpd_restriction_classes = internal_deliver_only, internal_recipient_only
> smtpd_sasl_auth_enable = yes
> smtpd_sasl_authenticated_header = no
> smtpd_sasl_local_domain =
> smtpd_sasl_path = private/auth
> smtpd_sasl_security_options = noanonymous
> smtpd_sasl_type = dovecot
> smtpd_sender_login_maps = mysql:/etc/postfix/sender_login_maps.mysql
> smtpd_sender_restrictions = reject_authenticated_sender_login_mismatch,
> reject_sender_login_mismatch,
> reject_unauthenticated_sender_login_mismatch, permit_sasl_authenticated,
> permit_mynetworks
> smtpd_tls_cert_file = /etc/postfix/certs/postfixCert.pem
> smtpd_tls_key_file = /etc/postfix/certs/postfixKey.pem
> smtpd_tls_loglevel = 0
> smtpd_tls_security_level = may
> tls_random_source = dev:/dev/urandom
> transport_maps = mysql:/etc/postfix/transport_maps.mysql
> unknown_local_recipient_reject_code = 550
> virtual_alias_maps = mysql:/etc/postfix/virtual_alias_maps.mysql
> virtual_gid_maps = static:2000
> virtual_mailbox_base = /home/vmail
> virtual_mailbox_maps = mysql:/etc/postfix/virtual_mailbox_maps.mysql
> virtual_minimum_uid = 2000
> virtual_transport = dovecot
> virtual_uid_maps = static:2000
> internal_deliver_only = check_recipient_access
> mysql:/etc/postfix/internal_deliver_only.mysql, reject
> internal_recipient_only = check_sender_access
> mysql:/etc/postfix/internal_recipient_only.mysql, reject
> ----<<----

Reply | Threaded
Open this post in threaded view
|

Re: restriction class and mysql lookup

Zhang Huangbin
mouss wrote:
> so your check_recipient_access returns 'b.cn', which is not a valid
> action.
>
> what are you trying to do? you cannot implement checks based on
> multiple fields at a time, so your table design is suspicious
> (username and restricteddomain are independent).

Thanks mouss. :)

i want to restrict this user, and 'internal_deliver_only' means user
'[hidden email]' can *ONLY* send mail to restricteddomain (b.cn).

In my record:
----cut<<----
mysql> select username,restriction_class,restricteddomain from
restrictions;
+----------+-----------------------+------------------+
| username | restriction_class     | restricteddomain |
+----------+-----------------------+------------------+
| [hidden email] | internal_deliver_only | b.cn             |
+----------+-----------------------+------------------+
----cut<<----

Replace mysql lookup by hash file, it works:
----cut<<----
b.cn   OK
----cut<<----

So, what SQL query should i use in mysql lookup file?

Thanks very much. :)

--
Best Regards.

Zhang Huangbin

- iRedMail: Mail Server Solution for Red Hat(R) Enterprise Linux &
  CentOS 5.x: http://iRedMail.googlecode.com/



Reply | Threaded
Open this post in threaded view
|

Re: restriction class and mysql lookup

Magnus Bäck
On Tuesday, August 19, 2008 at 04:48 CEST,
     Zhang Huangbin <[hidden email]> wrote:

[...]

> In my record:
> ----cut<<----
> mysql> select username,restriction_class,restricteddomain from
> restrictions;
> +----------+-----------------------+------------------+
> | username | restriction_class     | restricteddomain |
> +----------+-----------------------+------------------+
> | [hidden email] | internal_deliver_only | b.cn             |
> +----------+-----------------------+------------------+
> ----cut<<----
>
> Replace mysql lookup by hash file, it works:
> ----cut<<----
> b.cn   OK
> ----cut<<----
>
> So, what SQL query should i use in mysql lookup file?

Use any query that returns "OK" is the lookup key is "b.cn".

query = SELECT "OK" FROM restrictions WHERE restricteddomain = '%s'

--
Magnus Bäck
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: restriction class and mysql lookup

Zhang Huangbin
Magnus � wrote:

> On Tuesday, August 19, 2008 at 04:48 CEST,
>      Zhang Huangbin <[hidden email]> wrote:
>
> [...]
>
>  
>> In my record:
>> ----cut<<----
>> mysql> select username,restriction_class,restricteddomain from
>> restrictions;
>> +----------+-----------------------+------------------+
>> | username | restriction_class     | restricteddomain |
>> +----------+-----------------------+------------------+
>> | [hidden email] | internal_deliver_only | b.cn             |
>> +----------+-----------------------+------------------+
>> ----cut<<----
>>
>> Replace mysql lookup by hash file, it works:
>> ----cut<<----
>> b.cn   OK
>> ----cut<<----
>>
>> So, what SQL query should i use in mysql lookup file?
>>    
>
> Use any query that returns "OK" is the lookup key is "b.cn".
>
> query = SELECT "OK" FROM restrictions WHERE restricteddomain = '%s'
>  

Thanks Magnus. :)

It works. But which filed should i use to match 'username' ([hidden email]) in
query string?

query = SELECT "OK" FROM restrictions WHERE restricteddomain='%s' AND
username='???'

Thanks very much.

Reply | Threaded
Open this post in threaded view
|

Re: restriction class and mysql lookup

mouss-2
Zhang Huangbin wrote:

> Magnus � wrote:
>> On Tuesday, August 19, 2008 at 04:48 CEST,
>>      Zhang Huangbin <[hidden email]> wrote:
>>
>> [...]
>>
>>  
>>> In my record:
>>> ----cut<<----
>>> mysql> select username,restriction_class,restricteddomain from
>>> restrictions;
>>> +----------+-----------------------+------------------+
>>> | username | restriction_class     | restricteddomain |
>>> +----------+-----------------------+------------------+
>>> | [hidden email] | internal_deliver_only | b.cn             |
>>> +----------+-----------------------+------------------+
>>> ----cut<<----
>>>
>>> Replace mysql lookup by hash file, it works:
>>> ----cut<<----
>>> b.cn   OK
>>> ----cut<<----
>>>
>>> So, what SQL query should i use in mysql lookup file?
>>>    
>>
>> Use any query that returns "OK" is the lookup key is "b.cn".
>>
>> query = SELECT "OK" FROM restrictions WHERE restricteddomain = '%s'
>>  
>
> Thanks Magnus. :)
>
> It works. But which filed should i use to match 'username' ([hidden email]) in
> query string?
>
> query = SELECT "OK" FROM restrictions WHERE restricteddomain='%s' AND
> username='???'


as I said earlier, you can't check multiple fields at a time. you need a
policy server. so your mysql table design doesn't match postfix
capabilities.

In case this is not clear, you cannot implement this:
        if sender is foo and recipient is bar, then do blah
directly. you need restriction classes, but restriction classes must be
defined ahead of time in main.cf. they cannot be results of sql lookup.



Reply | Threaded
Open this post in threaded view
|

Re: restriction class and mysql lookup

Zhang Huangbin
In reply to this post by Zhang Huangbin
Thanks mouss. :)

Use hash file as replacement now.