Connecting your database with PiSaas

There are two key ways of connecting your data with a PiSaas deployment that are dependent on the how your database is hosted.

IPs to whitelist

The following list is our current availability for PiSaas instances, not where you’re deploying your Bastion host. if you’re unsure as to which region your PiSaas instance is deployed, please contact your CSM.

All traffic originating from PiSaas will originate from these IP addresses. Please ensure connectivity is enabled to and from these.

Database in a Publicly Accessible Subnet

If your database is hosted in a publicly accessible subnet, you can whitelist our PiSaas IPv4 address directly on the database.
With AWS, this is adding this IP address to the inbound section on your security groups. Also ensure any NACLs present on the subnet allow inbound and outbound traffic to this address.
Working with Azure, add this IP address to the inbound firewall settings on the connection security tab on your database.

Bastion Hosts for Private Subnets

It’s possible to connect to your database using a publicly available bastion as a jump host.
The bastion host needs to be able to connect both to PiSaas and to your database, allowing any traffic passing through to be controlled and monitored if necessary.
For example, in AWS use an EC2 instance deployed in a public subnet with inbound rules on the security group from our Pisaas IPv4 address, using the IP address from the table above, and typically on port 22. It must also enable outbound traffic to your database(s) on the ports which the databases use. Set the inbound security group rules on the database to accept connections from the bastion host security group, on the ports the database uses.

PI dashboard configuration

Good news is Pi natively supports connecting with bastion hosts using ssh. Please visit the 'data connections' page in your admin panel on your dashboard.

Before Starting

You will need to generate your own public and private key pair for use with this process. The private key is used when configuring the SSH connection from the Pi dashboard ‘data connections’. The public key must be added to the authorised keys on the bastion host.

AWS example:

Creating your Bastion host

• Sign into your AWS account.

• Create an EC2 instance (see the chart below for correct sizing of bastion instances). Create with a default ssh key for this instance. This instance must be in a public subnet and have a public IP address.

• Specify appropriate security groups or create a security group for the bastion host.

• Create an inbound rule on your bastion security group that opens port 22 to the IP address given by Panintelligence (found in the table above).

• Create an inbound rule on your bastion security group that opens port 22 to your IP address (this is only to enable you to connect to the instance and configure it in the next steps).

• Create an outbound rule on your bastion security group that opens your database port (e.g. 5432 for Postgres) to your database security group.

• Create an inbound rule on your database security group that opens your database port (e.g. 5432 for Postgres) from your new bastion security group.

• Log into to your new EC2 host using SSH and the default private key for this instance. The AWS console can provide you with the necessary details (endpoint etc) and steps to do this.

• create a new user sudo useradd panintelligence

• switch to your new user sudo su - panintelligence

• create the ~/.ssh directory if it does not exist mkdir ~/.ssh

• change the permissions of this new directory chmod 700 ~/.ssh

• create a file called authorized_keys in the ~/.ssh directory touch ~/.ssh/authorized_keys

• change the permissions of this new file chmod 600 ~/.ssh/authorized_keys

• Create a new key pair, specific to the panintelligence user. This can be done locally or managed as AWS key pairs

• Return to the instance and edit the authorized_keys file that you created. Copy the contents of the panintelligence public key.

• Keep the private key securely to configure later in the dashboard ‘data connections'

additional steps

• Consider hardening the OS, which refers to increasing the security that has been provided by the OS.

Bastion Host Sizing and Connection Limits

Each database connection made through the SSH tunnel consumes resources on the bastion host, including network sockets, file descriptors, memory and CPU.

A single PiSaaS ‘data connection’ may maintain multiple pooled connections for performance.

For this reason, the number of PiSaaS data connections configured against a bastion host can be proportional to, but not fully representative of, the total number of connections being maintained by the host.

Additionally, you must consider if any other users or processes are using the Bastion host that might further use up resources and affect performance.

Recommended Connection Limits

There is no fixed maximum number of database connections that can be passed through a bastion host. The availability depends on the instance size, operating system configuration, SSH configuration, database workload and the number of concurrent database connections created by PiSaaS

As a general starting point for a dedicated PiSaS bastion host:

These figures should be treated as a sizing guidance rather than hard limits. The appropriate capacity should be validated against your workload.

Connection Pooling

When sizing a bastion host, consider the total number of concurrent database connections rather than simply the number of data connections configured within PiSaaS

For example, if five PiSaaS data connections each maintain a pool of 30 database connections, the bastion host may need to handle approximated:

5x30 = 150 concurrent database connections

Additional SSH, administrative and operating system connections should also be allowed for when determining capacity.

The destination database must also be configured to support and monitored for the resulting number of concurrent connections.

Operating System Limits

Linux creates network sockets as file descriptors. A bastion host therefore needs sufficient file-descriptor capacity for the expected number of SSH and forwarded database connections

Check the configured limits using:

ulimit -n

and:

cat /proc/sys/fs/file-max

A low per-process file-descriptor limit can prevent new connections from being establish even when the EC2 instance has sufficient CPU, memory and network capacity.

For bastion hosts handling larger number of connections, the SSH service and operating-system limits should be reviewed and increased where appropriate.

Monitoring

For production environments, we recommend monitoring the bastion host rather than relying solely on EC2 size.

Useful metrics include:

• CPU ultilisation

• Memory Utilisation

• Network Throughput

• Established TCP connections

• SSH connections

• Open file descriptors

• Connection failures

• Database connection count

Capacity Planning

When planning a deployment, estimate bastion requirements using:

Expected concurrent connections = number of PiSaaS data connections × expected connection pool size

Allow additional capacity for connection spikes, administrative SSH sessions and operating-system overhead.

We recommend maintaining sufficient headroom rather than operating a bastion host close to its maximum observed capacity. For production environments, a target of less than approximately 70% of the tested connection capacity provides room for temporary connection spikes and workload growth.

Where the expected workload is uncertain, start with a modest instance size, enable monitoring and resize or distribute the workload as connection demand becomes known.

Other considerations:

If horizontal scaling is enabled on your PiSaaS environment, this could double the connection count to your bastion. Please alert support@panintelligence.com if you’re using bastion hosts so the team is aware.