Re: about mysql query and input keys

[Manuel Mely]

I think I will have to implement my own SMTP Access Policy [1] in this case.

1- http://www.postfix.org/SMTPD_POLICY_README.html


Phil Stracchino  schrieb am Mo. 8. Okt. 2018 um 14:34:

> On 10/8/18 6:42 AM, Manuel Mely wrote:
> > Well the tables look basically like this:
> >
> > domain: id (binary), name (string), ... other fields
> > external_domain: id (binary), name (string)
> > domain_external_domain_association: id(binary), domain__id(binary),
> > external_domain(binary), receive (int), send (int)
> >
> > So, the idea is that virtual domains (domain table) can be configured to
> > send or receive from the internet or from/to allowed external domains.
> > Therefore the domain_external_domain_association table.
> > So i can ask something like, for the virtual domain X, is there any
> > association to external_domain entries.
> >
> > query= SELECT [OK OR REJECT GOES
> > HERE] FROM domain_external_domain_association  AS deda
> > JOIN domain as d ON deda.domain_id = d.id 
> > JOIN external_domain as ed ON deda.external_domain_id = ed.id
> >  WHERE d.name  = 'HERE MY VDOMAIN' AND
> > deda.receive = 1 AND ed.name  = '%d'
> >
> > d.name  => should be recipient domain.
> > ed.name  => is the sender domain, which works fine with
> > %d, as I have another check before with a similar query.
>
> OK, so %d gets substituted with domain name?
>
>
> > The problem is, I want to use in a check_sender_access information about
> > the recipient (for the joins in the query), which i think does not work.
>
> Yeah, I don't see anything wrong with the query itself, but whether
> it'll work in a check_sender_access rule is another question, which I
> can't answer because I've never tried to do that.
>
>
> --
>   Phil Stracchino
>   Babylon Communications
>   ph...@caerllewys.net
>   p...@co.ordinate.org
>   Landline: +1.603.293.8485
>   Mobile:   +1.603.998.6958
>

Re: about mysql query and input keys

[Phil Stracchino]

On 10/8/18 6:42 AM, Manuel Mely wrote:
> Well the tables look basically like this:
> 
> domain: id (binary), name (string), ... other fields
> external_domain: id (binary), name (string)
> domain_external_domain_association: id(binary), domain__id(binary),
> external_domain(binary), receive (int), send (int)
> 
> So, the idea is that virtual domains (domain table) can be configured to
> send or receive from the internet or from/to allowed external domains.
> Therefore the domain_external_domain_association table.
> So i can ask something like, for the virtual domain X, is there any
> association to external_domain entries.
> 
> query= SELECT [OK OR REJECT GOES
> HERE] FROM domain_external_domain_association  AS deda
> JOIN domain as d ON deda.domain_id = d.id 
> JOIN external_domain as ed ON deda.external_domain_id = ed.id
>  WHERE d.name  = 'HERE MY VDOMAIN' AND
> deda.receive = 1 AND ed.name  = '%d'
> 
> d.name  => should be recipient domain.
> ed.name  => is the sender domain, which works fine with
> %d, as I have another check before with a similar query.

OK, so %d gets substituted with domain name?


> The problem is, I want to use in a check_sender_access information about
> the recipient (for the joins in the query), which i think does not work.

Yeah, I don't see anything wrong with the query itself, but whether
it'll work in a check_sender_access rule is another question, which I
can't answer because I've never tried to do that.


-- 
  Phil Stracchino
  Babylon Communications
  ph...@caerllewys.net
  p...@co.ordinate.org
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958

Re: about mysql query and input keys

[Manuel Mely]

Well the tables look basically like this:

domain: id (binary), name (string), ... other fields
external_domain: id (binary), name (string)
domain_external_domain_association: id(binary), domain__id(binary),
external_domain(binary), receive (int), send (int)

So, the idea is that virtual domains (domain table) can be configured to
send or receive from the internet or from/to allowed external domains.
Therefore the domain_external_domain_association table.
So i can ask something like, for the virtual domain X, is there any
association to external_domain entries.

query= SELECT [OK OR REJECT GOES
HERE] FROM domain_external_domain_association  AS deda
JOIN domain as d ON deda.domain_id = d.id
JOIN external_domain as ed ON deda.external_domain_id = ed.id WHERE
d.name = 'HERE
MY VDOMAIN' AND deda.receive = 1 AND ed.name = '%d'

d.name => should be recipient domain.
ed.name => is the sender domain, which works fine with %d, as I have
another check before with a similar query.

