如何在C#创建PostgreSQL数据库时向密码提示符发送标准输入

我有一个基于.Net Framework 4.7.2的应用，需要连接PostgreSQL数据库。当数据库已存在时连接正常，但如果数据库不存在，希望在应用中创建它。计划使用ProcessStartInfo执行命令：

string strDbCreationCmd = $"createdb -U postgres {dbName}";

直接在命令行执行该命令时，会提示输入超级用户密码，手动输入后可成功创建数据库。但修改Process执行代码后，虽然能弹出命令提示符显示密码输入框，但尝试通过代码自动发送密码（示例为"test"）时无响应，只能手动输入才能继续。

以下是我的命令行执行代码：

static Tuple<string, string> RunCmdLine(string cmd, string navCmd1 = "", string navCmd2 = ""){
var startInfo = new ProcessStartInfo()
{
    WindowStyle = ProcessWindowStyle.Normal,
    WorkingDirectory = @"C:\",
    FileName = "cmd.exe",
    UseShellExecute = false,
    RedirectStandardInput = true,
    RedirectStandardOutput = true,
    RedirectStandardError = true
};
var response = new StringBuilder();
var error = new StringBuilder();

Process process = new Process() { StartInfo = startInfo };

AutoResetEvent errorWaitHandle = new AutoResetEvent(false);
    
process.ErrorDataReceived += (sender, args) =>
{
    if (args.Data == null)
    {
        errorWaitHandle.Set();
    }
    else
    {
        error.AppendLine(args.Data);
    }
};

process.Start();

/* clear out the boot up text:
    Microsoft Windows [Version 10.0.19045.4170]
    (c) Microsoft Corporation. All rights reserved.
    empty line
*/
process.StandardOutput.ReadLine();
process.StandardOutput.ReadLine();
process.StandardOutput.ReadLine();

// if navigation to a directory is needed
if (!string.IsNullOrEmpty(navCmd1))
{
    process.StandardInput.WriteLine(navCmd1);
    process.StandardOutput.ReadLine(); // cd\
    process.StandardOutput.ReadLine(); // space
}
if (!string.IsNullOrEmpty(navCmd2))
{
    process.StandardInput.WriteLine(navCmd2);
    process.StandardOutput.ReadLine(); // cd "Program Files\PostgreSQL\15\bin"
    process.StandardOutput.ReadLine(); // space
}

// now write the actual command
process.StandardInput.WriteLine(cmd);
response.AppendLine(process.StandardOutput.ReadLine());

errorWaitHandle.WaitOne(1000);
process.StandardInput.WriteLine("test");

// read the input
response.AppendLine(process.StandardOutput.ReadLine());
// get the output we actually want
response.AppendLine(process.StandardOutput.ReadLine());
// get any errors that arrived
process.BeginErrorReadLine();

// give a second for errors to read if any (freezes if none otherwise)
errorWaitHandle.WaitOne(1000);
    
// close the process
process.Close();

return new Tuple<string, string>(response.ToString(), error.ToString());}

方法1：使用PGPASSWORD环境变量（推荐） #

PostgreSQL命令行工具支持通过PGPASSWORD环境变量传递密码，无需交互输入，这是官方推荐的无交互认证方式：

