Setting up RDS and Aurora PostgreSQL 9.x and 10.x for Clumio

Description

Clumio backup service for RDS supports DB Engines PostgreSQL 9.4 and higher, as well as Aurora PostgreSQL 9.6 and higher. However, to support PostgreSQL 9.x or 10.x, customers must ensure that the master database user has “select” access to all the subordinate databases.

This is a manual process in RDS and Aurora as a result of the AWS implementation of PostgreSQL as a managed service.

Clumio provides manual steps as well as an automated program to detect if the customer’s RDS/Aurora instance is affected by testing master user's “select” access permission on each database/table on a given instance. This can also be used for correcting the master user permissions if the user provides the credentials of the subordinate databases. This is a one-time operation for a given DB instance.

This may need to be done for every new 9.x or 10.x instance customer is adding to their AWS account.

Note: AWS has fixed this problem in RDS PostgreSQL 11.x and Aurora PostgreSQL 11.x, as a result the steps described in this article are not required for those versions.

Purpose

This document describes steps for the manual and automated tasks required for the following operations:

• Identify the DB and tables with restricted permissions to the master user for performing granular backups.
• Configure the correct master user permissions for PostgreSQL 9.x and 10.x user DBs and tables with restricted permissions.
• Configure "template1" DB to setup correct master user permissions for future user DBs created in the DB instance environment.

This will allow Clumio backup service to perform granular backups for all existing/new DBs and linked tables by assuming the role of the master user without any permission issues. 

Support Environments

• Amazon RDS PostgreSQL 9.x and 10.x

Prerequisites

The user configuring the master user permissions must have the following: 

• RDS Cluster/Instance joining details (Endpoint, Port, etc...)
• Master user credentials
• Table owner user credentials

Resolution

Manual Workflow

The steps required to check and configure the correct master user permissions to query the tables of a user database can be performed by connecting to the database instance through pgCLI or using a tool like pgAdmin.  

Steps to identify the DB tables with restricted master user permissions:

1. Fetch the master user login credentials for each RDS cluster/instance running Postgres 9.x or 10.x.
2. Log into the DB instance with the master user through the pgCLI or pgAdmin tool using relevant endpoint and port details.

pgCLI Login Command: psql --pset=pager=on -h ‘hostname’ -p port -U ‘master_username‘ -d postgres -W

(enter master user password on prompt)

3. List all the databases in the instance using the command ' \l '.

The list of databases will include "rdsadmin", "template0" and "template1" system databases which will not participate in the granular backup and can be ignored for the following steps. 

The remaining databases are user databases and the tables inside them will be backed up if the master user from Step1 has required permission to query all the tables.

4. To validate the master user permissions for each database, connect to the user database from the list in Step3 as the master user with the following command " \c <DB_name> ".
5. Check if your master user has permission to query all tables in the user database. This can be validated by using the following PL/PgSQL to get one empty row for each table.

DO  
$$  
DECLARE  
  t record;  
BEGIN  
  FOR t IN   
  SELECT schemaname, tablename  
  FROM pg_catalog.pg_tables  
  where schemaname NOT LIKE 'pg_'||'%' and schemaname != 'information_schema'  
  LOOP  
    EXECUTE 'SELECT '''' FROM '  
    || quote_ident(t.schemaname) || '.'  
    || quote_ident(t.tablename) limit 1;  
  END LOOP;  
END;  
$$ LANGUAGE plpgsql;

• If step 5 does not result in any permission error, then this user database is all good for performing granular backups using the master user credentials
• If step 5 returns a permission error for any of the tables in the selected user database as shown in the image attached below, the master user permissions have to be configured correctly for that table for the Clumio backup service to successfully perform granular backups.

Steps to configure master user permissions for performing granular backups for all tables of a user database:

1. Log in to the user DB with master user credentials for which the user wants to configure the correct master permissions by using the command " \c <DB_name> ".
2. Run the following query to show all the user schemas and tables in the connected user database.

select schemaname, tablename, tableowner from pg\_tables where schemaname NOT LIKE 'pg\_'||'%' and schemaname != 'information\_schema';

From the results of the query mentioned above, you will see the details of the table owners. Some tables will be owned by master user and other tables can have different owners. All the other owners must grant "master user" select privilege on tables they own.

3. To grant the necessary select permission to the master user, open a second terminal, or use the pgAdmin tool to connect to the user DB as any table-owner user name as captured in step 3 by using the command "\c <DB_name> <tableowner_user_name>".
4. Run the following PL/PgSQL to grant the master user permissions to query all the tables owned by the "table owner user".

Replace the <master_user> with the name of your database master user and <table_owner_user> with the name of logged-in table owner user.

DO  
$$  
DECLARE  
  t record;  
BEGIN  
FOR t IN  
  SELECT schemaname, tablename  
  FROM pg_catalog.pg_tables  
  WHERE tableowner='*<table\_owner\_user>'*  
  LOOP  
    EXECUTE format('GRANT SELECT ON TABLE %I.%I TO *<master\_user>*;',t.schemaname, t.tablename);  
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO *<master\_user>*;',t.schemaname);  
    EXECUTE format('Alter default privileges in schema %I grant select on tables to *<master\_user>*;', t.schemaname);  
  END LOOP;  
END;  
$$ LANGUAGE plpgsql

• After repeating step 4 for every table owner identified in step 2, the master user will be able to query all the tables in the selected user database to perform granular backups by transparently assuming the role of the master user.

Steps to configure master user permissions for user databases created in the future:

Each RDS database instance will have a special database named "template1" by default which can be used as a template to set up new databases.

1. Login to the "template1" DB as the master user by using the command " \c <DB_name> "
2. Once connected, run the grant command mentioned below.

grant select on all tables in schema public to <master_user>;

The steps mentioned above will modify the "template1" DB so that the master user automatically has access to tables created with the "public" schema.

Automated Workflow

Clumio has worked on automating the manual steps mentioned above.  We provide binaries for multiple platforms (Linux, macOS, and Windows) for the same that can be downloaded by customers.

• macOS Binary File
• Linux Binary File
• Windows Binary File

This automated process takes away the pain of running the manual steps against multiple DBs in a single DB instance to detect and correct the master user permissions issue.

Steps to detect and correct master user permissions with automated workflow:

1. Download the ZIP file from the source mentioned above.
2. Extract the ZIP file to a location of your preference. This folder will contain the binaries for multiple platforms along with the source code. Customers can directly select and execute the binary suitable for their platform or can compile the go source code.

Refer to the "README.md"from the downloaded bundle for additional details and some quick examples. 

Contact [email protected] in case of any clarifications or questions.