How to access to postfix mail server from PostgreSql function (pgmail)?

November 9, 2016 108 views
Email PostgreSQL Firewall CentOS

i need to send mail via postfix mail server by using pgmail but i can not access to postfix mailserver from pgmail function because my aplication working on windows machine and i trying to access remote centOS machine (, for this i try change configuration postfix mail server (configuration file:, i tried acces to centOS server and postgresql server both are set up on the same machine, see more for this my using pl/tlcu function:

RETURNS integer AS

set mailfrom $1
set mailto $2
set mailsubject $3
set mailmessage $4
set myHost ""
set myPort 587 or 25
set mySock [socket $myHost $myPort]
set toemailaddressstart [string first "<" $mailto]
if {$toemailaddress
start != -1} {
set toemailaddressfinish [string first ">" $mailto]
set toemailaddress
start [expr $toemailaddressstart + 1]
set toemailaddress
finish [expr $toemailaddressfinish - 1]
set toemailaddress [string range $mailto $toemailaddress
start $toemailaddressfinish]
} else {
set toemailaddress $mailto
set fromemailaddress
start [string first "<" $mailfrom]
if {$fromemailaddressstart != -1} {
set fromemailaddress
finish [string first ">" $mailfrom]
set fromemailaddressstart [expr $fromemailaddressstart + 1]
set fromemailaddressfinish [expr $fromemailaddressfinish - 1]
set fromemailaddress [string range $mailfrom $fromemailaddressstart $fromemailaddressfinish]
} else {
set fromemailaddress $mailfrom
fileevent $mySock writable [list svcHandler $mySock]
fconfigure $mySock -buffering line
puts $mySock "HELO"
gets $mySock name
puts $mySock "MAIL FROM: $fromemailaddress"
gets $mySock name
puts $mySock "RCPT TO: $toemailaddress"
gets $mySock name
puts $mySock "DATA"
gets $mySock name
puts $mySock "From: $mailfrom"
puts $mySock "To: $mailto"
puts $mySock "Subject: $mailsubject"
puts $mySock "MIME-Version: 1.0"
puts $mySock "Content-type: text/plain; charset=UTF-8"
puts $mySock "Content-Transfer-Encoding: 8bit"
puts $mySock ""
puts $mySock "$mailmessage"
puts $mySock ""
puts $mySock "."
gets $mySock name
puts $mySock "QUIT"
gets $mySock name
close $mySock
return 1$BODY$

I applied these settings for my mail server on centOS:
but didn't work again pgmail code, otherwise what should i do?

here is my according:

Global Postfix configuration file. This file lists only a subset

of all parameters. For the syntax, and for a complete parameter

list, see the postconf(5) manual page (command: "man 5 postconf").

For common configuration examples, see BASICCONFIGURATIONREADME

and STANDARDCONFIGURATIONREADME. To find these documents, use

the command "postconf htmldirectory readmedirectory", or go to

For best results, change no more than 2-3 parameters at a time,

and test if Postfix still works after every change.


The soft_bounce parameter provides a limited safety net for

testing. When soft_bounce is enabled, mail will remain queued that

would otherwise bounce. This parameter disables locally-generated

bounces, and prevents the SMTP server from rejecting mail permanently

(by changing 5xx replies into 4xx replies). However, soft_bounce

is no cure for address rewriting mistakes or mail routing mistakes.

soft_bounce = no


The queue_directory specifies the location of the Postfix queue.

This is also the root directory of Postfix daemons that run chrooted.

See the files in examples/chroot-setup for setting up Postfix chroot

environments on different UNIX systems.

queue_directory = /var/spool/postfix

The command_directory parameter specifies the location of all

postXXX commands.

command_directory = /usr/sbin

The daemon_directory parameter specifies the location of all Postfix

daemon programs (i.e. programs listed in the file). This

directory must be owned by root.

daemon_directory = /usr/libexec/postfix

The data_directory parameter specifies the location of Postfix-writable

data files (caches, random numbers). This directory must be owned

by the mail_owner account (see below).

data_directory = /var/lib/postfix


The mail_owner parameter specifies the owner of the Postfix queue

and of most Postfix daemon processes. Specify the name of a user



particular, don't specify nobody or daemon. PLEASE USE A DEDICATED


mail_owner = postfix

The default_privs parameter specifies the default rights used by

the local delivery agent for delivery to external file or command.

These rights are used in the absence of a recipient user context.


default_privs = nobody


The myhostname parameter specifies the internet hostname of this

mail system. The default is to use the fully-qualified domain name

from gethostname(). $myhostname is used as a default value for many

other configuration parameters.

myhostname =

myhostname = virtual.domain.tld

The mydomain parameter specifies the local internet domain name.

The default is to use $myhostname minus the first component.

$mydomain is used as a default value for many other configuration


mydomain = domain.tld


The myorigin parameter specifies the domain that locally-posted

mail appears to come from. The default is to append $myhostname,

which is fine for small sites. If you run a domain with multiple

machines, you should (1) change this to $mydomain and (2) set up

a domain-wide alias database that aliases each user to


For the sake of consistency between sender and recipient addresses,

myorigin also specifies the default domain name that is appended

to recipient addresses that have no @domain part.

myorigin = $myhostname .............??????

myorigin = $mydomain .............?????


The inet_interfaces parameter specifies the network interface

addresses that this mail system receives mail on. By default,

the software claims all active interfaces on the machine. The

parameter also controls delivery of mail to user@[ip.address].

See also the proxy_interfaces parameter, for network addresses that

are forwarded to us via a proxy or network address translator.

Note: you need to stop/start Postfix when this parameter changes.

inet_interfaces = all

inet_interfaces = $myhostname

inet_interfaces = $myhostname, localhost

inet_interfaces = loopback-only

Enable IPv4, and IPv6 if supported

inet_protocols = all

The proxy_interfaces parameter specifies the network interface

addresses that this mail system receives mail on by way of a

proxy or network address translation unit. This setting extends

the address list specified with the inet_interfaces parameter.

You must specify your proxy/NAT addresses when your system is a

backup MX host for other domains, otherwise mail delivery loops

will happen when the primary MX host is down.

proxy_interfaces =

proxy_interfaces =

The mydestination parameter specifies the list of domains that this

machine considers itself the final destination for.

These domains are routed to the delivery agent specified with the

local_transport parameter setting. By default, that is the UNIX

compatible delivery agent that lookups all recipients in /etc/passwd

and /etc/aliases or their equivalent.

The default is $myhostname + localhost.$mydomain. On a mail domain

gateway, you should also include $mydomain.

Do not specify the names of virtual domains - those domains are

specified elsewhere (see VIRTUAL_README).

Do not specify the names of domains that this machine is backup MX

host for. Specify those names via the relay_domains settings for

the SMTP server, or use permitmxbackup if you are lazy (see


The local machine is always the final destination for mail addressed

to user@[] of an interface that the mail system

receives mail on (see the inet_interfaces parameter).

Specify a list of host or domain names, /file/name or type:table

patterns, separated by commas and/or whitespace. A /file/name

pattern is replaced by its contents; a type:table is matched when

a name matches a lookup key (the right-hand side is ignored).

Continue long lines by starting the next line with whitespace.


mydestination = $myhostname

mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain .......????

mydestination = $myhostname, localhost.$mydomain, localhost, $mydomain,

mail.$mydomain, www.$mydomain, ftp.$mydomain


The localrecipientmaps parameter specifies optional lookup tables

with all names or addresses of users that are local with respect

to $mydestination, $inetinterfaces or $proxyinterfaces.

If this parameter is defined, then the SMTP server will reject

mail for unknown local users. This parameter is defined by default.

To turn off local recipient checking in the SMTP server, specify

localrecipientmaps = (i.e. empty).

The default setting assumes that you use the default Postfix local

delivery agent for local delivery. You need to update the

localrecipientmaps setting if:

- You define $mydestination domain recipients in files other than

/etc/passwd, /etc/aliases, or the $virtualaliasmaps files.

For example, you define $mydestination domain recipients in

the $virtualmailboxmaps files.

- You redefine the local delivery agent in

- You redefine the "local_transport" setting in

- You use the "luserrelay", "mailboxtransport", or "fallback_transport"

feature of the Postfix local delivery agent (see local(8)).

Details are described in the LOCALRECIPIENTREADME file.

Beware: if the Postfix SMTP server runs chrooted, you probably have

to access the passwd file via the proxymap service, in order to

overcome chroot restrictions. The alternative, having a copy of

the system passwd file in the chroot jail is just not practical.

The right-hand side of the lookup tables is conveniently ignored.

In the left-hand side, specify a bare username, an @domain.tld

wild-card, or specify a user@domain.tld address.

localrecipientmaps = unix:passwd.byname $alias_maps

localrecipientmaps = proxy:unix:passwd.byname $alias_maps

localrecipientmaps =

The unknownlocalrecipientrejectcode specifies the SMTP server

response code when a recipient domain matches $mydestination or

${proxy,inet}interfaces, while $localrecipient_maps is non-empty

and the recipient address or address local-part is not found.

The default setting is 550 (reject mail) but it is safer to start

with 450 (try again later) until you are certain that your

localrecipientmaps settings are OK.

unknownlocalrecipientrejectcode = 550


The mynetworks parameter specifies the list of "trusted" SMTP

clients that have more privileges than "strangers".

In particular, "trusted" SMTP clients are allowed to relay mail

through Postfix. See the smtpdrecipientrestrictions parameter

in postconf(5).

You can specify the list of "trusted" network addresses by hand

or you can let Postfix do it for you (which is the default).

By default (mynetworks_style = subnet), Postfix "trusts" SMTP

clients in the same IP subnetworks as the local machine.

On Linux, this does works correctly only with interfaces specified

with the "ifconfig" command.

Specify "mynetworks_style = class" when Postfix should "trust" SMTP

clients in the same IP class A/B/C networks as the local machine.

Don't do this with a dialup site - it would cause Postfix to "trust"

your entire provider's network. Instead, specify an explicit

mynetworks list by hand, as described below.

Specify "mynetworks_style = host" when Postfix should "trust"

only the local machine.

mynetworks_style = class

mynetworks_style = subnet

mynetworks_style = host

Alternatively, you can specify the mynetworks list by hand, in

which case Postfix ignores the mynetworks_style setting.

Specify an explicit list of network/netmask patterns, where the

mask specifies the number of bits in the network part of a host


You can also specify the absolute pathname of a pattern file instead

of listing the patterns here. Specify type:table for table-based lookups

(the value on the table right-hand side is not used).

mynetworks =,

mynetworks = $config_directory/mynetworks

mynetworks = hash:/etc/postfix/network_table

The relay_domains parameter restricts what destinations this system will

relay mail to. See the smtpdrecipientrestrictions description in

postconf(5) for detailed information.

By default, Postfix relays mail

- from "trusted" clients (IP address matches $mynetworks) to any destination,

- from "untrusted" clients to destinations that match $relay_domains or

subdomains thereof, except addresses with sender-specified routing.

The default relay_domains value is $mydestination.

In addition to the above, the Postfix SMTP server by default accepts mail

that Postfix is final destination for:

- destinations that match $inetinterfaces or $proxyinterfaces,

- destinations that match $mydestination

- destinations that match $virtualaliasdomains,

- destinations that match $virtualmailboxdomains.

These destinations do not need to be listed in $relay_domains.

Specify a list of hosts or domains, /file/name patterns or type:name

lookup tables, separated by commas and/or whitespace. Continue

long lines by starting the next line with whitespace. A file name

is replaced by its contents; a type:name table is matched when a

(parent) domain appears as lookup key.

NOTE: Postfix will not automatically forward mail for domains that

list this system as their primary or backup MX host. See the

permitmxbackup restriction description in postconf(5).

relay_domains = $mydestination


The relayhost parameter specifies the default host to send mail to

when no entry is matched in the optional transport(5) table. When

no relayhost is given, mail is routed directly to the destination.

On an intranet, specify the organizational domain name. If your

internal DNS uses no MX records, specify the name of the intranet

gateway host instead.

In the case of SMTP, specify a domain, host, host:port, [host]:port,

[address] or [address]:port; the form [host] turns off MX lookups.

If you're connected via UUCP, see also the default_transport parameter.

relayhost = []:587
smtpalwayssendehlo = yes
usetls = yes
saslauthenable = yes
smtpsaslpasswordmaps = hash:/etc/postfix/saslpasswd
smtptlsCAfile = /etc/ssl/certs/ca-bundle.crt
smtpsaslsecurityoptions = noanonymous
sasltlssecurityoptions = noanonymous
tlsscertverifydepth = 5
smtptlsenforce_peername = no

relayhost = []

relayhost = [mailserver.isp.tld]

relayhost = uucphost

relayhost = [an.ip.add.ress]


The relayrecipientmaps parameter specifies optional lookup tables

with all addresses in the domains that match $relay_domains.

If this parameter is defined, then the SMTP server will reject

mail for unknown relay users. This feature is off by default.

The right-hand side of the lookup tables is conveniently ignored.

In the left-hand side, specify an @domain.tld wild-card, or specify

a user@domain.tld address.

relayrecipientmaps = hash:/etc/postfix/relay_recipients


The inflowdelay configuration parameter implements mail input

flow control. This feature is turned on by default, although it

still needs further development (it's disabled on SCO UNIX due

to an SCO bug).

A Postfix process will pause for $inflowdelay seconds before

accepting a new message, when the message arrival rate exceeds the

message delivery rate. With the default 100 SMTP server process

limit, this limits the mail inflow to 100 messages a second more

than the number of messages delivered per second.

Specify 0 to disable the feature. Valid delays are 0..10.

inflowdelay = 1s


The ADDRESSREWRITINGREADME document gives information about

address masquerading or other forms of address rewriting including

username->Firstname.Lastname mapping.


The VIRTUAL_README document gives information about the many forms

of domain hosting that Postfix supports.


See the discussion in the ADDRESSREWRITINGREADME document.


See the discussion in the ADDRESSREWRITINGREADME document.


The alias_maps parameter specifies the list of alias databases used

by the local delivery agent. The default list is system dependent.

On systems with NIS, the default is to search the local alias

database, then the NIS alias database. See aliases(5) for syntax


If you change the alias database, run "postalias /etc/aliases" (or

wherever your system stores the mail alias file), or simply run

"newaliases" to build the necessary DBM or DB file.

It will take a minute or so before changes become visible. Use

"postfix reload" to eliminate the delay.

alias_maps = dbm:/etc/aliases

alias_maps = hash:/etc/aliases

alias_maps = hash:/etc/aliases, nis:mail.aliases

alias_maps = netinfo:/aliases

The alias_database parameter specifies the alias database(s) that

are built with "newaliases" or "sendmail -bi". This is a separate

configuration parameter, because alias_maps (see above) may specify

tables that are not necessarily all under control by Postfix.

alias_database = dbm:/etc/aliases

alias_database = dbm:/etc/mail/aliases

alias_database = hash:/etc/aliases

alias_database = hash:/etc/aliases, hash:/opt/majordomo/aliases

ADDRESS EXTENSIONS (e.g., user+foo)

The recipient_delimiter parameter specifies the separator between

user names and address extensions (user+foo). See canonical(5),

local(8), relocated(5) and virtual(5) for the effects this has on

aliases, canonical, virtual, relocated and .forward file lookups.

Basically, the software tries user+foo and .forward+foo before

trying user and .forward.

recipient_delimiter = +


The home_mailbox parameter specifies the optional pathname of a

mailbox file relative to a user's home directory. The default

mailbox file is /var/spool/mail/user or /var/mail/user. Specify

"Maildir/" for qmail-style delivery (the / is required).

home_mailbox = Mailbox

home_mailbox = Maildir/

The mailspooldirectory parameter specifies the directory where

UNIX-style mailboxes are kept. The default setting depends on the

system type.

mailspooldirectory = /var/mail

mailspooldirectory = /var/spool/mail

The mailbox_command parameter specifies the optional external

command to use instead of mailbox delivery. The command is run as

the recipient with proper HOME, SHELL and LOGNAME environment settings.

Exception: delivery for root is done as $default_user.

Other environment variables of interest: USER (recipient username),

EXTENSION (address extension), DOMAIN (domain part of address),

and LOCAL (the address localpart).

Unlike other Postfix configuration parameters, the mailbox_command

parameter is not subjected to $parameter substitutions. This is to

make it easier to specify shell syntax (see example below).

Avoid shell meta characters because they will force Postfix to run

an expensive shell process. Procmail alone is expensive enough.



mailbox_command = /some/where/procmail

mailbox_command = /some/where/procmail -a "$EXTENSION"

The mailbox_transport specifies the optional transport in

to use after processing aliases and .forward files. This parameter

has precedence over the mailboxcommand, fallbacktransport and

luser_relay parameters.

Specify a string of the form transport:nexthop, where transport is

the name of a mail delivery transport defined in The

:nexthop part is optional. For more details see the sample transport

configuration file.

NOTE: if you use this feature for accounts not in the UNIX password

file, then you must update the "localrecipientmaps" setting in

the file, otherwise the SMTP server will reject mail for

non-UNIX accounts with "User unknown in local recipient table".

Cyrus IMAP over LMTP. Specify ``lmtpunix cmd="lmtpd"

listen="/var/imap/socket/lmtp" prefork=0'' in cyrus.conf.

mailbox_transport = lmtp:unix:/var/lib/imap/socket/lmtp

If using the cyrus-imapd IMAP server deliver local mail to the IMAP

server using LMTP (Local Mail Transport Protocol), this is prefered

over the older cyrus deliver program by setting the

mailbox_transport as below:

mailbox_transport = lmtp:unix:/var/lib/imap/socket/lmtp

The efficiency of LMTP delivery for cyrus-imapd can be enhanced via

these settings.

localdestinationrecipient_limit = 300

localdestinationconcurrency_limit = 5

Of course you should adjust these settings as appropriate for the

capacity of the hardware you are using. The recipient limit setting

can be used to take advantage of the single instance message store

capability of Cyrus. The concurrency limit can be used to control

how many simultaneous LMTP sessions will be permitted to the Cyrus

message store.

Cyrus IMAP via command line. Uncomment the "cyrus...pipe" and

subsequent line in

mailbox_transport = cyrus .................????

The fallback_transport specifies the optional transport in

to use for recipients that are not found in the UNIX passwd database.

This parameter has precedence over the luser_relay parameter.

Specify a string of the form transport:nexthop, where transport is

the name of a mail delivery transport defined in The

:nexthop part is optional. For more details see the sample transport

configuration file.

NOTE: if you use this feature for accounts not in the UNIX password

file, then you must update the "localrecipientmaps" setting in

the file, otherwise the SMTP server will reject mail for

non-UNIX accounts with "User unknown in local recipient table".

fallback_transport = lmtp:unix:/var/lib/imap/socket/lmtp

fallback_transport =

The luser_relay parameter specifies an optional destination address

for unknown recipients. By default, mail for unknown@$mydestination,

unknown@[$inetinterfaces] or unknown@[$proxyinterfaces] is returned

as undeliverable.

The following expansions are done on luser_relay: $user (recipient

username), $shell (recipient shell), $home (recipient home directory),

$recipient (full recipient address), $extension (recipient address

extension), $domain (recipient domain), $local (entire recipient

localpart), $recipient_delimiter. Specify ${name?value} or

${name:value} to expand value only when $name does (does not) exist.

luser_relay works only for the default Postfix local delivery agent.

NOTE: if you use this feature for accounts not in the UNIX password

file, then you must specify "localrecipientmaps =" (i.e. empty) in

the file, otherwise the SMTP server will reject mail for

non-UNIX accounts with "User unknown in local recipient table".

luser_relay = $

luser_relay = $

luser_relay = admin+$local


The controls listed here are only a very small subset. The file

SMTPDACCESSREADME provides an overview.

The header_checks parameter specifies an optional table with patterns

that each logical message header is matched against, including

headers that span multiple physical lines.

By default, these patterns also apply to MIME headers and to the

headers of attached messages. With older Postfix versions, MIME and

attached message headers were treated as body text.

For details, see "man header_checks".

headerchecks = regexp:/etc/postfix/headerchecks


Postfix maintains per-destination logfiles with information about

deferred mail, so that mail can be flushed quickly with the SMTP

"ETRN domain.tld" command, or by executing "sendmail -qRdomain.tld".

See the ETRN_README document for a detailed description.

The fastflushdomains parameter controls what destinations are

eligible for this service. By default, they are all domains that

this server is willing to relay mail to.

fastflushdomains = $relay_domains


The smtpd_banner parameter specifies the text that follows the 220

code in the SMTP server's greeting banner. Some people like to see

the mail version advertised. By default, Postfix shows no version.

You MUST specify $myhostname at the start of the text. That is an

RFC requirement. Postfix itself does not care.

smtpdbanner = $myhostname ESMTP $mailname

smtpdbanner = $myhostname ESMTP $mailname ($mail_version)


How many parallel deliveries to the same user or domain? With local

delivery, it does not make sense to do massively parallel delivery

to the same user, because mailbox updates must happen sequentially,

and expensive pipelines in .forward files can cause disasters when

too many are run at the same time. With SMTP deliveries, 10

simultaneous connections to the same domain could be sufficient to

raise eyebrows.

Each message delivery transport has its XXXdestinationconcurrency_limit

parameter. The default is $defaultdestinationconcurrency_limit for

most delivery transports. For the local delivery agent the default is 2.

localdestinationconcurrency_limit = 2

defaultdestinationconcurrency_limit = 20


The debugpeerlevel parameter specifies the increment in verbose

logging level when an SMTP client or server host name or address

matches a pattern in the debugpeerlist parameter.

debugpeerlevel = 3

The debugpeerlist parameter specifies an optional list of domain

or network patterns, /file/name patterns or type:name tables. When

an SMTP client or server host name or address matches a pattern,

increase the verbose logging level by the amount specified in the

debugpeerlevel parameter.

debugpeerlist =

debugpeerlist = some.domain

The debugger_command specifies the external command that is executed

when a Postfix daemon program is run with the -D option.

Use "command .. & sleep 5" so that the debugger can attach before

the process marches on. If you use an X-based debugger, be sure to

set up your XAUTHORITY environment variable before starting Postfix.

debuggercommand =
ddd $daemon
directory/$processname $processid & sleep 5

If you can't use X, use this to capture the call stack when a

daemon crashes. The result is in a file in the configuration

directory, and is named after the process name and the process ID.

debugger_command =

PATH=/bin:/usr/bin:/usr/local/bin; export PATH; (echo cont;

echo where) | gdb $daemondirectory/$processname $process_id 2>&1

>$configdirectory/$processname.$process_id.log & sleep 5

Another possibility is to run gdb under a detached screen session.

To attach to the screen sesssion, su root and run "screen -r

<id_string>" where <id_string> uniquely matches one of the detached

sessions (from "screen -list").

debugger_command =

PATH=/bin:/usr/bin:/sbin:/usr/sbin; export PATH; screen

-dmS $processname gdb $daemondirectory/$process_name

$process_id & sleep 1


The following parameters are used when installing a new Postfix version.

sendmail_path: The full pathname of the Postfix sendmail command.

This is the Sendmail-compatible mail posting interface.

sendmail_path = /usr/sbin/sendmail.postfix

newaliases_path: The full pathname of the Postfix newaliases command.

This is the Sendmail-compatible command to build alias databases.

newaliases_path = /usr/bin/newaliases.postfix

mailq_path: The full pathname of the Postfix mailq command. This

is the Sendmail-compatible mail queue listing command.

mailq_path = /usr/bin/mailq.postfix

setgid_group: The group for mail submission and queue management

commands. This must be a group name with a numerical group ID that

is not shared with other accounts, not even with the Postfix account.

setgid_group = postdrop

html_directory: The location of the Postfix HTML documentation.

html_directory = no

manpage_directory: The location of the Postfix on-line manual pages.

manpage_directory = /usr/share/man

sample_directory: The location of the Postfix sample configuration files.

This parameter is obsolete as of Postfix 2.1.

sample_directory = /usr/share/doc/postfix-2.10.1/samples

readme_directory: The location of the Postfix README files.

readmedirectory = /usr/share/doc/postfix-2.10.1/READMEFILES
smtpdhelorequired = yes

and here is the my sasl_passwd file content:


and tried at below sql command but pgmail can not sending mail to my gmail account, how it fix?

select pgmail('','','message subject','message content');

thanks in advence...

Be the first one to answer this question.