Quick Installation Guide

For Google Cloud Marketplace Customers

PostgreSQL Armored by SG™

**A Shielded PostgreSQL Server With Transparent Encryption and Cloud Backup **

Thank you for deploying the “PostgreSQL Armored by SG” instance. You are in good company. Customers all around the world have enjoyed the benefits of our solutions since 2015. We have major installations in Europe, Asia, and the United States. Businesses use our solutions to protect their critical data and to achieve regulatory compliance. We also provide a variety of other services related to secure data transfer, storage, backup, and migration for data owners. Please contact us by sending an email to [email protected] if you have any questions. We would love to hear from you!

Introduction:

PostgreSQL Armored by SG is a self-protecting PostgreSQL server instance that empowers users to control their data encryption key. This means that the encrypted PostgreSQL data can be moved across various cloud platforms or even to your data center without requiring a new key management system or data decryption.

Our solution is fully compliant with Google's Assured Workload environments, including FedRamp Medium and HIPAA. PostgreSQL Armored by SG offers advanced security features such as transparent encryption of PostgreSQL data, reduced attack surface, access controls, tamper-resistant logs, hardened instances, virtual instance security, and integrity monitoring. In addition, you can take advantage of secure backup features that include encrypted backups, cost-effective archival, flexible scheduling, and easy restoration.

By choosing PostgreSQL Armored by SG, you can enjoy the peace of mind that comes with having complete control over your data encryption key while benefiting from a highly secure, portable, and flexible PostgreSQL server instance.

Prerequisites:

• An active Google Cloud Platform account: To install PostgreSQL Armored by SG on Google Cloud, you must have an active Google Cloud Platform account. If you don't have an account yet, you can sign up for a free trial at https://cloud.google.com/free/. Once you have an account, you'll need to create a new project to deploy the PostgreSQL Armored by SG solution.
• Proper privileges to install a Google Marketplace virtual machine instance: To install PostgreSQL Armored by SG from the Google Cloud Marketplace, you must have proper privileges to create and manage virtual machine instances in Google Cloud. This typically requires the "Compute Instance Admin" or "Project Editor" role in Google Cloud IAM.
• Privileges to manage billing accounts. This typically requires the “Billing Account User” and “Billing Account Viewer” or “Billing Account Administrator”.
• Additional permissions to create and manage storage resources: If you plan to use Google Cloud storage to store your encrypted backups, you will need to have additional permissions to create and manage storage resources. This typically requires the "Storage Admin" or "Project Editor" role in Google Cloud IAM.
• PostgreSQL Armored by SG includes a valid license as part of the installation process from the Google Marketplace. This means that when deploying the solution through the Google Marketplace, there is no need to purchase a separate license. It's important to note that this automatic licensing feature applies only to machines deployed from the Google Marketplace.

Section 1: Accessing the PostgreSQL Armored by SG solution in Google Cloud Marketplace:

Goal - At the end of this step, your secure PostgreSQL server instance will be running on Google Cloud Engine.

Once you have met the prerequisites, you can proceed with accessing and deploying PostgreSQL Armored by SG on Google Cloud. Follow these steps:

1. Log in to the Google Cloud Console at https://console.cloud.google.com/.
2. Click on the "Marketplace" button on the left sidebar.
3. In the search bar at the top of the page, search for "Server General."
4. Select "PostgreSQL Armored by SG" from the list of search results.
5. On the PostgreSQL Armored by SG product page, click the "Launch" button.
6. Configure the virtual machine template using the provided information:
   • Deployment name: Enter a unique identifier for the deployment.
   • Zone: Select the geographic region of the data center hosting the deployment.
   • Machine type: Select the CPU and RAM types appropriate for your deployment.
   • Boot disk type: Select the disk type suitable for your deployment.
   • Boot disk size in GB: Choose the storage capacity appropriate for your deployment.
   • Additional disk size in GB: Select storage capacity, considering your data storage requirements. Note that the second disk is used to store your encrypted data sets, and it should be twice as large as your data set.

