Golang使用env文件时database/sql数据库连接失败问题排查

The Problem #

Originally, your Go code with hardcoded Postgres connection parameters worked perfectly. But when you switched to using os.Getenv("DATABASE_PWD") to fetch the password from environment variables, you hit this error:

panic: pq: password authentication failed for user "bond"

Common Causes & Fixes #

Let’s walk through the most likely issues and how to fix them, ordered by how often they pop up:

• Environment variable isn’t loaded properly
  This is the #1 culprit. If you set the variable in your terminal but didn’t run your Go program in the same session, or you’re using a .env file without a loader, os.Getenv will return an empty string—leading to an incorrect password.

  • Fixes:
    1. If setting the variable temporarily in your terminal, run your program right after in the same session:

       export DATABASE_PWD="password"
       go run your-file.go
       

    2. If using a .env file, you need a library like github.com/joho/godotenv to load it (Go’s standard library doesn’t auto-read .env files). Example:

       import (
           "log"
           "github.com/joho/godotenv"
       )
       
       func main() {
           // Load variables from .env
           err := godotenv.Load()
           if err != nil {
               log.Fatalf("Failed to load .env file: %v", err)
           }
           // Now fetch the password
           password := os.Getenv("DATABASE_PWD")
           // Rest of your connection code...
       }
       

• Typos in the environment variable name
  Double-check that the variable name in your .env file matches exactly what you’re passing to os.Getenv. A tiny typo (like DATABASE_PASSWORD instead of DATABASE_PWD, or mixed case) will result in an empty string being returned.

  • Fix: Cross-verify the variable name in both your .env file and your Go code—they need to be identical.

• Special characters in the password aren’t handled correctly
  If your password has special characters like $, @, spaces, or quotes, setting it directly in the terminal or .env file can cause parsing issues, leading to an incorrect password being passed to Postgres.

  • Fixes:
    1. When setting the variable in the terminal, wrap the password in quotes:

       export DATABASE_PWD="my$tr0ngP@ssword"
       

    2. In your .env file, also wrap the password in quotes to preserve special characters:

       DATABASE_PWD="my$tr0ngP@ssword"
       

• Existing environment variable is overriding your value
  It’s possible a system-wide or session-wide variable with the same name already exists, and os.Getenv is pulling that instead of your intended value.

  • Fix: Add a debug print to confirm what password your code is actually fetching (remove this before deploying!):

    func main() {
        password := os.Getenv("DATABASE_PWD")
        fmt.Printf("Retrieved password: '%s'\n", password) // Debug check
        // Rest of your connection logic...
    }
    

    If this prints an empty string or the wrong value, you’ll know where to focus your troubleshooting.

Quick Validation Step #

Before diving into fixes, always add that debug print to confirm the password variable’s value. This instantly tells you if the issue is with fetching the variable or something else.

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