Postgres通过sendmail发送HTML邮件出现莫名感叹号的问题排查与解决

问题场景 #

通过PostgreSQL的COPY命令结合sendmail发送HTML邮件，邮件可正常发送，但收到的邮件HTML源码中部分行末尾出现多余感叹号，导致格式错乱。这些感叹号仅出现在生成的邮件中，原始HTML里并不存在，且集中在CSS代码区域。已尝试移除换行符、过滤不可打印字符，问题仍存在，且受环境限制只能使用sendmail。

原始PostgreSQL COPY命令 #

copy
  (Select 'Subject: This is the subject'
   UNION ALL
   SELECT 'Content-Type: text/html'
   UNION ALL
   SELECT  email_body from sometable)
to program
  'sendmail -f aaa@aaa.com aaa@aaa.com'
with (
  format text);

数据表中的原始HTML #

<html xmlns:mso="urn:schemas-microsoft-com:office:office"><HEAD><style type="text/css"> .body, p, ul, li, ol, h1, h2, a, b, strong  {color: #FFFFFF; font-family: 'Citi-Sans-Display-Regular', Arial, Helvetica, sans-serif; } .body, p, ul, li, ol { color: #FFFFFF; font-family: Arial, Verdana, Arial, Sans-serif; font-size: 10pt; line-height: 1.2; } body,td,th { color: #FFFFFF; } a { color: #00BDF2; text-decoration: none;} a:link { color: #00BDF2; text-decoration: none;} a:visited { color: #00BDF2; text-decoration: none;} a:hover { color: #00BDF2; text-decoration: underline;} a:active { color: #00BDF2; text-decoration: none;} .blue{ color: #00bdf2} .div-header { background-color: #0F1632; } .div-sub-header { background-color: #255BE3; } table.blueTable { font-family: 'Citi-Sans-Display-Regular', Arial, Helvetica, sans-serif; border: 0px solid #73C2FC; background-color: #EEEEEE; width: 100%; text-align: left; border-collapse: collapse; } table.blueTable td, table.blueTable th { border: 1px solid #AAAAAA; padding: 3px 2px; } table.blueTable tbody td { font-size: 13px; color: #53565a } table.blueTable tr:nth-child(even) { background: #FFFFFF; }  table.blueTable thead { background: #73C2FC; border-bottom: 2px solid #444444; text-align: center; } table.blueTable thead th { font-size: 15px; font-weight: bold; color: #FFFFFF; border-left: 2px solid #D0E4F5; } table.blueTable thead th:first-child { border-left: none; }  table.blueTable tfoot { font-size: 14px; font-weight: bold; color: #FFFFFF; background: #D0E4F5; background: -moz-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%); background: -webkit-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%); background: linear-gradient(to bottom, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%); border-top: 2px solid #444444; } table.blueTable tfoot td { font-size: 14px; } table.blueTable tfoot .links { text-align: right; } table.blueTable tfoot .links a{ display: inline-block; background: #73C2FC; color: #FFFFFF; padding: 2px 8px; border-radius: 5px; }  </style> </HEAD> <body>test</body> </html>

生成的存在问题的HTML源码 #

<html xmlns:mso="urn:schemas-microsoft-com:office:office"><head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"><style type="text/css"> .body, p, ul, li, ol, h1, h2, a, b, strong  {color: #FFFFFF; font-family: 'Citi-Sans-Display-Regular', Arial, Helvetica, sans-serif; } .body, p, ul, li, ol { color: #FFFFFF; font-family: Arial, Verdana, Arial, Sans-serif; font-size: 10pt; line-height: 1.2; } body,td,th { color: #FFFFFF; } a { color: #00BDF2; text-decoration: none;} a:link { color: #00BDF2; text-decoration: none;} a:visited { color: #00BDF2; text-decoration: none;} a:hover { color: #00BDF2; text-decoration: underline;} a:active { color: #00BDF2; text-decoration: none;} .blue{ color: #00bdf2} .div-header { background-color: #0F1632; } .div-sub-header { background-color: #255BE3; } table.blueTable { font-family: 'Citi-Sans-Display-Regular', Arial, Helvetica, sans-serif; border: 0px solid #73C2FC; background-color: #EEEEEE; width: 100%; text-align: left; border-collapse: collapse; } table.blueTable td, table.blueTable th { bo!
 rder: 1px solid #AAAAAA; padding: 3px 2px; } table.blueTable tbody td { font-size: 13px; color: #53565a } table.blueTable tr:nth-child(even) { background: #FFFFFF; }  table.blueTable thead { background: #73C2FC; border-bottom: 2px solid #444444; text-align: center; } table.blueTable thead th { font-size: 15px; font-weight: bold; color: #FFFFFF; border-left: 2px solid #D0E4F5; } table.blueTable thead th:first-child { border-left: none; }  table.blueTable tfoot { font-size: 14px; font-weight: bold; color: #FFFFFF; background: #D0E4F5; background: -moz-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%); background: -webkit-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%); background: linear-gradient(to bottom, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%); border-top: 2px solid #444444; } table.blueTable tfoot td { font-size: 14px; } table.blueTable tfoot .links { text-align: right; } table.blueTable tfoot .links a{ display: inline-block; background: #73C2FC; colo !
 r: #FFFFFF; padding: 2px 8px; border-radius: 5px; }  </style> !
 </head> <body>test</body> </html>

解决方案 #

感叹号是由于行长度超限导致的。通过fold -s命令对HTML内容进行合理换行，避免单行过长，修改后的COPY命令如下：

copy (Select 'Subject: This is the subject' 
UNION ALL SELECT 'Content-Type: text/html' 
UNION ALL SELECT replace(email_body,chr(10),'') from sometable) 
to program 'fold -s | sendmail -f aaa@aaa.com aaa@aaa.com' with ( 
format text);

内容的提问来源于stack exchange，提问作者DandC