static Tuple<string, string> RunCmdLine(string cmd, string navCmd1 = "", string navCmd2 = ""){
var startInfo = new ProcessStartInfo()
{
    WindowStyle = ProcessWindowStyle.Normal,
    WorkingDirectory = @"C:\",
    FileName = "cmd.exe",
    UseShellExecute = false,
    RedirectStandardInput = true,
    RedirectStandardOutput = true,
    RedirectStandardError = true
};

// 设置PostgreSQL密码环境变量
startInfo.EnvironmentVariables["PGPASSWORD"] = "test";

var response = new StringBuilder();
var error = new StringBuilder();

Process process = new Process() { StartInfo = startInfo };

AutoResetEvent errorWaitHandle = new AutoResetEvent(false);
    
process.ErrorDataReceived += (sender, args) =>
{
    if (args.Data == null)
    {
        errorWaitHandle.Set();
    }
    else
    {
        error.AppendLine(args.Data);
    }
};

process.Start();

// 清除启动文本
process.StandardOutput.ReadLine();
process.StandardOutput.ReadLine();
process.StandardOutput.ReadLine();

// 目录导航
if (!string.IsNullOrEmpty(navCmd1))
{
    process.StandardInput.WriteLine(navCmd1);
    process.StandardOutput.ReadLine();
    process.StandardOutput.ReadLine();
}
if (!string.IsNullOrEmpty(navCmd2))
{
    process.StandardInput.WriteLine(navCmd2);
    process.StandardOutput.ReadLine();
    process.StandardOutput.ReadLine();
}

// 执行创建命令
process.StandardInput.WriteLine(cmd);

// 读取全部输出
string outputLine;
while ((outputLine = process.StandardOutput.ReadLine()) != null)
{
    response.AppendLine(outputLine);
}

process.BeginErrorReadLine();
errorWaitHandle.WaitOne(5000); // 延长等待时间确保错误输出读取完成
    
process.Close();

return new Tuple<string, string>(response.ToString(), error.ToString());
}

方法2：直接调用createdb.exe而非cmd.exe #

绕开cmd.exe中间层，直接启动createdb.exe，减少输出同步问题，同时设置环境变量：

static Tuple<string, string> CreatePostgresDb(string dbName, string password)
{
    var startInfo = new ProcessStartInfo()
    {
        FileName = @"C:\Program Files\PostgreSQL\15\bin\createdb.exe",
        Arguments = $"-U postgres {dbName}",
        UseShellExecute = false,
        RedirectStandardOutput = true,
        RedirectStandardError = true,
        WindowStyle = ProcessWindowStyle.Hidden // 可选，隐藏命令行窗口
    };

    startInfo.EnvironmentVariables["PGPASSWORD"] = password;

    var response = new StringBuilder();
    var error = new StringBuilder();

    using (var process = Process.Start(startInfo))
    {
        response.Append(process.StandardOutput.ReadToEnd());
        error.Append(process.StandardError.ReadToEnd());
        process.WaitForExit();
    }

    return new Tuple<string, string>(response.ToString(), error.ToString());
}

方法3：修正原代码的输入流同步问题（不推荐，易出错） #

原代码的核心问题是发送密码的时机不对，且依赖固定次数的ReadLine()容易同步失败。如果一定要用输入流发送密码，需要检测到密码提示符后再发送：

static Tuple<string, string> RunCmdLine(string cmd, string navCmd1 = "", string navCmd2 = ""){
var startInfo = new ProcessStartInfo()
{
    WindowStyle = ProcessWindowStyle.Normal,
    WorkingDirectory = @"C:\",
    FileName = "cmd.exe",
    UseShellExecute = false,
    RedirectStandardInput = true,
    RedirectStandardOutput = true,
    RedirectStandardError = true
};

var response = new StringBuilder();
var error = new StringBuilder();

Process process = new Process() { StartInfo = startInfo };

// 异步读取输出，检测到密码提示符时自动发送密码
process.OutputDataReceived += (sender, args) => 
{
    if (!string.IsNullOrEmpty(args.Data))
    {
        response.AppendLine(args.Data);
        if (args.Data.Contains("Password:"))
        {
            process.StandardInput.WriteLine("test");
        }
    }
};

process.ErrorDataReceived += (sender, args) =>
{
    if (!string.IsNullOrEmpty(args.Data))
    {
        error.AppendLine(args.Data);
    }
};

process.Start();

process.BeginOutputReadLine();
process.BeginErrorReadLine();

// 目录导航
if (!string.IsNullOrEmpty(navCmd1))
{
    process.StandardInput.WriteLine(navCmd1);
}
if (!string.IsNullOrEmpty(navCmd2))
{
    process.StandardInput.WriteLine(navCmd2);
}

// 执行创建命令
process.StandardInput.WriteLine(cmd);

process.WaitForExit();
process.Close();

return new Tuple<string, string>(response.ToString(), error.ToString());
}

关键提示 #

• 方法1和2是最可靠的无交互实现方式，避免了模拟输入流的同步风险。
• 不要在代码中硬编码密码，建议从配置文件或安全存储（如Windows凭据管理器）中读取。

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