Cloud SQL Postgre connection problem with credentials

[datasavo]

I have been stuck with this problem for two days now and starting to get a bit frustrated. So basically I have set up the cloud sql proxy using command .

.\cloud-sql-proxy --address 0.0.0.0 --port 5432 myproject:region:instance

 This is done in another powershell terminal. Then I try to connect my django application to this postgre database to make migrations. My django settings for database looks like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'new': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': DB_NAME,
        'USER': DB_SUPER,
        'PASSWORD': DB_SUPER_PW,
        'HOST': '127.0.0.1',
        'PORT': DB_PORT,
            }
}

And the command to migrate:

python manage.py migrate --database=new

I have double triple checked the user name and password, I have tried both the super user and the database user and neither work. I have reseted passwords and still not working, restarted the instance and what so ever. I have also hard coded the credentials at some point but after all this I am still getting:

FATAL: password authentication failed for user "*********"

I would really appreciate help, thanks.

[ms4446]

Here are a few more things you can check to help resolve the "password authentication failed" error when connecting to your Cloud SQL PostgreSQL instance via the Cloud SQL Auth Proxy:

. Verify Credentials

• Cloud SQL Console:
  • Log in to the Google Cloud Console.
  • Navigate to your Cloud SQL instance.
  • Under the "Users" tab, verify the username and password match your Django settings exactly.
• Temporary Hardcoding: Temporarily hardcode the credentials in your Django settings to rule out issues with environment variables.

DATABASES = {
    # ... (other database settings)
    'new': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'your_db_name',
        'USER': 'your_user',
        'PASSWORD': 'your_password',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

2. Check Network Connectivity

• Proxy Status:
  • Ensure the Cloud SQL Auth Proxy is running correctly and showing no errors.
  • Look for messages indicating a successful connection to your Cloud SQL instance.
• Telnet/NC Test:
  • telnet 127.0.0.1 5432 (if telnet is available)
  • nc -zv 127.0.0.1 5432 (if telnet is not available)
  • A successful connection should be displayed if the proxy is accessible.
• Firewall Rules: Ensure no firewall rules block the connection from your application to the proxy's port (5432).

3. Validate Instance Connection Name

• Cloud SQL Console:
  • Double-check the instance connection name (e.g., myproject:region:instance) under the "Overview" tab.
  • Copy and paste it directly into your proxy command to avoid typos.

4. Review Authentication Method

• Cloud SQL Console:
  • Go to the "Connections" tab.
  • Confirm that "Password authentication" is enabled. Adjust Django settings if using a different method (e.g., IAM).

Additional Checks and Tips

• Restart Services: Restart the Cloud SQL Auth Proxy and your Django application after making changes.
• Check Logs: Examine Cloud SQL logs (in the console) and Cloud SQL Auth Proxy logs for errors.
• Direct Connection Test (Advanced): Try connecting directly to the Cloud SQL instance's public IP (if enabled) using psql.

psql -h <PUBLIC_IP_ADDRESS> -U your_user -d your_db_name -p 5432 

Secure Configuration with Environment Variables

1. Install python-dotenv: pip install python-dotenv
2. Create .env file: Add credentials to a .env file in your project root.
3. Update Django Settings:

import os
from dotenv import load_dotenv

load_dotenv()

DATABASES = {
    # ... (other database settings)
    'new': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': os.getenv('DB_NAME'),
        'USER': os.getenv('DB_USER'),
        'PASSWORD': os.getenv('DB_PASSWORD'),
        'HOST': os.getenv('DB_HOST'),
        'PORT': os.getenv('DB_PORT'),
    }
}