使用psycopg2连接AWS Redshift Serverless超时及密码异常问题

问题概述 #

• 使用psycopg2连接AWS Redshift Serverless时触发连接超时，调试语句hello正常输出，但psycopg2.connect()执行后无响应，无法打印Nasa
• 修改Redshift密码时系统提示成功，但实际密码未更新

相关代码 #

AWSRedshift.py #

#from warehouseInterface import Warehouse

import boto3
from dotenv import load_dotenv
import os
import psycopg2
#any file in python is module

class AWSWarehouse():
    def connect(self, config):
        #redshift_client = boto3.client('redshift', aws_access_key_id=config['AKIA6OOMDUKKNSXXXX'],
                            # aws_secret_access_key=config['OEur3QbGL2vhRQyG4Rlicu/pvhFBIYAzFjjJnKj/'],
                            # region_name=config['ap-south-1'])

        # redshift Connection details
        redshift_endpoint = 'default-workgroup.xxxx.ap-south-1.redshift-serverless.amazonaws.com'
        redshift_port = '5439'
        redshift_database = 'dev'
        redshift_user = 'admin'
        redshift_password = 'password'
        
        print("hello")

        # setting up connection with redshift
        redshift_conn = psycopg2.connect(   
                                         
            host=redshift_endpoint,
            port=redshift_port,
            database=redshift_database,
            user=redshift_user,
            password=redshift_password
        )
        
        print("Nasa")
        
        return redshift_conn
    
    
    def execute_query(self, query, conn):
        return super().execute_query(query)

main.py #

#take data from tranform and load itin redshift

from DataTransform import Asteriod_Tranform
from DataLoading.MyWareHouse import AWSRedshift

res = Asteriod_Tranform.mydic
#print(res)

name = res['name']
closeData = res['close_approach_data']
closeDate = res['close_approach_date']
#count = res['count']


# Creating an instance of the AWSWarehouse class
aws_warehouse = AWSRedshift.AWSWarehouse()
print(aws_warehouse)

# Assuming you have the necessary AWS configuration in a dictionary
config = {
    'AWS_ACCESS_KEY_ID': 'your_access_key',
    'AWS_SECRET_ACCESS_KEY': 'your_secret_key',
    'AWS_REGION': 'your_region',
    # other configuration parameters...
}



# Call the connect method
connection = aws_warehouse.connect(config)

print(connection)

create_table_query = """
CREATE TABLE your_table (
    name VARCHAR(255),
    closeData VARCHAR(255),
    closeDate DATE,
    
);
"""

# Call the execute_query method to create the table
aws_warehouse.execute_query(create_table_query, connection)

# Close the connection after creating the table
connection.close()

错误信息 #

Original exception was:
Traceback (most recent call last):
  File "main.py", line 30, in <module>
    connection = aws_warehouse.connect(config)
  File "/home/mayank/Desktop/Project/DE/NASA/DataLoading/MyWareHouse/AWSRedshift.py", line 25, in connect
    redshift_conn = psycopg2.connect(   
  File "/home/mayank/.local/lib/python3.8/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "default-workgroup.993102373524.ap-south-1.redshift-serverless.amazonaws.com" (172.31.1.60), port 5439 failed: Connection timed out
        Is the server running on that host and accepting TCP/IP connections?

解决方案 #

一、连接超时问题处理 #

1. 检查网络与安全组配置
   • Redshift Serverless的VPC安全组必须允许客户端所在IP（或IP段）访问5439端口。登录AWS控制台，找到对应Redshift Serverless工作组的VPC安全组，添加入站规则：协议TCP，端口5439，来源设为客户端公网IP。
   • 用telnet default-workgroup.xxxx.ap-south-1.redshift-serverless.amazonaws.com 5439或nc -zv <端点> 5439测试连通性，不通则排查公司防火墙、本地VPN等网络限制。
2. 验证端点与端口正确性
   • 登录AWS控制台，进入Redshift Serverless -> 工作组，确认端点地址与代码中redshift_endpoint完全匹配。
   • 确认工作组未修改默认端口5439。
3. 改用IAM身份验证（推荐）
   避免硬编码密码，用IAM角色生成临时凭证连接：

   import psycopg2
   import boto3
   
   def connect_with_iam(config):
       redshift_client = boto3.client('redshift-serverless', 
                                      aws_access_key_id=config['AWS_ACCESS_KEY_ID'],
                                      aws_secret_access_key=config['AWS_SECRET_ACCESS_KEY'],
                                      region_name=config['AWS_REGION'])
       # 生成临时凭证
       creds = redshift_client.get_credentials(db_name='dev', db_user='admin')
       conn = psycopg2.connect(
           host='default-workgroup.xxxx.ap-south-1.redshift-serverless.amazonaws.com',
           port=5439,
           database='dev',
           user=creds['DbUser'],
           password=creds['DbPassword']
       )
       return conn
   

   注意：需给IAM用户/角色添加redshift-serverless:GetCredentials权限，且Redshift用户已关联IAM角色。

二、密码不更新问题处理 #

1. 确认修改密码的正确操作
   • 控制台修改：进入工作组 -> 编辑 -> 数据库配置 -> 修改管理员密码，等待工作组状态变为“可用”后再测试。
   • CLI修改：执行aws redshift-serverless update-workgroup --workgroup-name default-workgroup --admin-user-password <新密码>，等待命令执行完成后验证。
2. 清除客户端缓存
   • 关闭所有现有Redshift连接，重启测试脚本后再尝试登录。
   • 若使用SQL客户端，清除连接缓存后重新连接。
3. 验证IAM权限
   确认执行修改操作的IAM用户拥有redshift-serverless:UpdateWorkgroup权限，否则修改操作看似成功但实际未生效。

额外代码优化建议 #

• 用dotenv加载敏感信息，避免硬编码：

  load_dotenv()
  redshift_endpoint = os.getenv('REDSHIFT_ENDPOINT')
  redshift_password = os.getenv('REDSHIFT_PASSWORD')
  

• 修复execute_query方法（原方法调用super()但未继承父类，无实际逻辑）：

  def execute_query(self, query, conn):
      with conn.cursor() as cur:
          cur.execute(query)
          conn.commit()
          return cur.fetchall() if cur.description else None
  

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