Create Postgres password protected backup from C#

Question

I want to create a password protected Postgres backup from C#

I tried next code but I think there is a more secure way: to pass backup directly from pg_dump to arhive without saving it on the disk and to add password form env variable like is done with PGPASSWORD

   string pgDumpPath, outFile, host, port, database, user, password, dumpCommand, batFilePath, zipPth, outPath, tmpPath;

    pgDumpPath = @"d:\_dev_\postgres\pgsql\bin\pg_dump.exe";
    host = "127.0.0.1";
    port = "5432";
    database = "postgres";
    user = "postgres";
    password = "mypass";
    dumpCommand = pgDumpPath + " -Fc" + " -h " + host + " -p " + port + " -d " + database + " -U " + user;
    tmpPath = Path.GetTempPath();
    batFilePath = tmpPath + Guid.NewGuid().ToString() + ".cmd";

    outFile = @"myBackup";

    outPath = @"d:\";

    zipPth = @"c:\Program Files\7-Zip\7z.exe";
    string tmpfile = "\"" + tmpPath + outFile + ".sql\"";
    Environment.SetEnvironmentVariable("PGPASSWORD", password);

    try
    {


        File.WriteAllText(batFilePath,
         "@echo off " + "\n" +
          dumpCommand + "  > " + tmpfile + "\n" + "\"" +
          zipPth + "\"" + " a \"" + outPath + outFile + ".zip\" -p" + password + " " +tmpfile +
           "  &DEL " + tmpfile  +
           "  &DEL \"" + batFilePath + "\"",
         Encoding.ASCII);


        if (File.Exists(tmpfile))
            File.Delete(tmpfile);

        var oInfo = new ProcessStartInfo(batFilePath)
        {
            UseShellExecute = false,
            CreateNoWindow = true
        };

        using (var proc = Process.Start(oInfo))
        {
            proc.WaitForExit();
            proc.Close();
        }
    }
    finally
    {
        if (File.Exists(batFilePath))
            File.Delete(batFilePath);

    }

Answer 1

got a working solution:

string database = "postgres",user = "postgres", password = "mypass", archivePassword = "mypass2";

            var outFile = @"d:\postgres_" + DateTime.Now.ToString("yyyy") + "_" + DateTime.Now.ToString("MM") + "_" +
                                     DateTime.Now.ToString("dd") + "_" + DateTime.Now.ToString("hh") + DateTime.Now.ToString("mm") +
                                     DateTime.Now.ToString("ss");

            var sInfo = new ProcessStartInfo()
            {
                FileName = @"d:\_dev_\postgres\pgsql\bin\pg_dump.exe",
                Arguments = $"-U {user} -w {database}",
                UseShellExecute = false,
                RedirectStandardOutput = true,
                CreateNoWindow = true
            };

            sInfo.EnvironmentVariables.Add("PGPASSWORD", password);


            var cInfo = new ProcessStartInfo()
            {

                FileName = @"c:\Program Files\7-Zip\7z.exe",
                Arguments = $"a -p{archivePassword} -si{Path.GetFileNameWithoutExtension(outFile)} {outFile}",
                UseShellExecute = false,
                RedirectStandardInput = true,
                CreateNoWindow = true
            };



            using (var proc = Process.Start(sInfo))
            {
                using (var comp = Process.Start(cInfo))
                {
                    proc.StandardOutput.BaseStream.CopyTo(comp.StandardInput.BaseStream);

                    proc.WaitForExit();
                    comp.StandardInput.BaseStream.Flush();
                    comp.StandardInput.BaseStream.Dispose();
                    comp.WaitForExit();

                }
            }