7. Click the "Deploy" button to proceed.
8. Wait for the virtual machine instance to be created and initialized. Once complete, you can access the PostgreSQL Armored by SG solution using a web browser.
9. Follow the on-screen instructions to configure your instance.

Section 2: Configure Your Instance:

Goal - At the end of this step, your PostgreSQL Armored by SG instance will be fully configured, and your PostgreSQL data will be encrypted.

1. Access your instance by clicking on the link provided on the Deployment Manager page or opening a web browser and typing in the IP address of your machine: https://<<IP address of your machine>>/.
2. Ignore any invalid SSL certificate warning and proceed.
3. On the License Agreement page, scroll down and accept the terms and conditions to proceed with the configuration.
4. Your machine will run a connectivity test to ensure it can access the licensing server, key lockers, and logging servers. Click "process" if the test passes, or else check your firewall settings and ensure you are not restricting outbound TCP/IP traffic.

5. To obtain a new license, please verify your email address. A security code will be sent to your registered email address. Enter the security code to activate your instance license.

6. Configure the "sgadmin" user by assigning a password. This user is a system user, and all commands are issued in the context of this user. Remember the password as you'll need it to log into the web management console of your machine.

7. Establish the Security Officer (SO), choose an alpha-numeric string that is 16 characters or longer, designated as the Security Officer's Master Key (SMK). It's imperative to record this passcode as it cannot be recovered in case of loss.

8. To set up the Data Administrator (DA), choose an alpha-numeric passcode of at least 16 characters, referred to as the Data Administrator's Master Key (DMK). Initializing the DA requires entering the SMK, exclusively manageable by the SO for adding or removing DAs. Document the DMK securely, as it's essential for overseeing all operations concerning your safeguarded data sets.

Section 3: Configure Backup Functionality:

Goal - At the end of this step, backup functionality will be enabled and configured.

1. Go to the 'Configure Cloud Backup' section, then choose 'Get verification code.' This step will prompt a new window, where you'll need to authenticate with your Google account.

2. Note: If a pop-up blocker is enabled, please disable it or add an exception to ensure the code is visible.

3. After authentication, your verification code will be displayed. Copy it by clicking the 'Copy' button in the provided interface.

4. Return to the previous page and input this code into the designated "Google verification code" field. Finally, click on "Enter" to proceed

5. After Google identity verification, the next step is selecting the project.

6. Following the project selection, the next step is enabling billing. Choose the appropriate billing account from the available options.

7. The next step would be to create a service account. You can achieve this by simply clicking on the "Create Service Account" button and then proceeding to grant the necessary permissions by clicking on the respective "Grant Permissions" button.

8. The next action is to create a new agent pool. Agent pools enable parallel data transfer by distributing tasks among multiple agents. This significantly improves transfer performance compared to a single agent. We use three agents. Please note that the installation process might take some time to complete.

9. At this stage, you can create a bucket to store backups. Click on the "Create" button. If you encounter an error indicating that the name is already in use, please consider using a unique name. You can achieve this by incorporating the Universal Namespace (e.g., tg-bucket-10 characters hash) or by adding a unique identifier to the end of the name. Kindly allow some time for the process to be completed after creating the bucket. Following this, you'll be able to initiate the backup creation.

10. To enable the backup functionality, Google Transfer Agent needs to be installed. The installation might take some time, but once completed, the backup process will automatically begin functioning.

11. We'll configure the PostgreSQL "postgres" user for you, allowing you to log in without a password. This provides initial access, but we strongly recommend that you set your own password for enhanced security.

12. Once you complete these steps, a new security policy will take effect, automatically encrypting your PostgreSQL data and starting your server. Only the "sgadmin" and "postgres" users will have access to clear text data, while you retain control over the encryption key. Rest assured, your application will continue to access data seamlessly as we leverage transparent data encryption. Here's what your dashboard will look like after implementation:

