Here is the script I use to create the postfix/postgres maps.
#!/usr/bin/perl #=============================================================================== # # FILE: postfix_create_pgsql_files.pl # # USAGE: ./postfix_create_pgsql_files.pl # # DESCRIPTION: create the postgres maps for postfix and produce a shell # script to save the current settings and set the new ones # # This software is provided under the GNU GPLv3 license # for terms and conditions see http://www.gnu.org/licenses/gpl.html # # ******************** IMPORTANT ************************** # Before running, check your database connections # and the directory and postconf utility locations # Only basic error correcting is implemented # # The configuration is provided in hashes at the top of this # file. # # Check you database settings in the $DbConnectionSettings hash # # In the $UserSettings map the following keys need to be defined # * postfix_conf_dir # * postconf # # Map definitions are held in the $MapFileSettings hash # Optional settings are in the $MorePostfixSettings hash # # ******************** IMPORTANT ************************** # # OPTIONS: none # REQUIREMENTS: perl, Carp, Readonly # BUGS: minimal error handling # NOTES: # AUTHOR: (tim ),# VERSION: 0.8 # CREATED: 14/10/12 12:19:31 GMT # REVISION: --- #=============================================================================== use strict; use warnings; use Carp; use Readonly; # # Fill in the correct connection strings for your database here # my $DbConnectionSettings = { pg => { host => 'database', database => 'postfix_test', user => 'postfixadmin_test', password => 'postfixtestpass', port => '5432', mapprefix => 'pgsql', comments => '', }, my => { host => 'database', database => 'postfixtest', user => 'postfixadmin_test', password => 'postfixpasstest', mapprefix => 'mysql', comments => '', }, }; # # Other required settings. read first # my $UserSettings = { # # Postfix configuration directory # #postfix_conf_dir => '/tmp/pf', # testing #postfix_conf_dir => '/usr/local/etc/postfix', # FreeBSD #postfix_conf_dir => '/etc/postfix', # linux # # check the location of your postconf command and then set this # #postconf => '/usr/sbin/postconf', # Linux #postconf => '/usr/local/sbin/postconf', # FreeBSD conf_dir => 'pgsql', # sub dir under postfix_conf_dir to place maps use_proxy => 'yes', # add the proxy: directive in from of the map type # # name of the shell script containing the postconf commands to save the # existing settings and add the new ones. # shell_script => 'Postconf.sh', # ensure it's at the top/first when listing postfix_user => 'root', # name of the owner of the map files postfix_group => 'postfix', # postfix group - should be postfix create_test_sql_line => 'yes', # create connection test lines in the shell script sql_cmd_lines => { pg => "psql -h HOST -U USER -w DATABASE < < _PGCMD\n\\dt\n_PGCMD\n\n", my => "mysql -u USER -h HOST -p -D DATABASE< <_MYCMD\nselect * from alias\n_MYCMD\n\n", }, version => '0.8', }; # # I don't use mySql - so I can't guarentee mysql # queries will work. # # format is # map_description=> { # file => filename in the conf_dir directory # pg => postgres query # my => mysql query # postconf => array of config lines in main.cf for this map # comments => optional array of comments for map file # } # my $MapFileSettings = { virtual_domains_maps => { file => "virtual_domains_maps.cf", pg => { query => "SELECT domain FROM domain WHERE domain=' % s'", }, my => { query => "SELECT domain FROM domain WHERE domain=' % s'", }, postconf => [ 'virtual_mailbox_domains=MAPTYPE:MAPFILE', ], }, alias_domain => { file => 'alias_domain.cf', pg => { query => "SELECT target_domain FROM alias_domain WHERE alias_domain='%s'", }, my => { query => "SELECT target_domain FROM alias_domain WHERE alias_domain='%s'", }, postconf => [ 'virtual_alias_domains=MAPTYPE:MAPFILE', ], }, gids => { file => 'gids.cf', comments => [ '# if you are using static GIDS you could also return a constant', '# the query to return a different guid for each user', "# query=SELECT gid FROM mailbox WHERE username='%s'", '# or return a static value from the map', '# query=SELECT 12 as gid', '# ', '# or you could use the "static" keyword in the configuration. eg: ', '# ', '# virtual_gid_maps = static:12', ], my => { query => "SELECT gid FROM mailbox WHERE username='%s'", }, pg => { query => 'SELECT 12 as gid', }, postconf => [ 'virtual_gid_maps=MAPTYPE:MAPFILE', ], }, relay_alias_domains_maps => { file => 'relay_alias_domains_maps.cf', pg => { query => "SELECT a.domain FROM domain a, domain t, alias_domain" . " WHERE a.domain='%s' AND a.transport = 'relay' AND a.active AND" . " alias_domain.alias_domain = a.domain AND alias_domain.active" . " AND t.domain = alias_domain.target_domain AND t.transport = 'relay'" . " AND t.active", }, my => { query => "SELECT a.domain FROM domain a, domain t, alias_domain" . " WHERE a.domain='%s' AND a.transport = 'relay' AND a.active AND" . " alias_domain.alias_domain = a.domain AND alias_domain.active" . " AND t.domain = alias_domain.target_domain AND t.transport = 'relay'" . " AND t.active", }, postconf => [ 'relay_domains=$mydestination, MAPTYPE:MAPFILE', ], }, relay_domains => { file => 'relay_domains.cf', pg => { query => "select * from alias_domain where alias_domain = '%s' AND alias_domain.active )", }, postconf => [ 'relay_domains =MAPTYPE:MAPFILE', ], }, relay_password => { file => 'relay_password.cf', pg => { query => "select username || ':' || password as pw from x_relay_password" . " where relay='%s' and active", }, postconf => [ 'smtp_sasl_password_maps = MAPTYPE:MAPFILE', ], }, relay_transports => { file => 'relay_transports.cf', pg => { query => "SELECT 'relay:[' || description || ']' as t FROM domain" . " WHERE domain='%s' and transport = 'relay' and active ", }, postconf => [ 'smtp_sasl_password_maps = MAPTYPE:MAPFILE', ], }, transport => { file => 'transport.cf', pg => { query => "SELECT transport FROM domain WHERE domain='%s'", }, my => { query => "SELECT transport FROM domain WHERE domain='%s'", }, postconf => [ 'transport_maps=MAPTYPE:MAPFILE', ], }, uids => { file => 'uids.cf', pg => { query => "SELECT 3000 as uid", }, my => { query => "SELECT uid FROM mailbox WHERE username='%s'", }, postconf => [ 'virtual_uid_maps = MAPTYPE:MAPFILE', ], comments => [ '# if you are using static UIDS you could also return a constant', '# the query to return a different uid for each user', "# query=SELECT uid FROM mailbox WHERE username='%s'", '# ', '# or you could use the "static" keyword in the configuration. eg: ', '# ', '# virtual_uid_maps = static:3000', '# virtual_minimum_uid = 3000', ], }, virtual_alias_maps => { file => 'virtual_alias_maps.cf', pg => { query => "SELECT goto FROM alias WHERE address='%s' AND active", }, my => { query => "SELECT goto FROM alias WHERE address='%s' AND active", }, postconf => [ 'virtual_alias_maps=MAPTYPE:MAPFILE, hash:/var/lib/mailman/data/aliases, hash:/etc/postfix/aliases', 'alias_maps=MAPTYPE:MAPFILE, hash:/var/lib/mailman/data/aliases, hash:/etc/postfix/aliases', ], }, virtual_domains_maps => { file => 'virtual_domains_maps.cf', pg => { query => "SELECT domain FROM domain WHERE domain='%s'", }, my => { query => "SELECT domain FROM domain WHERE domain='%s'", }, postconf => [ 'virtual_mailbox_domains = MAPTYPE:MAPFILE', ], comments => [ '#optional query to use when relaying for backup MX', "#query = SELECT domain FROM domain WHERE domain='%s' and backupmx = false and active = t", ], }, virtual_mailbox_limits => { # I don't use quotas, so this isn't enabled. file => 'virtual_mailbox_limits.cf', comments => [ '# quota support', ], postconf => ['# virtual_mailbox_limit=MAPTYPE:MAPFILE'], pg => { query => "SELECT quota FROM mailbox WHERE username='%s'", }, my => { query => "SELECT quota FROM mailbox WHERE username='%s'", }, }, virtual_mailbox_maps => { file => 'virtual_mailbox_maps.cf', pg => { query => "SELECT maildir FROM postfix_mailbox " . " WHERE username='%s' AND active", }, my => { query => "SELECT maildir FROM postfix_mailbox " . " WHERE username='%s' AND active", }, postconf => [ 'virtual_mailbox_maps=MAPTYPE:MAPFILE', ], }, }; # # fill in another other settings you would like applied (domains etc) # shfilecommands => array of additional lines for the shell script # my $MorePostfixSettings = { postconf => [ 'alias_database = hash:/var/lib/mailman/data/aliases, hash:/etc/postfix/aliases', 'virtual_mailbox_base=/home/vmail', 'virtual_transport=dovecot', 'mailbox_command=', 'virtual_mailbox_limit=0', 'mydestination=', 'mydomain=postman.local', 'myhostname=postman.ci9', 'mynetworks=127.0.0.0/8', 'message_size_limit=20480000', ], # # text to include verbatim in the shell file # shfilecommands => [ '# ', 'echo Remember to check the master.cf file', '# enter the following lines for dovecot delivery to the master.cf file', '# point deliver to the location your utility is located', '# dovecot unix - n n - - pipe', '# flags=DRhu user=vmail:mail argv=/usr/lib/dovecot/deliver -d ${recipient} -a ${recipient} -f ${sender}', '#', ], }; ############################################################################### # # START OF SCRIPT # ############################################################################### # sub MkTimeStamp { #=== FUNCTION ================================================================ # return a formatted date in YYYYMMDDHHmmSS format # uses currrent time #=============================================================================== my ( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) = localtime(time); $year += 1900; $mon++; return sprintf( '%4.0d-%2.2d-%2.2d-%2.0d%2.0d', $year, $mon, $mday, $hour, $min ); } sub MkPostConfCommand { #=== FUNCTION ================================================================ # return a correctly formatted postfix configuration line # If the global use_proxy is set to yes in the $UserSettings hash then the # argument "proxy" is prefixed to the map type. # ie. command=proxy:pgsql:/path/to/file or # command=pgsql:/path/to/file or # # args # the postfix command line # the name of the map file # the directory the map file is located # the maptype as defined in the DbConnectionSettings hash. pgsql or mysql etc # returns # formatted line. #=============================================================================== my $postfixConfLine = shift; my $mapFile = shift; my $destDir = shift; my $mapType = shift; $postfixConfLine =~ s/MAPFILE/$destDir\/$mapFile/; $postfixConfLine =~ s/MAPTYPE/proxy:MAPTYPE/ if ( $UserSettings->{use_proxy} ); $postfixConfLine =~ s/MAPTYPE/$mapType/; return $postfixConfLine; } sub CreateFileHeader { #=== FUNCTION ================================================================ # create the database connection string used by each map file # args: # database => # dbtype => # user => # passwd => >database user's password> # include_comments => yes or no. Include some extra comments from the db # settings hash # # returns: # string holding the file header #=============================================================================== my ($arg_ref) = @_; my $retVal = "#\n"; my $connType; Readonly my %ARG_DEFAULTS => ( database => '', dbtype => 'pg', user => '', password => '', include_comments => "yes", ); my %args = ref $arg_ref eq 'HASH' ? ( %ARG_DEFAULTS, %{$arg_ref} ) : %ARG_DEFAULTS; $connType = $args{db}; if ( defined $DbConnectionSettings->{$connType} ) { $retVal .= "# Database connection details\n" . "# Connection type is [$connType] "; if ( defined $DbConnectionSettings->{$connType}->{type} ) { $retVal .= "($DbConnectionSettings->{$connType}->{type})"; } $retVal .= "\n"; if ( defined $DbConnectionSettings->{$connType}->{comments} ) { $retVal .= "# $DbConnectionSettings->{$connType}->{comments}\n"; } $retVal .= "#\n"; # # add all the connection settings if the are defined # foreach my $f ( 'host', 'database', 'user', 'password', 'port' ) { if ( defined( $DbConnectionSettings->{$connType}->{$f} ) ) { $retVal .= "$f = $DbConnectionSettings->{$connType}->{$f}\n"; } } } else { $retVal .= < < "_CONNERR"; # # ********* ERROR **********\ # # No Valid Database Connection Provided for # database of type [$connType] # # Please provide correct values for the following connection details # # host # database # user # password # _CONNERR } $retVal .= "\n"; if ( ( $args{include_comments} eq 'Yes' ) && ( defined $DbConnectionSettings->{comments} ) ) { # # add any additional comments - if any # for ( my $j = 0; $j < $#{ $DbConnectionSettings->{comments} }; $j++ ) { $retVal .= "# $DbConnectionSettings->{comments}->[$j]\n"; } $retVal .= "\n#\n"; } return $retVal; } sub MkMapFile { #=== FUNCTION ================================================================ # write out the map file # # args # header : the database connection information # query : the query to run # file : the name of the file # destination : location of postfix configuration directory (or other location) # maptype : pgsql or mysql # timestamp : to identify when the file was created # comments : any additional lines to go into the map file (comments etc) # # returns # a string containing the whole map file # #=============================================================================== my ($arg_ref) = @_; my $retVal = ''; my $connType; my $mapFileContents; Readonly my %ARG_DEFAULTS => ( # maptype => 'pgsql', # set correct default if required header => "#\n", query => 'select 1;', destination => $ENV{TMPDIR}, maptype => 'Unknown', # raise an error in main.cf timestamp => MkTimeStamp(), file => int( (rand) * 100000000 ) . '.cf', associated_postconf_line => undef, ); my %args = ref $arg_ref eq 'HASH' ? ( %ARG_DEFAULTS, %{$arg_ref} ) : %ARG_DEFAULTS; my $file = "$arg_ref->{destination}/$arg_ref->{file}"; if ( -f $file ) { unlink $file; } while ( $args{query} =~ s/ / / ) { 0; } # strip multiple spaces out of the query # # common settings - database connection details # and a timestamp # $mapFileContents = < < "_SHFILE_TOP"; # File: $args{file} # # Created: $args{timestamp} # # Auto-generated # $args{header} query = $args{query} _SHFILE_TOP # # add the associated main.cf settings as comments to # the file, so to provide an easy reference when looking # at the map # if ( defined( $args{associated_postconf_line} ) ) { $mapFileContents .= "\n#\n#\n" . "# The line(s) in your main.cf file relating to this file:\n"; for ( my $j = 0; $j <= $#{ $args{associated_postconf_line} }; $j++ ) { $mapFileContents .= '# ' . MkPostConfCommand( $args{associated_postconf_line}->[$j], # command $args{file}, # name of the map file $args{destination}, # destination directory $args{maptype}, # type of map (pgsql.mysql) ) . "\n\n"; } } # # if there are additional lines to add to the map file add them. # if ( defined( $args{comments} ) ) { $mapFileContents .= "\n\n#\n# Additional comments\n#\n#\n"; foreach my $comment ( @{ $args{comments} } ) { $mapFileContents .= "$comment\n"; } } $mapFileContents .= "\n\n# END OF GENERATED MAP\n"; open my $CF, ">", $file || carp "can't open $file for writing"; print $CF $mapFileContents; close $CF; return $mapFileContents; } sub MkShellFile { #=== FUNCTION ================================================================ # # create the shell script that will save the existing settings # and then run teh new ones # # args # command list: reference to the array # comments ; reference to comment array # shellcommands : reference to shell command array # timestamp: timestamp string # conntype : connection (database) type # destination directory: where to place this (same as map files) # # returns # string holding the shell file # #=============================================================================== my ($arg_ref) = @_; my $commands = $arg_ref->{commands}; my $comments = $arg_ref->{comments}; my $timestamp = $arg_ref->{timestamp}; my $shellCmds = $arg_ref->{shellcommands}; my $connType = $arg_ref->{conntype}; my $destDir = $arg_ref->{destination}; # # Step 1. create the header # my $shellFile = < < "_SHFILE"; #!/bin/bash # # Postfix commands to set references to these maps # # Created: $timestamp # # PCCMD=$UserSettings->{postconf} echo The postconf command is \${PCCMD} # timestamp ts=\$(date +\%Y\%m\%d\%H\%M\%S) savedfile=$destDir/saved_settings.cf.\$ts echo the old settings will be saved to file \${savedfile} # # Record the current settings for posterity # _SHFILE # # Step 2. create lines to save the existing settings # and place them in a save file for reference # for ( my $i = 0; $i < $#{$commands}; $i++ ) { next if $commands =~ /^#/; ( $shellFile .= '$PCCMD -n "' . $commands->[$i] ) =~ s/=.*$//; $shellFile .= '" >> ${savedfile}' . "\n"; } # # Step 3. Apply the settings to the main.cf file # using the postconf command # $shellFile .= "#\n#\n# Set the parameters\n#\n"; for ( my $i = 0; $i < $#{$commands}; $i++ ) { $shellFile .= "\$PCCMD -e \"$commands->[$i]\"\n"; } # # Step 4. include any additional comments or commands # $shellFile .= "#\n#\n"; for ( my $i = 0; $i < $#{$shellCmds}; $i++ ) { $shellFile .= "$shellCmds->[$i]\n"; } $shellFile .= "#\n#\n"; for ( my $i = 0; $i < $#{$comments}; $i++ ) { $shellFile .= "$comments->[$i]\n"; } # # Step 5. # If config->{create_test_sql_line} is yes # then make a test connection to the database # # if ( $UserSettings->{create_test_sql_line} eq "yes" ) { $shellFile .= "\n\n#\n# command lines to test the connection settings\n" . "# copy to the command line to use.\n#\n"; foreach my $key ( sort keys $UserSettings->{sql_cmd_lines} ) { my $sql = $UserSettings->{sql_cmd_lines}->{$key}; my $host = $DbConnectionSettings->{$key}->{host}; my $database = $DbConnectionSettings->{$key}->{database}; my $port = $DbConnectionSettings->{$key}->{port}; my $password = $DbConnectionSettings->{$key}->{password}; my $user = $DbConnectionSettings->{$key}->{user}; $sql =~ s/DATABASE/$database/; $sql =~ s/USER/$user/; $sql =~ s/HOST/$host/; $sql =~ s/PORT/$port/; ## uncomment to store plaintext password in file #$sql =~ s/PASSWORD/$password/; $shellFile .= "$sql\n"; } } $shellFile .= "#\n#\n# END OF postconf.cf settings\n"; return $shellFile; } sub MkFiles { #=== FUNCTION ================================================================ # # go through the $MapFileSettings hash and produce all the map files and place # in the location defing in the config hash. # store a list of all the required postfix commands and create a # small shell script that can save the existing settings and then # set the options to point to the new maps. # # args # db : this is a valid type as defined in the db settings (pg or my) # destination: destination directory to pace maps # # returns # 0; # #=============================================================================== my ($arg_ref) = @_; my $connType = $arg_ref->{db}; # connection type my $destDir = $arg_ref->{destination}; my $timestamp = MkTimeStamp(); # timestamp text for file headers my @shCommands; # array to hold all shell script my @commands; # array to hold all postconf commands my @comments; # array to hold additional comments my $shellFileContents; # hold the generated shell script my $confcmd; # tmp var to hold postconf command my $map; # tmp var to hold current map details my $shFileName = "$destDir/$UserSettings->{shell_script}"; # file name of sh script my $postfixUID = getpwnam( $UserSettings->{postfix_user} ); my $postfixGID = getgrnam( $UserSettings->{postfix_group} ); if ( !defined $postfixUID ) { croak " Can't find user $UserSettings->{postfix_user} . Please check user exists "; } # mapprefix : the string required in the map option (pgsql or mysql) my $mapPrefix = $DbConnectionSettings->{ $arg_ref->{db} }->{mapprefix}; # # create the database connection details use by all files # my $header = CreateFileHeader( { db => $connType } ); if ( !-d $destDir ) { mkdir $destDir; croak " Can't make directory $destDir" if ( !-d $destDir ); } # # step 1. Create all the map files # foreach my $def ( sort keys $MapFileSettings ) { $map = $MapFileSettings->{$def}; if ( defined( $map->{$connType} ) ) { MkMapFile( { file => $map->{file}, header => $header, query => $map->{$connType}->{query}, destination => $destDir, associated_postconf_line => $map->{postconf}, timestamp => $timestamp, maptype => $mapPrefix, comments => $map->{comments}, postfix_gid => $postfixGID, postfix_uid => $postfixUID, } ); # # change the ownership and rights to rw-r---- # push( @shCommands, "chown $UserSettings->{postfix_user}:$UserSettings->{postfix_group} $map->{file}" ); push( @shCommands, "chmod 0640 $map->{file}" ); if ( defined $map->{postconf} ) { # extract the postconf settings for ( my $j = 0; $j < = $#{ $map->{postconf} }; $j++ ) { $confcmd = MkPostConfCommand( $map->{postconf}->[$j], # the command $map->{file}, # the name of the map file $destDir, # location directory $mapPrefix # map type (pgsql/mysql ); push( @commands, $confcmd ); } } } } # # step 2: # get the additional postfix commands # TODO: no substutions done here but could be easily added if needed # for my $command ( @{ $MorePostfixSettings->{postconf} } ) { push( @commands, $command ); } # # step 3: # get the additional comments/commands # for my $comment ( @{ $MorePostfixSettings->{shfilecommands} } ) { push( @comments, $comment ); } # # Step 4: # add all the commands to a shell script and write it # to the same directory as the configuration files # $shellFileContents = MkShellFile( { commands => \@commands, destination => $destDir, timestamp => $timestamp, comments => \@comments, shellcommands => \@shCommands, conntype => $connType, } ); unlink $shFileName if ( -e $shFileName ); open my $CONF, " > ", $shFileName || croak " Can't open $shFileName for writing $!\n "; print $CONF $shellFileContents; close $CONF; return 0; } ######################################### # # START MAIN # ######################################### if ( !defined( $UserSettings->{postfix_conf_dir} ) ) { croak 'Configuration directory not defined in $UserSettings->{postfix_conf_dir}'; } if ( !defined( $UserSettings->{postconf} ) ) { croak 'Postfix postconf utility is not defined in $UserSettings->{postconf}'; } if ( !defined( $UserSettings->{conf_dir} ) ) { croak 'map directory is not defined in $UserSettings->{conf_dir}'; } my $dest_dir = "$UserSettings->{postfix_conf_dir}/$UserSettings->{conf_dir}"; MkFiles( { db => 'pg', destination => $dest_dir } ); 0;