Have an error in my SQL syntax
Hi, I was following this guides to set up mail.
As I use this comman
GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'mypassword';
it shows
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '‘mypassword’' at line 1
My MySQL version is 15.1 Distrib 10.3.27-MariaDB
(the guide is using ver. 14.14)
Now I don't know how and where to find the correct syntax.
Can anyone help me out? THANKS A LOT!
Update: Everything's fine now! I delete all the 'and it worked.
8 Replies
I'm a little rusty on this but https://www.mysqltutorial.org/mysql-grant.aspx says it should be like this:
CREATE USER mailuser@127.0.0.1 IDENTIFIED BY 'mypassword';
GRANT SELECT ON mailserver.* TO mailuser@127.0.0.1;
…plus or minus a few quote marks.
If your mail server is not going to have a lot of users or volume, try using SQLite for this. Your pain will be a lot less! If you're not an SQL wizard…or you don't play one on tee-vee, SQLite is a great learning vehicle. https://sqlite.org
Also, don't forget that you have to set up SPF, DKIM & DMARC so that your server plays nice with the big boyz (Comcast, Gmail, et.al.).
-- sw
Hi, thank you for replying:)
I want to ask another question here.
As I was following this guide
I can not get my OpenDKIM worked.
I also try follow this guide because I saw one previous request from others said it worked.
But it didn't… I stock at the testing step by using this command
sudo opendkim-testkey -d mydomain.com -s default -vvv
it shows
opendkim-testkey: using default configfile /etc/opendkim.conf
opendkim-testkey: checking key 'default._domainkey.mydomain.com'
opendkim-testkey: 'default._domainkey.mydomain.com' query failed
Here's the status of OpenDKIM
opendkim.service - OpenDKIM DomainKeys Identified Mail (DKIM) Milter
Loaded: loaded (/lib/systemd/system/opendkim.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Thu 2021-02-04 04:36:35 CST; 38s ago
Docs: man:opendkim(8)
man:opendkim.conf(5)
man:opendkim-genkey(8)
man:opendkim-genzone(8)
man:opendkim-testadsp(8)
man:opendkim-testkey
http://www.opendkim.org/docs.html
Process: 4335 ExecStart=/usr/sbin/opendkim -x /etc/opendkim.conf (code=exited, status=78)
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Control process exited, code=exited, status=78/CONFIG
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Failed with result 'exit-code'.
Feb 04 04:36:35 myhostname systemd[1]: Failed to start OpenDKIM DomainKeys Identified Mail (DKIM) Milter.
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Service RestartSec=100ms expired, scheduling restart.
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Scheduled restart job, restart counter is at 5.
Feb 04 04:36:35 myhostname systemd[1]: Stopped OpenDKIM DomainKeys Identified Mail (DKIM) Milter.
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Start request repeated too quickly.
Feb 04 04:36:35 myhostname systemd[1]: opendkim.service: Failed with result 'exit-code'.
Feb 04 04:36:35 myhostname systemd[1]: Failed to start OpenDKIM DomainKeys Identified Mail (DKIM) Milter.
Anyone on Debian10 have a solution for this?
or where should I go ask for help?
THANKS A LOT!
You write:
opendkim-testkey: using default configfile /etc/opendkim.conf
opendkim-testkey: checking key 'default._domainkey.allmyloveyogurt.com'
opendkim-testkey: 'default._domainkey.allmyloveyogurt.com' query failed
default._domainkey.allmyloveyogurt.com is a reference to the DNS TXT record you have to create holding the public signing key. Here's mine (domain name redacted):
stevewi:~ $ dig -t txt mailkey._domainkey.mydomain.com @ns1.linode.com
; <<>> DiG 9.10.6 <<>> -t txt mailkey._domainkey.mydomain.com @ns1.linode.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 8874
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;mailkey._domainkey.mydomain.com. IN TXT
;; ANSWER SECTION:
mailkey._domainkey.mydomain.com. 86400 IN TXT "v=DKIM1; h=sha256; k=rsa; c=relaxed/simple; p=MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAubf3LHUtzWmCXP7CsKsmzi0Q5nTd4UQr81Rre4aenNnJ9vzeejpMh6/Y1UOLvojYbEbSANEoDrEsidkUDkCaOWl1MI9Sit3hfaN1BYMylDGqx1R9Q7GniYqxsCGzlM1nGNTnaU6Z7UlvNTEZ9ojDjc32yEi69NWuUH5fc" "Gms5yQ4Lx2R2zy8mKqmGwkCZh4vLwnqeS9+Nw4bt3Nc29dx9CcXMYlrvnjinQROsgo2VDmL0JjrFzctgPvAkyXXf7oTM2SaUwQQGG+DgyI4OvV6M4FjGaDpLDnGZmIKADC7h/waAQIIWo77cCUwlWbRvgpgYtEB0Qr36EtxmT51aZ2i2wIDAQAB"
;; Query time: 95 msec
;; SERVER: <DNS Server IPv6 address redacted>)
;; WHEN: Wed Feb 03 13:44:34 PST 2021
;; MSG SIZE rcvd: 545
As you can see, mine is called mailkey._domainkey.mydomain.com. I believe you need another TXT record that looks something like this:
stevewi:~ $ dig -t txt _adsp._domainkey.mydomain.com @ns1.linode.com
; <<>> DiG 9.10.6 <<>> -t txt _adsp._domainkey.mydomain.com @ns1.linode.com
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 42485
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 512
;; QUESTION SECTION:
;_adsp._domainkey.mydomain.com. IN TXT
;; ANSWER SECTION:
_adsp._domainkey.mydomain.com. 86400 IN TXT "dkim=all"
;; Query time: 75 msec
;; SERVER: <DNS Server IPv6 address redacted>)
;; WHEN: Wed Feb 03 13:49:03 PST 2021
;; MSG SIZE rcvd: 110
This TXT record tells OpenDKIM which emails to sign. I sign everything ("dkim=all").
-- sw
P.S. My public signing key is SHA-256. This is technically unsupported but seems to work. You may want to stick with SHA-128. Once you get this working, I can send you a shell script that you can run monthly or so to change the public signing key regularly (for better security).
Hi,
so I added a TXT record at Linode DNS manager.
Is there anything I should do on Linux?
And these are what I got.
Max:~ $ dig _adsp._domainkey.mydomain.com TXT
; <<>> DiG 9.10.6 <<>> _adsp._domainkey.mydomain.com TXT
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 59343
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1232
;; QUESTION SECTION:
;_adsp._domainkey.mydomain.com. IN TXT
;; ANSWER SECTION:
_adsp._domainkey.mydomain.com. 86400 IN TXT "dkim=all"
;; Query time: 231 msec
;; SERVER: 192.168.1.1#53(192.168.1.1)
;; WHEN: Thu Feb 04 15:41:05 CST 2021
;; MSG SIZE rcvd: 86
Also this
Max:~ $ dig default._domainkey.mydomain.com TXT
; <<>> DiG 9.10.6 <<>> default._domainkey.mydomain.com TXT
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 57573
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1232
;; QUESTION SECTION:
;default._domainkey.mydomain.com. IN TXT
;; ANSWER SECTION:
default._domainkey.mydomain.com. 300 IN TXT "v=DKIM1; h=sha256; k=rsa; \010\009 p=MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAlH18DK1rMPVn+RheQoGhf0TYFHAMhwortrFWwFeha9dJTSoC9HHNOG6OM7QvxuKpBjFGB3TyZ3LWtJ3ffFYYKb+gqHlHLmnmZ9CKN4JGOxIdhvteQtNjUG108kiPdmB4wKhadiKx7GPvaOP26jWKplYlyYKV0JAYLVog5v/Crc8r+0l6jFx" "d1GvoKjainz1E8jKXjztMAYXRbQ\010\009 4fNUhTDq2YOjhEJtOvk+igzHDRyEhkxaMrxFClQzmMALcyomI5aFGfv2ZSjWRwbvJzZasliPjhoOOEmboVLpVJojSQjkm0j+CifJ2eiKI9UXJaGj00jS+UMrT4IbEyicXpCluI5wIDAQAB"
;; Query time: 246 msec
;; SERVER: 192.168.1.1#53(192.168.1.1)
;; WHEN: Thu Feb 04 15:39:48 CST 2021
;; MSG SIZE rcvd: 509
`
I wonder why mine don't have the @ns1.linode.com at the end of line 1 like yours.
And c=relaxed/simple in the TXT value.
Thanks again.
You write:
I wonder why mine don't have the @ns1.linode.com at the end of line 1 like yours.
This was part of the dig command line…telling dig which DNS server to query. I did those from my Mac and dig queried the DNS server(s) for my ISP (a large fibre operator in the Pacific Northwest). I had to qualify my request.
And c=relaxed/simple in the TXT value.
You have to SPECIFICALLY put c=relaxed/simple in the TXT value. Since it's an optional value, you have to specify it explicitly if you don't want the default. See:
https://help.returnpath.com/hc/en-us/articles/222438487-DKIM-signature-header-detail
This page describes what all that gobbledegook means and does for you. Skip down to the Optional tags section for an explanation of canonicalization. It has to do with which parts of the message are allowed to be modified in transit and how much modification you're willing to accept and still find the message to be valid. If you don't specify c=, I believe it defaults to c=relaxed/relaxed.
Sorry for the late response… I've been working on my taxes :-(
-- sw
Hi @stevewi :)
it's okay! I'm also trying to figure out why it won't work.
Anyway, it's quit embarrassed that I found out it's because of the copy-paste error…
Now the key-testing shows OK!
But somehow I can't send or receive email now…
And the OpenDKIM still won't work…
(ahhh so stressful but I really want to learn and make this to be done.)
Should I post a new question or would you give me some advice?
Thank you so much!
Here's what it shows.
opendkim.service - OpenDKIM DomainKeys Identified Mail (DKIM) Milter
Loaded: loaded (/lib/systemd/system/opendkim.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Sun 2021-02-07 03:38:35 CST; 4s ago
Docs: man:opendkim(8)
man:opendkim.conf(5)
man:opendkim-genkey(8)
man:opendkim-genzone(8)
man:opendkim-testadsp(8)
man:opendkim-testkey
http://www.opendkim.org/docs.html
Process: 23469 ExecStart=/usr/sbin/opendkim -x /etc/opendkim.conf (code=exited, status=0/SUCCESS)
Main PID: 23470 (code=exited, status=69)
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Main process exited, code=exited, status=69/UNAVAILABLE
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Failed with result 'exit-code'.
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Service RestartSec=100ms expired, scheduling restart.
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Scheduled restart job, restart counter is at 5.
Feb 07 03:38:35 myhostname systemd[1]: Stopped OpenDKIM DomainKeys Identified Mail (DKIM) Milter.
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Start request repeated too quickly.
Feb 07 03:38:35 myhostname systemd[1]: opendkim.service: Failed with result 'exit-code'.
Feb 07 03:38:35 myhostname systemd[1]: Failed to start OpenDKIM DomainKeys Identified Mail (DKIM) Milter.
You write:
But somehow I can't send or receive email now…
This is because postfix(1) is trying to communicate with something that's not there (opendkim).
Here's what it shows.
This looks like a permissions/ownership problem to me. Make sure the runtime directories and configuration files have the correct ownership/permissions:
drwxr-x--- 2 opendkim opendkim 60 Feb 6 08:48 /var/run/opendkim
and
stevewi@dave:~$ ll /etc/opendkim
total 32
-rw-r--r-- 1 opendkim opendkim 86 Jan 10 2020 KeyTable
-rw------- 1 opendkim opendkim 20 Apr 2 2020 mailkey.private
-rw-r--r-- 1 opendkim opendkim 199 Jan 10 2020 SigningTable
-rw-r--r-- 1 opendkim opendkim 122 Jan 8 2020 TrustedHosts
n.b., mailkey.private doesn't have to have this name as long as the name you use matches what you entered in KeyTable.
Can you copy/paste the filter configuration lines from /etc/postfix/main.cf (this is mine…yours may look different):
# milter configuraton
#
milter_default_action = accept
milter_protocol = 6
smtpd_milters =
unix:var/run/opendkim/opendkim.sock,
unix:var/run/opendmarc/opendmarc.sock
non_smtpd_milters = $smtpd_milters
Should I post a new question or would you give me some advice?
Lets move this to a new thread…and gain some visibility with others. Your OP was about SQL syntax…
-- sw
Okay!Thanks:)