PostgreSQL Data Connection

A robust Python script for establishing secure SSH-tunneled connections to PostgreSQL databases with comprehensive database exploration capabilities.

🚀 Features

• Secure SSH Tunnel: Establishes encrypted connections through SSH tunnels
• Database Exploration: Automatically discovers and documents database structure
• Error Handling: Comprehensive error handling with detailed error messages
• Cross-Platform: Works on macOS, Linux, and Windows
• Virtual Environment Support: Compatible with Python virtual environments

📋 Prerequisites

• Python 3.7+
• SSH access to the target server
• PostgreSQL client libraries
• Valid SSH private key (PEM format)

🛠️ Installation

1. Clone the repository:

   git clone https://github.com/joudathashmi/postgres-data-connection.git
   cd postgres-data-connection

2. Create and activate virtual environment:

   python -m venv .venv
   source .venv/bin/activate  # On Windows: .venv\Scripts\activate

3. Install dependencies:

   pip install psycopg2-binary

⚙️ Configuration

Edit the connection parameters in PostgresIsnsertion.py:

# SSH & DB details
ssh_host = "your-server-ip"
ssh_user = "your-ssh-username"
ssh_pem = "path/to/your/private-key.pem"

db_host = "localhost"
db_name = "your-database-name"
db_user = "your-db-username"
db_password = "your-db-password"
db_port = 5432

🚀 Usage

Basic Usage

python PostgresIsnsertion.py

With Virtual Environment

source .venv/bin/activate
python PostgresIsnsertion.py

📊 Output

The script provides comprehensive database information:

• Connection Status: Confirms successful database connection
• Database Version: PostgreSQL version information
• Schema Discovery: Lists all available schemas
• Table Structure: Detailed table and column information
• Data Types: Column data types and nullability

Example Output

🔗 Creating SSH tunnel...
SSH tunnel established at local port 5433
Connected to PostgreSQL: ('PostgreSQL 14.18 (Ubuntu 14.18-0ubuntu0.22.04.1)...')

Schemas:
 - pg_catalog
 - public
 - information_schema

Tables in 'public' schema:
 - Opportunity
 - Company
 - MatchingOutput
 - User
 - ...

Table structures:
>> Opportunity
   id (integer) nullable=NO
   opportunity_name (text) nullable=NO
   sector (text) nullable=YES
   ...

🔧 Technical Details

SSH Tunnel Implementation

The script uses subprocess to create SSH tunnels, avoiding library compatibility issues:

ssh_cmd = [
    "ssh", "-i", ssh_pem, "-L", f"{local_port}:{db_host}:{db_port}",
    "-N", "-f", f"{ssh_user}@{ssh_host}"
]

Error Handling

Comprehensive error handling for:

• SSH connection failures
• Database connection issues
• File not found errors
• Authentication problems

Security Features

• SSH Key Authentication: Uses private key authentication
• Local Port Binding: Tunnels database traffic through local ports
• Connection Cleanup: Properly closes connections and tunnels

📁 Repository Structure

postgres-data-connection/
├── README.md                 # This file
├── PostgresIsnsertion.py     # Main connection script
├── requirements.txt          # Python dependencies
├── .gitignore               # Git ignore rules
└── examples/                # Usage examples
    └── basic_usage.py       # Basic usage example

🔒 Security Considerations

• Never commit SSH keys: Keep your private keys secure and out of version control
• Use environment variables: Consider using environment variables for sensitive data
• Network security: Ensure SSH access is properly secured
• Database permissions: Use least-privilege database accounts

🐛 Troubleshooting

Common Issues

1. SSH Connection Failed

   • Verify SSH key permissions: chmod 600 your-key.pem
   • Check SSH server accessibility
   • Verify username and hostname

2. Database Connection Failed

   • Verify database credentials
   • Check if PostgreSQL is running on the target server
   • Ensure database exists and user has access

3. Permission Denied

   • Check SSH key file permissions
   • Verify SSH user has database access
   • Ensure proper sudo permissions if needed

Debug Mode

Add debug output by modifying the script:

import logging
logging.basicConfig(level=logging.DEBUG)

🤝 Contributing

1. Fork the repository
2. Create a feature branch: git checkout -b feature-name
3. Commit changes: git commit -am 'Add feature'
4. Push to branch: git push origin feature-name
5. Submit a pull request

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🆘 Support

For support and questions:

• Create an issue in this repository
• Check the troubleshooting section
• Review the error messages for specific guidance

🔄 Version History

• v1.0.0 - Initial release with SSH tunnel support
• v1.1.0 - Added comprehensive error handling
• v1.2.0 - Virtual environment compatibility
• v1.3.0 - Enhanced database exploration features

---

Note: This script is designed for development and testing purposes. For production use, consider implementing additional security measures and connection pooling.