The problem is, I want to use in a check_sender_access information about
the recipient (for the joins in the query), which i think does not work.

[OK OR REJECT GOES HERE] , it is just a place holder for this e-mail :)

On Mon, Oct 8, 2018 at 3:55 AM Phil Stracchino  wrote:

> On 10/7/18 7:45 PM, Manuel Mely wrote:
> > Hi there,
> >
> > I'm designing a mail system with virtual domains and have the following
> > requirement:
> >
> > A virtual domain is able to send/receive mails to/from the Internet or
> > to/from custom "external domains".
> >
> > For that i'm using a MySQL table called
> > "domain_external_domain_association"  which connects my virtual domains
> > table and another external_domain table.
> >
> > Somewhere in my set of smtpd_restrictions_classes there is one class
> > that uses a check_sender_access where i need to check if the sender
> > domain (external domain) is in this "domain_external_domain_association"
> > when the recipient domain is X (my virtual domain)
> >
> > So the query looks like this:
> >
> > query= SELECT [OK OR REJECT GOES
> HERE] FROM domain_external_domain_association  AS deda
> > JOIN domain as d ON deda.domain_id = d.id
> > JOIN external_domain as ed ON deda.external_domain_id = ed.id
> > WHERE d.name = 'HERE MY VDOMAIN' AND deda.receive = 1 AND ed.name = '%d'
> >
> > Also, the sql query explains the problem by itself :)
>
>
> Actually, no, it doesn't, entirely.  That isn't very clear at all.
>
> What do the tables look like?
> Is that '%d' a formatting placeholder intended to be replaced before the
> query is evaluated, or are you trying to match ed.name against a string
> containing a wildcard?  (If so, you need to be using ed.name LIKE '%d',
> not ed.name = '%d'.)
> Is ed.name an integer or s string?  If it's a string, you probably want
> %s there, not %d.  %d is the formatting code for an integer.
> And what do you mean by [OK OR REJECT GOES HERE]?  Are you trying to
> SELECT a column, a string value, a string literal...?
>
>
> > I think this is not possible to do, at least in the way i want to do it,
> > because i'm trying to use in the query some information that the
> > check_sender_access is not aware of.
>
> Since you haven't provided enough information to make sense of your
> query, it's rather difficult to tell that.
>
>
>
> --
>   Phil Stracchino
>   Babylon Communications
>   ph...@caerllewys.net
>   p...@co.ordinate.org
>   Landline: +1.603.293.8485
>   Mobile:   +1.603.998.6958
>

Re: about mysql query and input keys

[Phil Stracchino]

On 10/7/18 7:45 PM, Manuel Mely wrote:
> Hi there,
> 
> I'm designing a mail system with virtual domains and have the following
> requirement:
> 
> A virtual domain is able to send/receive mails to/from the Internet or
> to/from custom "external domains".
>  
> For that i'm using a MySQL table called 
> "domain_external_domain_association"  which connects my virtual domains
> table and another external_domain table.
> 
> Somewhere in my set of smtpd_restrictions_classes there is one class
> that uses a check_sender_access where i need to check if the sender
> domain (external domain) is in this "domain_external_domain_association"
> when the recipient domain is X (my virtual domain)
> 
> So the query looks like this:
> 
> query= SELECT [OK OR REJECT GOES HERE] FROM 
> domain_external_domain_association  AS deda
> JOIN domain as d ON deda.domain_id = d.id
> JOIN external_domain as ed ON deda.external_domain_id = ed.id
> WHERE d.name = 'HERE MY VDOMAIN' AND deda.receive = 1 AND ed.name = '%d'
> 
> Also, the sql query explains the problem by itself :)


Actually, no, it doesn't, entirely.  That isn't very clear at all.

What do the tables look like?
Is that '%d' a formatting placeholder intended to be replaced before the
query is evaluated, or are you trying to match ed.name against a string
containing a wildcard?  (If so, you need to be using ed.name LIKE '%d',
not ed.name = '%d'.)
Is ed.name an integer or s string?  If it's a string, you probably want
%s there, not %d.  %d is the formatting code for an integer.
And what do you mean by [OK OR REJECT GOES HERE]?  Are you trying to
SELECT a column, a string value, a string literal...?


> I think this is not possible to do, at least in the way i want to do it,
> because i'm trying to use in the query some information that the
> check_sender_access is not aware of.

Since you haven't provided enough information to make sense of your
query, it's rather difficult to tell that.



-- 
  Phil Stracchino
  Babylon Communications
  ph...@caerllewys.net
  p...@co.ordinate.org
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958