Requirements for database users

In Delphix Continuous Data, a Database (DB) User is required to run the linking and ingestion commands upon a source database. In PostgreSQL, this user is often referred to as a Database Role. This user’s credentials are provided to Delphix Continuous Data during the dSource linking configuration process.

During each linking, ingestion, and snapshot operation, the Database User’s credentials are used via the Staging Environment’s Operating System (OS) user. Therefore, it’s important to confirm the user will be made accessible from that host and the credentials are up to date.

The following sections describe the suggested PostgreSQL roles for each source database type and accompanying examples.

On-Prem (Standard PostgreSQL)

We recommend the following standard PostgreSQL roles for new users.

PostgreSQL Attributes and Roles	Description
REPLICATION [via CREATE]	[Best Practice] Grants the least number of privileges that cover the widest range of ingestion methods.
SUPERUSER [via CREATE]	If replication has insufficient permission or added security is not an immediate concern, then the superuser is a great alternative.
pg_read_all_data [via GRANT]	If you would rather re-use an existing user, then you can grant this permission instead.

Examples

After connecting to the PostgreSQL shell, new users can be created using the CREATE USER SQL statement. Create a user with the replication role:

CREATE USER <DATABASE_USER> WITH REPLICATION ENCRYPTED PASSWORD ‘<DATABASE__USER_PASSWORD>’;

Similarly, create a user with the superuser role, i.e. superuser:

CREATE USER <DATABASE_USER> WITH SUPERUSER ENCRYPTED PASSWORD ‘<DATABASE__USER_PASSWORD>’;

If you would rather use an existing user and grant it additional privileges, then run the following statement:

GRANT pg_read_all_data TO <DATABASE_USER>;

AWS and Azure

New users in PaaS PostgreSQL platforms are created like those on-prem systems. However, they are granted roles differently than the on-prem model. We must create a simple User first and then add the necessary permissions second.

PostgreSQL Attributes and Roles	Description
pg_read_all_data [via GRANT]	[Best Practice] Creating a new user with this permission enables the widest range of ingestion methods for AWS.
rds_superuser [via GRANT]	[AWS RDS and Aurora RDS Only] If pg_read_all_data has insufficient permissions or added security is not an immediate concern, then the rds_superuser is a great alternative.
azure_pg_admin [via GRANT]	Azure Database for PostgreSQL - Flexible Server If pg_read_all_data has insufficient permissions or added security is not an immediate concern, then the azure_pg_admin is a great alternative.

Examples

After connecting to the PostgreSQL shell, new users are created using the CREATE USER SQL statement:

CREATE USER <DATABASE_USER> ENCRYPTED PASSWORD '<DATABASE_USER_PASSWORD>';

Now, we instead must provide the specific privilege using the GRANT command.

If we want to attach the pg_read_all_data with the user which is generally required by a user who wants to execute pg_dumpall/pg_dump utilities:

GRANT pg_read_all_data TO <DATABASE_USER>;

Or, instead, to provide superuser privileges we can attach the rds_superuser or azure_pg_admin role:

AWS RDS

GRANT rds_superuser TO <DATABASE_USER>;

Azure

GRANT azure_pg_admin TO <DATABASE_USER>;