Section 4: Verify installation:

Goal - At the end of this step, you will be able to see that your data is encrypted, and our advanced access control rules are enforced.

Your PostgreSQL data is now encrypted and protected by advanced access controls. This means only authorized users, specifically the "allowed users," can access the unencrypted data sets. All other users, including the "root" user, will be unable to view the cleartext data.

To confirm this, let's test with the assumption you used the "Start Encrypted PostgreSQL" option from the web console to start your server.

First, we'll verify that the PostgreSQL user and the "sgadmin" user still have access to the data, even though it's encrypted.

On Linux:

Add your public SSH key to the "/home/sgadmin/.ssh/authorized_keys" file on the instance.

Use the following command to log in as the "sgadmin" user:

$ ssh -i <path to google compute engine key stored on your computer> sgadmin@<IP address of your server>

You can also use the “gcloud” command to log into your machine. It will look like this:

gcloud compute --project "project-name" ssh --zone "us-central1-f" "sgadmin@name-of-your-vm"

Once logged in, we will attempt to access the data and verify successful decryption by authorized users.

Query any table of importance to you - here we’ll establish a PostgreSQL connection and execute a query on the “user” table (which holds database credentials). You should get similar results:

$ sudo -u postgres psql -d postgres -c "SELECT usename FROM pg_user;"

Enter the password when prompted. The output should look like this:

The above test proves that the “postgres” user is still able to see the data in cleartext.

Now let’s see if the “sgadmin” user can see the data in cleartext at the OS layer. Start a new terminal session and log in as the “sgadmin” user using the “gcloud” command that should look like this (or you can SSH into the machine using the Google Compute Engine key stored on your machine):

gcloud compute --project "your-project-name" ssh --zone "us-west1-b" "sgadmin@name-of-your-vm"

The “sgadmin” user should be able to see the PostgreSQL data per our security policy above.

$ sudo i 
# cd /var/lib/postgresql/10/main/base 
# ls -lt 
# strings <table_name>

You can copy the above commands and paste them into your SSH terminal to perform the tests.

The last command in the series above will show you the plain text characters scraped from within the file.

Please note that the file you are looking at is encrypted, but the “sgadmin” user is still able to see the data in cleartext.

You can see your encrypted files:

$ sudo ls -lt /vault/serverg/security_policy_tg2

Here you will find the encrypted version of the PostgreSQL files, previously stored in /var/lib/postgresql/10/main/base.

Now, if you want to see what unauthorized users will see, then you should log out again. You must log out and log back in as any other system user who is part of the group “sudo.” Elevate your privileges and then try to access /var/lib/postgresql/10/main/base. You will notice that even though you are the “root” user, you are unable to access the protected files. The following command generates an error stating no such file exists:

# cd /var/lib/postgresql/10/main/base

This command should show an empty directory like this:

ls: cannot access '/var/lib/postgresql/10/main/base': No such file or directory

Important №1:

When you reboot your PostgreSQL Armored by SG instance, you must restart your PostgreSQL server using the dedicated "Start PostgreSQL Server" option within the "PostgreSQL Armored by SG" console. This ensures proper configuration and security protocols are applied. Here's how to restart your PostgreSQL server:

Open your web browser and navigate to the following URL:

https://<IP address of your machine>

Log in to your "PostgreSQL Armored by SG" console. Click the "Start Encrypted PostgreSQL Server" button.

Important №2:

Do not attempt to start your PostgreSQL server manually using the command line unless you are confident in the process and understand the potential risks.

Using the "Start PostgreSQL Server" option guarantees a secure and optimized startup process.

By following these steps, you can ensure your PostgreSQL server restarts correctly after a reboot and continues to operate securely within the "PostgreSQL Armored by SG" environment.

Updated on November 21, 2023