PostgreSQL Guide
Learn more about the benefits of Satori for PostgreSQL and Schedule a demo meeting
It only takes a few minutes to get started with Satori. What you need is:
- Access to Satori's management console.
- The hostname of your PostgreSQL data store, for example:
abc123.cluster-cajcpwqzmvbb.us-east-1.rds.amazonaws.com
.
Adding a PostgreSQL Data Store to Satori
- Login to Satori's management console at https://app.satoricyber.com.
- In the Data Stores view, click the plus button to add a new data store.
- Select the PostgreSQL option.
- Enter an informative name for the data store, for example: Sales Data Warehouse.
- Enter the hostname of your PostgreSQL database, for example:
abc123.cluster-cajcpwqzmvbb.us-east-1.rds.amazonaws.com
. - Choose the public cloud provider and region for the Satori Data Access Controller.
- Click Create.
- Finally, you will be redirected to the Data Stores list view.
Connect to PostgreSQL Database via Satori
To connect to PostgreSQL database using Satori use the Satori hostname that was generated by the management console which can be found under Satori Hostname in the data store settings view, for example: abc123-cluster-cajcpwqzmvbb.us-east-1.a.p0.satoricyber.net
.
TLS SNI Required
To connect to PostgreSQL via Satori, the client application needs to support the Server Name Identification (SNI) extension of the Transport Level Security (TLS) protocol. For clients that do not support the SNI extension, Satori can enable a custom, dedicated port which overcomes the problem. The custom port number can be found in the data store setting in the management console.
Password Authentication for PostgreSQL
SCRAM-SHA-256 Authentication
PostgreSQL may use scram-sha-256
for password-based authentication method, to connect via Satori. Channel binding should be disabled by setting the channel_binding=disable
connection parameter. To learn more about SCRAM with channel binding read about it here.
MD5 Authentication
PostgreSQL version 14.0 has introduced scram-sha-256
as the default authentication method. Reverting to MD5 authentication, requires setting the following database flag on your PostgreSQL instance: password_encryption=MD5
, authentication for new users will be MD5 based.
Connecting with the JDBC Driver
To connect with the JDBC driver, override or add the host
parameter in the JDBC URL. For example:
jdbc:postgresql://abc123-cluster-cajcpwqzmvbb.us-east-1.a.p0.satoricyber.net/?user=john@example.com&...
Connecting with the PSQL Command Line Utility
TLS SNI not supported by PSQL Please use version 13.1 or higher of PSQL. In prior versions, PSQL uses the TLS1.0 protocol by default which is considered insecure and is not supported by Satori.
To connect to PostgreSQL using PSQL change the host
parameter to the Satori generated hostname and use the custom dedicated port for your data store. For example:
psql -h abc123-cluster-cajcpwqzmvbb.us-east-1.a.p0.satoricyber.net -p 12340 -U john example
Connecting with Looker
Looker uses the JDBC driver to connect to PostgreSQL. Override the Remote Host:Port
parameter with the Satori hostname. For example: abc123.ci3gimsawmqt.us-east-2.a.p0.satoricyber.net:5439
.
Connecting with Google Data Studio
Google Data Studio requires the Satori server certificate of the Data Access Controller to connect to PostgreSQL. To obtain the certificate contact Satori support team.
- Override the Hostname or IP field with the Satori hostname. For example:
abc123.ci3gimsawmqt.us-east-2.a.p0.satoricyber.net
. - Check the Enable SSL checkbox.
- Upload the Satori server certificate.
Authenticating via AWS IAM
AWS RDS supports authentication using short lived IAM tokens as described here. When setting up your application to work with Satori, make sure not to modify the hostname used to generate the token.
e.g. in the java example below the hostname parameter RDS_INSTANCE_HOSTNAME should not be modified to the Satori hostname
private static String generateAuthToken() {
BasicAWSCredentials awsCredentials = new BasicAWSCredentials(AWS_ACCESS_KEY, AWS_SECRET_KEY);
RdsIamAuthTokenGenerator generator = RdsIamAuthTokenGenerator.builder()
.credentials(new AWSStaticCredentialsProvider(awsCredentials)).region(REGION_NAME).build();
return generator.getAuthToken(GetIamAuthTokenRequest.builder()
.hostname(RDS_INSTANCE_HOSTNAME).port(RDS_INSTANCE_PORT).userName(DB_USER).build());
}
Authenticating via GCP IAM
GCP Cloud SQL supports authentication using short lived IAM tokens as described here. GCP expects the password connection parameter to contain the IAM token. For example:
PGPASSWORD=$(gcloud auth print-access-token) psql -h abc123-cluster-cajcpwqzmvbb.us-east-1.a.p0.satoricyber.net -p 12340 -U john.smith@example.com example
Network Policy Settings
Satori provides you with the ability to define your network security policy for your data store.
Simply, specify which IP addresses and subnet masks should be allowed access to the Data Store and which IP addresses and subnet masks should be blocked. Note that by default, Satori allows all IP address ranges if you leave the form empty.
Example 1
To allow all IP addresses - Leave the form empty.
Example 2
To block specific IP addresses - Add them to the blocked IP address list.
Example 3
To only allow access to specific IP addresses - Add them to the allow list. Note: Access from all other IP addresses ware blocked.
Example 4
To allow access from a specific IP address range while blocking parts of the range - Add the IP range to the list of allowed IP addresses and add the IP address that you wish to block to the Block list.