Re: userbase question
Hi Gilles, Thank you for your advice about using wrappers. I decided to implement an mda wrapper as per your suggestion. It is interesting that I still needed to specify either an mbox or maildir in the syntax when I specify a wrapper. In this case, it doesn't seem to matter if I use mbox or maildir because dovecot's LDA is doing the final delivery. This works but I might be doing it wrong. action "local" mbox wrapper "deliver" alias action "domain" mbox wrapper "deliver" virtual match for local action "local" match from any for domain action "domain" I have to agree that using the mda wrapper feature is a heck of a lot cleaner. I am even going to do some testing using OpenSMTPD for final delivery now that there is explicit support for junk mail delivery. I think the reason that the userbase didn't work is that I am using dovecot for final delivery of the email. Below is a patch for the smtpd.conf(8) man page to reflect where to use the wrapper specified by mda wrapper. --- smtpd.conf.5Sat Sep 1 08:52:32 2018 +++ smtpd.conf.5 Sat Sep 1 08:55:23 2018 @@ -156,6 +156,9 @@ .Pq see Sx FORMAT SPECIFIERS . .It Cm relay Relay the message to another SMTP server. +.It Cm wrapper Ar name +Use a wrapper specified by +.Cm mda wrapper .It command. .El .Pp The local delivery methods support additional options: On Sat, Sep 1, 2018 at 8:01 AM Gilles Chehade wrote: > > On Mon, Aug 27, 2018 at 09:54:05AM -0400, Matt Schwartz wrote: > > I am hoping not to have to use sqlite tables. I like the simplicity of > > file-based configuration. > > just for the record: > > besides table-specific features, all smtpd features are usable from file > configurations since I write the features for the file backend _then_ we > adapt the other backends. > > > > > On Mon, Aug 27, 2018 at 9:47 AM Reio Remma wrote: > > > > > > Iirc I got the .forward file working with sqlite tables, where the user > > > query also returned the virtual user???s maildir as an extra parameter. > > > > > > Good luck, > > > Reio > > > > > > > On 27 Aug 2018, at 16:11, Matt Schwartz > > > > wrote: > > > > > > > > Hello misc@, > > > > > > > > Below is my configuration file. I am trying to use the userbase > > > > parameter and when I try to send an email to myself, I get the 550 > > > > Invalid Recipient error. I am trying to get the usrbase parameter > > > > working so that I can add a .forward file for virtual users as per the > > > > table(5) man page. If I don't use the userbase parameter, mail > > > > delivery works just fine. I am not certain what I am doing wrong here. > > > > > > > > #smtpd.conf > > > > pki mail cert "/etc/ssl/smtpd.crt" > > > > pki mail key "/etc/ssl/private/smtpd.key" > > > > > > > > table aliases file:/etc/mail/aliases > > > > table addrnames file:/etc/mail/addrnames > > > > table credentials file:/etc/mail/credentials > > > > table domains file:/etc/mail/domains > > > > table virtuals file:/etc/mail/virtuals > > > > table usrbase file:/etc/mail/usrbase > > > > table rejects file:/etc/mail/rejects > > > > > > > > # Listeners > > > > # > > > > listen on lo0 > > > > listen on lo0 port 10028 tag DKIM > > > > listen on vio0 tls pki mail hostnames > > > > listen on vio0 port 587 tls-require pki mail auth \ > > > >hostnames > > > > > > > > # Actions > > > > # > > > > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > > > %{rcpt}'" alias > > > > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > > > %{rcpt}'" userbase virtual > > > > action "dkim" relay host smtp://127.0.0.1:10027 > > > > action "relay" relay > > > > > > > > # Incoming > > > > # > > > > match from any mail-from for any reject > > > > match from local for local action "local" > > > > match from any for domain action "domain" > > > > > > > > # Outgoing > > > > # > > > > match tag DKIM for any action "relay" > > > > match from local for any action "dkim" > > > > match auth from any for any action "dkim" > > > > > > > > #usrbase > > > > m...@example.org 2000:2000:/var/vmail/example.org/matt > > > > > > > > #virtuals > > > > m...@example.org vmail > > > > > > > > Thanks in advance, > > > > Matt > > > > > > > > -- > > > > You received this mail because you are subscribed to misc@opensmtpd.org > > > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > > > > > > > > > > > > -- > > > You received this mail because you are subscribed to misc@opensmtpd.org > > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > > > > > > -- > > You received this mail because you are subscribed to misc@opensmtpd.org > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > > > -- > Gilles Chehade > > https://www.poolp.org @poolpOrg -- You received this mail because you are
Re: userbase question
On Mon, Aug 27, 2018 at 09:54:05AM -0400, Matt Schwartz wrote: > I am hoping not to have to use sqlite tables. I like the simplicity of > file-based configuration. just for the record: besides table-specific features, all smtpd features are usable from file configurations since I write the features for the file backend _then_ we adapt the other backends. > On Mon, Aug 27, 2018 at 9:47 AM Reio Remma wrote: > > > > Iirc I got the .forward file working with sqlite tables, where the user > > query also returned the virtual user???s maildir as an extra parameter. > > > > Good luck, > > Reio > > > > > On 27 Aug 2018, at 16:11, Matt Schwartz wrote: > > > > > > Hello misc@, > > > > > > Below is my configuration file. I am trying to use the userbase > > > parameter and when I try to send an email to myself, I get the 550 > > > Invalid Recipient error. I am trying to get the usrbase parameter > > > working so that I can add a .forward file for virtual users as per the > > > table(5) man page. If I don't use the userbase parameter, mail > > > delivery works just fine. I am not certain what I am doing wrong here. > > > > > > #smtpd.conf > > > pki mail cert "/etc/ssl/smtpd.crt" > > > pki mail key "/etc/ssl/private/smtpd.key" > > > > > > table aliases file:/etc/mail/aliases > > > table addrnames file:/etc/mail/addrnames > > > table credentials file:/etc/mail/credentials > > > table domains file:/etc/mail/domains > > > table virtuals file:/etc/mail/virtuals > > > table usrbase file:/etc/mail/usrbase > > > table rejects file:/etc/mail/rejects > > > > > > # Listeners > > > # > > > listen on lo0 > > > listen on lo0 port 10028 tag DKIM > > > listen on vio0 tls pki mail hostnames > > > listen on vio0 port 587 tls-require pki mail auth \ > > >hostnames > > > > > > # Actions > > > # > > > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > > %{rcpt}'" alias > > > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > > %{rcpt}'" userbase virtual > > > action "dkim" relay host smtp://127.0.0.1:10027 > > > action "relay" relay > > > > > > # Incoming > > > # > > > match from any mail-from for any reject > > > match from local for local action "local" > > > match from any for domain action "domain" > > > > > > # Outgoing > > > # > > > match tag DKIM for any action "relay" > > > match from local for any action "dkim" > > > match auth from any for any action "dkim" > > > > > > #usrbase > > > m...@example.org 2000:2000:/var/vmail/example.org/matt > > > > > > #virtuals > > > m...@example.org vmail > > > > > > Thanks in advance, > > > Matt > > > > > > -- > > > You received this mail because you are subscribed to misc@opensmtpd.org > > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > > > > > > > > -- > > You received this mail because you are subscribed to misc@opensmtpd.org > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > > > -- > You received this mail because you are subscribed to misc@opensmtpd.org > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > -- Gilles Chehade https://www.poolp.org @poolpOrg -- You received this mail because you are subscribed to misc@opensmtpd.org To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
Re: userbase question
On Mon, Aug 27, 2018 at 09:11:02AM -0400, Matt Schwartz wrote: > Hello misc@, > > Below is my configuration file. I am trying to use the userbase > parameter and when I try to send an email to myself, I get the 550 > Invalid Recipient error. I am trying to get the usrbase parameter > working so that I can add a .forward file for virtual users as per the > table(5) man page. If I don't use the userbase parameter, mail > delivery works just fine. I am not certain what I am doing wrong here. > > #smtpd.conf > pki mail cert "/etc/ssl/smtpd.crt" > pki mail key "/etc/ssl/private/smtpd.key" > > table aliases file:/etc/mail/aliases > table addrnames file:/etc/mail/addrnames > table credentials file:/etc/mail/credentials > table domains file:/etc/mail/domains > table virtuals file:/etc/mail/virtuals > table usrbase file:/etc/mail/usrbase > table rejects file:/etc/mail/rejects > > # Listeners > # > listen on lo0 > listen on lo0 port 10028 tag DKIM > listen on vio0 tls pki mail hostnames > listen on vio0 port 587 tls-require pki mail auth \ > hostnames > > # Actions > # > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > %{rcpt}'" alias > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > %{rcpt}'" userbase virtual > action "dkim" relay host smtp://127.0.0.1:10027 > action "relay" relay > you might want to have a look at mda wrappers, it will simplify your actions considerably ;-) > # Incoming > # > match from any mail-from for any reject > match from local for local action "local" > match from any for domain action "domain" > > # Outgoing > # > match tag DKIM for any action "relay" > match from local for any action "dkim" > match auth from any for any action "dkim" > > #usrbase > m...@example.org 2000:2000:/var/vmail/example.org/matt > userbase maps a user to an account, so you shouldn't use an email address here, it should be 'vmail' since that's what you use as the delivery user in your virtuals table below: > #virtuals > m...@example.org vmail > -- Gilles Chehade https://www.poolp.org @poolpOrg -- You received this mail because you are subscribed to misc@opensmtpd.org To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
Re: userbase question
Sent from my Verizon SmartphoneOn Aug 27, 2018 8:54 AM, Matt Schwartz wrote:>> I am hoping not to have to use sqlite tables. I like the simplicity of > file-based configuration. > On Mon, Aug 27, 2018 at 9:47 AM Reio Remma wrote: > > > > Iirc I got the .forward file working with sqlite tables, where the user query also returned the virtual user’s maildir as an extra parameter. > > > > Good luck, > > Reio > > > > > On 27 Aug 2018, at 16:11, Matt Schwartz wrote: > > > > > > Hello misc@, > > > > > > Below is my configuration file. I am trying to use the userbase > > > parameter and when I try to send an email to myself, I get the 550 > > > Invalid Recipient error. I am trying to get the usrbase parameter > > > working so that I can add a .forward file for virtual users as per the > > > table(5) man page. If I don't use the userbase parameter, mail > > > delivery works just fine. I am not certain what I am doing wrong here. > > > > > > #smtpd.conf > > > pki mail cert "/etc/ssl/smtpd.crt" > > > pki mail key "/etc/ssl/private/smtpd.key" > > > > > > table aliases file:/etc/mail/aliases > > > table addrnames file:/etc/mail/addrnames > > > table credentials file:/etc/mail/credentials > > > table domains file:/etc/mail/domains > > > table virtuals file:/etc/mail/virtuals > > > table usrbase file:/etc/mail/usrbase > > > table rejects file:/etc/mail/rejects > > > > > > # Listeners > > > # > > > listen on lo0 > > > listen on lo0 port 10028 tag DKIM > > > listen on vio0 tls pki mail hostnames > > > listen on vio0 port 587 tls-require pki mail auth \ > > > hostnames > > > > > > # Actions > > > # > > > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > > %{rcpt}'" alias > > > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > > %{rcpt}'" userbase virtual > > > action "dkim" relay host smtp://127.0.0.1:10027 > > > action "relay" relay > > > > > > # Incoming > > > # > > > match from any mail-from for any reject > > > match from local for local action "local" > > > match from any for domain action "domain" > > > > > > # Outgoing > > > # > > > match tag DKIM for any action "relay" > > > match from local for any action "dkim" > > > match auth from any for any action "dkim" > > > > > > #usrbase > > > m...@example.org 2000:2000:/var/vmail/example.org/matt > > > > > > #virtuals > > > m...@example.org vmail > > > > > > Thanks in advance, > > > Matt > > > > > > -- > > > You received this mail because you are subscribed to misc@opensmtpd.org > > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > > > > > > > > -- > > You received this mail because you are subscribed to misc@opensmtpd.org > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > >> -- > You received this mail because you are subscribed to misc@opensmtpd.org > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org >It seems to be a bug. Look at the thread about forwarding a single email. He has the same issue. I switched to MySQL tables about a year ago and it is so much easier.
Re: userbase question
I am hoping not to have to use sqlite tables. I like the simplicity of file-based configuration. On Mon, Aug 27, 2018 at 9:47 AM Reio Remma wrote: > > Iirc I got the .forward file working with sqlite tables, where the user query > also returned the virtual user’s maildir as an extra parameter. > > Good luck, > Reio > > > On 27 Aug 2018, at 16:11, Matt Schwartz wrote: > > > > Hello misc@, > > > > Below is my configuration file. I am trying to use the userbase > > parameter and when I try to send an email to myself, I get the 550 > > Invalid Recipient error. I am trying to get the usrbase parameter > > working so that I can add a .forward file for virtual users as per the > > table(5) man page. If I don't use the userbase parameter, mail > > delivery works just fine. I am not certain what I am doing wrong here. > > > > #smtpd.conf > > pki mail cert "/etc/ssl/smtpd.crt" > > pki mail key "/etc/ssl/private/smtpd.key" > > > > table aliases file:/etc/mail/aliases > > table addrnames file:/etc/mail/addrnames > > table credentials file:/etc/mail/credentials > > table domains file:/etc/mail/domains > > table virtuals file:/etc/mail/virtuals > > table usrbase file:/etc/mail/usrbase > > table rejects file:/etc/mail/rejects > > > > # Listeners > > # > > listen on lo0 > > listen on lo0 port 10028 tag DKIM > > listen on vio0 tls pki mail hostnames > > listen on vio0 port 587 tls-require pki mail auth \ > >hostnames > > > > # Actions > > # > > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > %{rcpt}'" alias > > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > > %{rcpt}'" userbase virtual > > action "dkim" relay host smtp://127.0.0.1:10027 > > action "relay" relay > > > > # Incoming > > # > > match from any mail-from for any reject > > match from local for local action "local" > > match from any for domain action "domain" > > > > # Outgoing > > # > > match tag DKIM for any action "relay" > > match from local for any action "dkim" > > match auth from any for any action "dkim" > > > > #usrbase > > m...@example.org 2000:2000:/var/vmail/example.org/matt > > > > #virtuals > > m...@example.org vmail > > > > Thanks in advance, > > Matt > > > > -- > > You received this mail because you are subscribed to misc@opensmtpd.org > > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > > > > > -- > You received this mail because you are subscribed to misc@opensmtpd.org > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > -- You received this mail because you are subscribed to misc@opensmtpd.org To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
Re: userbase question
Iirc I got the .forward file working with sqlite tables, where the user query also returned the virtual user’s maildir as an extra parameter. Good luck, Reio > On 27 Aug 2018, at 16:11, Matt Schwartz wrote: > > Hello misc@, > > Below is my configuration file. I am trying to use the userbase > parameter and when I try to send an email to myself, I get the 550 > Invalid Recipient error. I am trying to get the usrbase parameter > working so that I can add a .forward file for virtual users as per the > table(5) man page. If I don't use the userbase parameter, mail > delivery works just fine. I am not certain what I am doing wrong here. > > #smtpd.conf > pki mail cert "/etc/ssl/smtpd.crt" > pki mail key "/etc/ssl/private/smtpd.key" > > table aliases file:/etc/mail/aliases > table addrnames file:/etc/mail/addrnames > table credentials file:/etc/mail/credentials > table domains file:/etc/mail/domains > table virtuals file:/etc/mail/virtuals > table usrbase file:/etc/mail/usrbase > table rejects file:/etc/mail/rejects > > # Listeners > # > listen on lo0 > listen on lo0 port 10028 tag DKIM > listen on vio0 tls pki mail hostnames > listen on vio0 port 587 tls-require pki mail auth \ >hostnames > > # Actions > # > action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > %{rcpt}'" alias > action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec > '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a > %{rcpt}'" userbase virtual > action "dkim" relay host smtp://127.0.0.1:10027 > action "relay" relay > > # Incoming > # > match from any mail-from for any reject > match from local for local action "local" > match from any for domain action "domain" > > # Outgoing > # > match tag DKIM for any action "relay" > match from local for any action "dkim" > match auth from any for any action "dkim" > > #usrbase > m...@example.org 2000:2000:/var/vmail/example.org/matt > > #virtuals > m...@example.org vmail > > Thanks in advance, > Matt > > -- > You received this mail because you are subscribed to misc@opensmtpd.org > To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org > -- You received this mail because you are subscribed to misc@opensmtpd.org To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
userbase question
Hello misc@, Below is my configuration file. I am trying to use the userbase parameter and when I try to send an email to myself, I get the 550 Invalid Recipient error. I am trying to get the usrbase parameter working so that I can add a .forward file for virtual users as per the table(5) man page. If I don't use the userbase parameter, mail delivery works just fine. I am not certain what I am doing wrong here. #smtpd.conf pki mail cert "/etc/ssl/smtpd.crt" pki mail key "/etc/ssl/private/smtpd.key" table aliases file:/etc/mail/aliases table addrnames file:/etc/mail/addrnames table credentials file:/etc/mail/credentials table domains file:/etc/mail/domains table virtuals file:/etc/mail/virtuals table usrbase file:/etc/mail/usrbase table rejects file:/etc/mail/rejects # Listeners # listen on lo0 listen on lo0 port 10028 tag DKIM listen on vio0 tls pki mail hostnames listen on vio0 port 587 tls-require pki mail auth \ hostnames # Actions # action "local" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a %{rcpt}'" alias action "domain" mda "/usr/local/bin/rspamc -d %{dest} --mime --exec '/usr/local/libexec/dovecot/dovecot-lda -f %{sender} -d %{dest} -a %{rcpt}'" userbase virtual action "dkim" relay host smtp://127.0.0.1:10027 action "relay" relay # Incoming # match from any mail-from for any reject match from local for local action "local" match from any for domain action "domain" # Outgoing # match tag DKIM for any action "relay" match from local for any action "dkim" match auth from any for any action "dkim" #usrbase m...@example.org 2000:2000:/var/vmail/example.org/matt #virtuals m...@example.org vmail Thanks in advance, Matt -- You received this mail because you are subscribed to misc@opensmtpd.org To unsubscribe, send a mail to: misc+unsubscr...@opensmtpd.org
RE: Userbase question.
Hi, In fact Dovecot handles the delivery via LMTP. à The relation & in the extract below permit the delivery via the SQL query (depending the case for local / vdomains). Extract from /etc/mail/smtpd.conf --- (…) ### # ## Allow to deliver # accept for local alias deliver to mbox ### # ## Relay # # Tagged mail returned from DKIM accept tagged DKIM_OUT for any relay # tagged mail returned from spampd deliver to maildir accept tagged SPAM_IN for domain virtual deliver to lmtp "/var/dovecot/lmtp" rcpt-to # deliver via lmtp # tagged mail returned from clamsmtpd either send to spampd or dkimproxy_out accept tagged CLAM_IN for any relay via smtp://127.0.0.1:10035 # send to spampd accept tagged CLAM_OUT for any relay via smtp://127.0.0.1:10030 # send to dkimproxy_out # # Start here (inbound) accept from any for domain relay via smtp://127.0.0.1:10025 # to clamd via clamsmtpd_in accept from local for any relay via smtp://127.0.0.1:10027 # to clamd via clamsmtpd_out In parallel dovecot has to be configured : # Authentication for SQL users. Included from 10-auth.conf. # # passdb { driver = sql # Path for SQL configuration file, see example-config/dovecot-sql.conf.ext args = /etc/dovecot/dovecot-sql.conf.ext } userdb { driver = static args = uid=vmail gid=vmail home=/var/vmail/%d/%n } /etc/mail/dovecot-sql.conf.ext +---> doas cat /etc/dovecot/dovecot-sql.conf.ext # This file is commonly accessed via passdb {} or userdb {} section in # conf.d/auth-sql.conf.ext # Database driver: mysql, pgsql, sqlite driver = pgsql connect = host=127.0.0.1 dbname=vmail user=vmail password=myPassword default_pass_scheme = BLF-CRYPT # BSD authentication password_query = \ SELECT email, password \ FROM credentials WHERE email = '%u' AND active = 'Y' # the active fiels permit here to unactive temporary a user. You have also to configure another part of the dovecot side : <http://wiki2.dovecot.org/> http://wiki2.dovecot.org/ Postgresql instructions for OpenBSD: doas pkg_add postqgresql-server doas su - _postgresql mkdir /var/postgresql/data initdb -D /var/postgresql/data -U postgres -A md5 -E UTF8 -W exit rcctl enable postgresql à to modify /var/postgresql/data/postgresql.conf expecting your preconisations à to modify /var/postgresql/data/pg_hba.conf expecting your preconisations doas rcctl start postgresql psql -U postgres CREATE USER myUser; REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE USAGE ON SCHEMA public FROM PUBLIC; GRANT CREATE ON SCHEMA public TO postgres; GRANT USAGE ON SCHEMA public TO postgres; CREATE DATABASE myDatabase WITH OWNER myUser; ALTER ROLE myUser WITH PASSWORD 'myPassword'; \q psql -U myUser CREATE SEQUENCE seq_myDatabase_vDomains_id START 1; CREATE SEQUENCE seq_myDatabase_vUsers_id START 1; CREATE SEQUENCE seq_myDatabase_credentials_id START 1; CREATE TABLE vDomains ( id INT NOT NULL DEFAULT nextval('seq_myDatabase_vdomains_id'), domain varchar(40) NOT NULL UNIQUE, PRIMARY KEY (id) ); CREATE TABLE vUsers ( id INT NOT NULL DEFAULT nextval('seq_myDatabase_vusers_id'), email VARCHAR(60) NOT NULL UNIQUE, domain VARCHAR(40) NOT NULL, destination VARCHAR(60) NOT NULL DEFAULT 'myUser', PRIMARY KEY (id), FOREIGN KEY (domain) REFERENCES vDomains(domain) ON DELETE CASCADE ); CREATE TABLE credentials ( id INT NOT NULL DEFAULT nextval('seq_myDatabase_credentials_id'), email VARCHAR(60) NOT NULL, password VARCHAR(60), active BOOLEAN DEFAULT 'TRUE' NOT NULL, PRIMARY KEY (id), FOREIGN KEY (email) REFERENCES vusers(email) ON DELETE CASCADE ); INSERT INTO vdomains (domain) VALUES ('first.domain'); INSERT INTO credentials (email, password) VALUES ('one.user', 'myEncryptedPassword') #using smtpctl encrypt INSERT INTO vusers (email, alias) VALUES ('one.user', 'first.domain','vmail') ('postmaster@my.domain', 'my.domain', 'my.aliass'); Olivier. De : Reio Remma [mailto:r...@mrstuudio.ee] Envoyé : Sunday, February 4, 2018 9:22 PM À : misc@opensmtpd.org Objet : Re: Userbase question. The only reference I've found that tackles a similar problem: https://hugo.barrera.io/journal/2015/02/15/opensmtpd-dovecot-shared-sql-db/ Rather convoluted for a simple thing though. :) Reio On 04.02.2018 22:01, Reio Remma wrote: On 04.02.2018 21:56, Reio Remma wrote: Hello! query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; I now realize you version doesn't return the actual user's virtu
Re: Userbase question.
On 05.02.18 2:18, Reio Remma wrote: On 04.02.2018 22:21, Reio Remma wrote: On 04.02.2018 22:01, Reio Remma wrote: On 04.02.2018 21:56, Reio Remma wrote: *De :*Reio Remma [mailto:r...@mrstuudio.ee] *Envoyé :* Sunday, February 4, 2018 3:02 PM *À :* misc@opensmtpd.org *Objet :* Userbase question. Hello! I'm trying to figure out how I can have virtual domains/users working completely decoupled from system users. Every virtual/alias path seem to want to end up at a system account so I'm trying to use userbase, but userbase seems to take username without the domain part as key. query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; domain-one.com - bob - emily domain-two.com - john - albert - bob (not the same bob as b...@domain-one.com <mailto:b...@domain-one.com>) Mail sent to b...@domain-two.com <mailto:b...@domain-two.com> will end up at b...@domain-one.com <mailto:b...@domain-one.com> mailbox. Am I missing something about using virtuals in general? I'm starting to feel a little stupid here. :) Thanks, Reio I think I may have solved it (with a similar approach to Hugo Barrera's). OpenSMTPD now sees all virtual maildirs' .forward files etc. query_alias SELECT CONCAT( username, '_', domain ) FROM users WHERE email = ?; query_domainSELECT domain FROM users WHERE domain = ? LIMIT 1; query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) as maildir FROM users WHERE domain = SUBSTRING_INDEX( @u := ?, "_", -1 ) AND username = TRIM( TRAILING CONCAT('_', SUBSTRING_INDEX( @u, "_", -1 ) ) FROM @u ); I do hope query_userinfo will one day accept a second parameter (domain). Good night! Reio A little refinement to query_userinfo: query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE domain = ( @dom := SUBSTRING_INDEX( @u := ?, "_", -1 ) ) AND username = TRIM( TRAILING CONCAT('_', @dom ) FROM @u ); -- Tervitades, Reio Remma spetsialist MR Stuudio OÜ Tondi 17B, 11316, Tallinn Tel: (+372) 6 504 808 Mob: (+372) 56 22 00 33 E-Mail: r...@mrstuudio.ee www.mrstuudio.ee
Re: Userbase question.
On 04.02.2018 22:21, Reio Remma wrote: The only reference I've found that tackles a similar problem: https://hugo.barrera.io/journal/2015/02/15/opensmtpd-dovecot-shared-sql-db/ Rather convoluted for a simple thing though. :) Reio On 04.02.2018 22:01, Reio Remma wrote: On 04.02.2018 21:56, Reio Remma wrote: Hello! query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; I now realize you version doesn't return the actual user's virtual mail directory. But maybe it doesn't need to. I suspect Dovecot can handle .forward files as well, though it would be nice if they were checked without turning to Dovecot. In my setup currently OpenSMTPD can use .forward files by itself, which is nice. Does it match against an actual (whole) e-mail addess or username for you? What does your "accept for domain ..." line in smtpd.conf look like? All the best, Reio On 04.02.2018 21:11, Olivier wrote: Hello I am using my own server with a postgresql database to store domains, users & password: vmail=> \d List of relations Schema | Name | Type | Owner +--+--+--- public | credentials | table | vmail public | seq_vmail_credentials_id | sequence | vmail public | seq_vmail_vdomains_id | sequence | vmail public | seq_vmail_vusers_id | sequence | vmail public | vdomains | table | vmail public | vusers | table | vmail This database is used by dovecot & opensmtpd for authentication (for encoding: use smtpctl encrypt). All email are stored in the Maildir format, and store in the disk: /var/vmail/some.domain.tld/SomeUsers/Maildir For this, following packages have to be installed _opensmtpd-extras-201703132115p1 extras _opensmtpd-extras-pgsql-201703132115p1 Extract from smtpd.conf: (…) ### # ## Define Table # table aliases db:/etc/mail/aliases.db table vdomains postgres:/etc/mail/pgsql.conf table passwd postgres:/etc/mail/pgsql.conf table valiases postgres:/etc/mail/pgsql.conf (…) Below, the database interface (/etc/mail/pgsql.conf) # smtpd.conf: table users pgsql:/etc/mail/pgsql.conf conninfo host='myHost' user=’myUser' password='myPassword' dbname='myDBName' # Alias lookup query # query_alias select destination from myRelation where email=$1; # # Domain lookup query # query_domain select domain from myRelation where domain=$1; # # User lookup query # #query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; # # Credentials lookup query # query_credentials select email, password from credentials where email=$1 and active = 'Y'; # <-- here your SQL request Sorry for my bad english. I hope that it will be helping. Olivier. *De :*Reio Remma [mailto:r...@mrstuudio.ee] *Envoyé :* Sunday, February 4, 2018 3:02 PM *À :* misc@opensmtpd.org *Objet :* Userbase question. Hello! I'm trying to figure out how I can have virtual domains/users working completely decoupled from system users. Every virtual/alias path seem to want to end up at a system account so I'm trying to use userbase, but userbase seems to take username without the domain part as key. query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; domain-one.com - bob - emily domain-two.com - john - albert - bob (not the same bob as b...@domain-one.com <mailto:b...@domain-one.com>) Mail sent to b...@domain-two.com <mailto:b...@domain-two.com> will end up at b...@domain-one.com <mailto:b...@domain-one.com> mailbox. Am I missing something about using virtuals in general? I'm starting to feel a little stupid here. :) Thanks, Reio I think I may have solved it (with a similar approach to Hugo Barrera's). OpenSMTPD now sees all virtual maildirs' .forward files etc. query_alias SELECT CONCAT( username, '_', domain ) FROM users WHERE email = ?; query_domain SELECT domain FROM users WHERE domain = ? LIMIT 1; query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) as maildir FROM users WHERE domain = SUBSTRING_INDEX( @u := ?, "_", -1 ) AND username = TRIM( TRAILING CONCAT('_', SUBSTRING_INDEX( @u, "_", -1 ) ) FROM @u ); I do hope query_userinfo will one day accept a second parameter (domain). Good night! Reio
Re: Userbase question.
The only reference I've found that tackles a similar problem: https://hugo.barrera.io/journal/2015/02/15/opensmtpd-dovecot-shared-sql-db/ Rather convoluted for a simple thing though. :) Reio On 04.02.2018 22:01, Reio Remma wrote: On 04.02.2018 21:56, Reio Remma wrote: Hello! query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; I now realize you version doesn't return the actual user's virtual mail directory. But maybe it doesn't need to. I suspect Dovecot can handle .forward files as well, though it would be nice if they were checked without turning to Dovecot. In my setup currently OpenSMTPD can use .forward files by itself, which is nice. Does it match against an actual (whole) e-mail addess or username for you? What does your "accept for domain ..." line in smtpd.conf look like? All the best, Reio On 04.02.2018 21:11, Olivier wrote: Hello I am using my own server with a postgresql database to store domains, users & password: vmail=> \d List of relations Schema | Name | Type | Owner +--+--+--- public | credentials | table | vmail public | seq_vmail_credentials_id | sequence | vmail public | seq_vmail_vdomains_id | sequence | vmail public | seq_vmail_vusers_id | sequence | vmail public | vdomains | table | vmail public | vusers | table | vmail This database is used by dovecot & opensmtpd for authentication (for encoding: use smtpctl encrypt). All email are stored in the Maildir format, and store in the disk: /var/vmail/some.domain.tld/SomeUsers/Maildir For this, following packages have to be installed _opensmtpd-extras-201703132115p1 extras _opensmtpd-extras-pgsql-201703132115p1 Extract from smtpd.conf: (…) ### # ## Define Table # table aliases db:/etc/mail/aliases.db table vdomains postgres:/etc/mail/pgsql.conf table passwd postgres:/etc/mail/pgsql.conf table valiases postgres:/etc/mail/pgsql.conf (…) Below, the database interface (/etc/mail/pgsql.conf) # smtpd.conf: table users pgsql:/etc/mail/pgsql.conf conninfo host='myHost' user=’myUser' password='myPassword' dbname='myDBName' # Alias lookup query # query_alias select destination from myRelation where email=$1; # # Domain lookup query # query_domain select domain from myRelation where domain=$1; # # User lookup query # #query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; # # Credentials lookup query # query_credentials select email, password from credentials where email=$1 and active = 'Y'; # <-- here your SQL request Sorry for my bad english. I hope that it will be helping. Olivier. *De :*Reio Remma [mailto:r...@mrstuudio.ee] *Envoyé :* Sunday, February 4, 2018 3:02 PM *À :* misc@opensmtpd.org *Objet :* Userbase question. Hello! I'm trying to figure out how I can have virtual domains/users working completely decoupled from system users. Every virtual/alias path seem to want to end up at a system account so I'm trying to use userbase, but userbase seems to take username without the domain part as key. query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; domain-one.com - bob - emily domain-two.com - john - albert - bob (not the same bob as b...@domain-one.com <mailto:b...@domain-one.com>) Mail sent to b...@domain-two.com <mailto:b...@domain-two.com> will end up at b...@domain-one.com <mailto:b...@domain-one.com> mailbox. Am I missing something about using virtuals in general? I'm starting to feel a little stupid here. :) Thanks, Reio
Re: Userbase question.
On 04.02.2018 21:56, Reio Remma wrote: Hello! query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; I now realize you version doesn't return the actual user's virtual mail directory. But maybe it doesn't need to. I suspect Dovecot can handle .forward files as well, though it would be nice if they were checked without turning to Dovecot. In my setup currently OpenSMTPD can use .forward files by itself, which is nice. Does it match against an actual (whole) e-mail addess or username for you? What does your "accept for domain ..." line in smtpd.conf look like? All the best, Reio On 04.02.2018 21:11, Olivier wrote: Hello I am using my own server with a postgresql database to store domains, users & password: vmail=> \d List of relations Schema | Name | Type | Owner +--+--+--- public | credentials | table | vmail public | seq_vmail_credentials_id | sequence | vmail public | seq_vmail_vdomains_id | sequence | vmail public | seq_vmail_vusers_id | sequence | vmail public | vdomains | table | vmail public | vusers | table | vmail This database is used by dovecot & opensmtpd for authentication (for encoding: use smtpctl encrypt). All email are stored in the Maildir format, and store in the disk: /var/vmail/some.domain.tld/SomeUsers/Maildir For this, following packages have to be installed _opensmtpd-extras-201703132115p1 extras _opensmtpd-extras-pgsql-201703132115p1 Extract from smtpd.conf: (…) ### # ## Define Table # table aliases db:/etc/mail/aliases.db table vdomains postgres:/etc/mail/pgsql.conf table passwd postgres:/etc/mail/pgsql.conf table valiases postgres:/etc/mail/pgsql.conf (…) Below, the database interface (/etc/mail/pgsql.conf) # smtpd.conf: table users pgsql:/etc/mail/pgsql.conf conninfo host='myHost' user=’myUser' password='myPassword' dbname='myDBName' # Alias lookup query # query_alias select destination from myRelation where email=$1; # # Domain lookup query # query_domain select domain from myRelation where domain=$1; # # User lookup query # #query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; # # Credentials lookup query # query_credentials select email, password from credentials where email=$1 and active = 'Y'; # <-- here your SQL request Sorry for my bad english. I hope that it will be helping. Olivier. *De :*Reio Remma [mailto:r...@mrstuudio.ee] *Envoyé :* Sunday, February 4, 2018 3:02 PM *À :* misc@opensmtpd.org *Objet :* Userbase question. Hello! I'm trying to figure out how I can have virtual domains/users working completely decoupled from system users. Every virtual/alias path seem to want to end up at a system account so I'm trying to use userbase, but userbase seems to take username without the domain part as key. query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; domain-one.com - bob - emily domain-two.com - john - albert - bob (not the same bob as b...@domain-one.com <mailto:b...@domain-one.com>) Mail sent to b...@domain-two.com <mailto:b...@domain-two.com> will end up at b...@domain-one.com <mailto:b...@domain-one.com> mailbox. Am I missing something about using virtuals in general? I'm starting to feel a little stupid here. :) Thanks, Reio
Re: Userbase question.
Hello! query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; Does it match against an actual (whole) e-mail addess or username for you? What does your "accept for domain ..." line in smtpd.conf look like? All the best, Reio On 04.02.2018 21:11, Olivier wrote: Hello I am using my own server with a postgresql database to store domains, users & password: vmail=> \d List of relations Schema | Name | Type | Owner +--+--+--- public | credentials | table | vmail public | seq_vmail_credentials_id | sequence | vmail public | seq_vmail_vdomains_id | sequence | vmail public | seq_vmail_vusers_id | sequence | vmail public | vdomains | table | vmail public | vusers | table | vmail This database is used by dovecot & opensmtpd for authentication (for encoding: use smtpctl encrypt). All email are stored in the Maildir format, and store in the disk: /var/vmail/some.domain.tld/SomeUsers/Maildir For this, following packages have to be installed _opensmtpd-extras-201703132115p1 extras _opensmtpd-extras-pgsql-201703132115p1 Extract from smtpd.conf: (…) ### # ## Define Table # table aliases db:/etc/mail/aliases.db table vdomains postgres:/etc/mail/pgsql.conf table passwd postgres:/etc/mail/pgsql.conf table valiases postgres:/etc/mail/pgsql.conf (…) Below, the database interface (/etc/mail/pgsql.conf) # smtpd.conf: table users pgsql:/etc/mail/pgsql.conf conninfo host='myHost' user=’myUser' password='myPassword' dbname='myDBName' # Alias lookup query # query_alias select destination from myRelation where email=$1; # # Domain lookup query # query_domain select domain from myRelation where domain=$1; # # User lookup query # #query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; # # Credentials lookup query # query_credentials select email, password from credentials where email=$1 and active = 'Y'; # <-- here your SQL request Sorry for my bad english. I hope that it will be helping. Olivier. *De :*Reio Remma [mailto:r...@mrstuudio.ee] *Envoyé :* Sunday, February 4, 2018 3:02 PM *À :* misc@opensmtpd.org *Objet :* Userbase question. Hello! I'm trying to figure out how I can have virtual domains/users working completely decoupled from system users. Every virtual/alias path seem to want to end up at a system account so I'm trying to use userbase, but userbase seems to take username without the domain part as key. query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; domain-one.com - bob - emily domain-two.com - john - albert - bob (not the same bob as b...@domain-one.com <mailto:b...@domain-one.com>) Mail sent to b...@domain-two.com <mailto:b...@domain-two.com> will end up at b...@domain-one.com <mailto:b...@domain-one.com> mailbox. Am I missing something about using virtuals in general? I'm starting to feel a little stupid here. :) Thanks, Reio
RE: Userbase question.
Hello I am using my own server with a postgresql database to store domains, users & password: vmail=> \d List of relations Schema | Name | Type | Owner +--+--+--- public | credentials | table| vmail public | seq_vmail_credentials_id | sequence | vmail public | seq_vmail_vdomains_id| sequence | vmail public | seq_vmail_vusers_id | sequence | vmail public | vdomains | table| vmail public | vusers | table| vmail This database is used by dovecot & opensmtpd for authentication (for encoding: use smtpctl encrypt). All email are stored in the Maildir format, and store in the disk: /var/vmail/some.domain.tld/SomeUsers/Maildir For this, following packages have to be installed _opensmtpd-extras-201703132115p1 extras _opensmtpd-extras-pgsql-201703132115p1 Extract from smtpd.conf: (…) ### # ## Define Table # table aliases db:/etc/mail/aliases.db table vdomains postgres:/etc/mail/pgsql.conf table passwdpostgres:/etc/mail/pgsql.conf table valiases postgres:/etc/mail/pgsql.conf (…) Below, the database interface (/etc/mail/pgsql.conf) # smtpd.conf: table users pgsql:/etc/mail/pgsql.conf conninfo host='myHost' user=’myUser' password='myPassword' dbname='myDBName' # Alias lookup query # query_alias select destination from myRelation where email=$1; # # Domain lookup query # query_domain select domain from myRelation where domain=$1; # # User lookup query # #query_userinfo select 1001,1001,'/var/vmail/' from vusers where email=$1; # # Credentials lookup query # query_credentials select email, password from credentials where email=$1 and active = 'Y'; # <-- here your SQL request Sorry for my bad english. I hope that it will be helping. Olivier. De : Reio Remma [mailto:r...@mrstuudio.ee] Envoyé : Sunday, February 4, 2018 3:02 PM À : misc@opensmtpd.org Objet : Userbase question. Hello! I'm trying to figure out how I can have virtual domains/users working completely decoupled from system users. Every virtual/alias path seem to want to end up at a system account so I'm trying to use userbase, but userbase seems to take username without the domain part as key. query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; domain-one.com - bob - emily domain-two.com - john - albert - bob (not the same bob as b...@domain-one.com) Mail sent to b...@domain-two.com will end up at b...@domain-one.com mailbox. Am I missing something about using virtuals in general? I'm starting to feel a little stupid here. :) Thanks, Reio
Re: Userbase question.
Hey! uid/gid are for vmail (/home/dovecot directory). I've looked at the smtpd lookup trace and query_userinfo queries the database purely by user name (without domain part). That is essentially where all virtuality fails. :/ If the database was queried by the full e-mail address (not unlike the virtual alias query), I could extract the domain part easily and proceed from there. In Dovecot I've specified the username + domain separately in MySQL lookups where clauses. Thanks! Reio On 04.02.2018 19:18, Edgar Pettijohn wrote: does the system have a uid and gid 5000? I'm using mysql myself, but i don't have a userinfo section. I'm guessing it should still work the same as the userinfo table described in table(5) though. Unfortunantly I am no sql expert, so I would just recommend verifying that your query does what you expect it to do perhaps run it from the command line and see what you get. On 02/04/18 10:32, Reio Remma wrote: Current smtpd.conf below. As I understand userbase is the only way to let OpenSMTPD know where to look for table aliases mysql:/etc/opensmtpd/mysql.conf table domains mysql:/etc/opensmtpd/mysql.conf table userinfo mysql:/etc/opensmtpd/mysql.conf table credentials mysql:/etc/opensmtpd/mysql.conf listen on 0.0.0.0 port 25 tls pki bwo.mrstuudio.ee listen on 0.0.0.0 port 587 tls-require pki bwo.mrstuudio.ee auth listen on lo port 10025 tag Filtered listen on lo port 10027 tag Signed accept tagged Filtered for domain virtual userbase deliver to lmtp "/var/run/dovecot/lmtp" rcpt-to accept from any for domain relay via lmtp://127.0.0.1:10024 accept tagged Signed for any relay via tls://orc.mrstuudio.ee accept from local for any relay via lmtp://127.0.0.1:10026 --- mysql.conf query_alias SELECT username FROM users WHERE email = ?; query_domain SELECT domain FROM users WHERE domain = ? LIMIT 1; query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; query_credentials SELECT username, password FROM users WHERE email = ?; Thanks, Reio On 04.02.2018 18:09, Edgar Pettijohn wrote: what does your smtpd.conf look like? On 02/04/18 08:01, Reio Remma wrote: Hello! I'm trying to figure out how I can have virtual domains/users working completely decoupled from system users. Every virtual/alias path seem to want to end up at a system account so I'm trying to use userbase, but userbase seems to take username without the domain part as key. query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; domain-one.com - bob - emily domain-two.com - john - albert - bob (not the same bob as b...@domain-one.com) Mail sent to b...@domain-two.com will end up at b...@domain-one.com mailbox. Am I missing something about using virtuals in general? I'm starting to feel a little stupid here. :) Thanks, Reio
Re: Userbase question.
what does your smtpd.conf look like? On 02/04/18 08:01, Reio Remma wrote: Hello! I'm trying to figure out how I can have virtual domains/users working completely decoupled from system users. Every virtual/alias path seem to want to end up at a system account so I'm trying to use userbase, but userbase seems to take username without the domain part as key. query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; domain-one.com - bob - emily domain-two.com - john - albert - bob (not the same bob as b...@domain-one.com) Mail sent to b...@domain-two.com will end up at b...@domain-one.com mailbox. Am I missing something about using virtuals in general? I'm starting to feel a little stupid here. :) Thanks, Reio
Userbase question.
Hello! I'm trying to figure out how I can have virtual domains/users working completely decoupled from system users. Every virtual/alias path seem to want to end up at a system account so I'm trying to use userbase, but userbase seems to take username without the domain part as key. query_userinfo SELECT 5000, 5000, CONCAT('/home/dovecot/domains/', domain, '/', username ) AS homedir FROM users WHERE username = ?; domain-one.com - bob - emily domain-two.com - john - albert - bob (not the same bob as b...@domain-one.com) Mail sent to b...@domain-two.com will end up at b...@domain-one.com mailbox. Am I missing something about using virtuals in general? I'm starting to feel a little stupid here. :) Thanks, Reio