Platform Support

Datical DB supports these Postgres platforms and variants:

• EDB Postgres
• PostgreSQL (on-premise)
• PostgreSQL on Amazon RDS
• PostgreSQL on Amazon Aurora

See Hardware, Software, and Database Requirements for more information on version support. 

Prerequisite: Ownership of Schema and Database Objects

In order for Datical DB to manage an existing Postgres instance, it must connect as a user with at least one of the following requirements:

• Owns all schema to be managed by Datical DB and owns all objects in those schema.
• Is a superuser (on-prem only–the superuser on Amazon RDS or Aurora does not have sufficient permissions)

Postgres has a distinction between ownership and privilege, and only one user may own an object. Only the owner (or a superuser) may alter or drop a given object. Therefore, simply granting the owner's role to another user is not sufficient. Ownership must be explicitly transferred (see below). When managing Postgres databases, please disregard any documentation referencing the use of multiple roles for Datical DB connection users.

"The right to modify or destroy an object is always the privilege of the owner only." - from PostgreSQL documentation https://www.postgresql.org/docs/9.6/ddl-priv.html

Since it is necessary for Datical DB to perform both backup and restore operations on all schema it manages (which involves dropping and recreating entire schema and their objects), its connection user must own these schema and objects. If it does not, packaging fails and a permissions error is reported. 

mydb=> CREATE ROLE change_owner LOGIN;
CREATE ROLE

2. Make both the current owner ('old_owner') and the new Datical owner ('new_ddb_owner') roles members of the newly created role

mydb=> GRANT old_owner TO change_owner; 
GRANT ROLE 
mydb=> GRANT new_ddb_owner TO change_owner; 
GRANT ROLE

$ psql -U change_owner mydb
mydb=>

mydb=> REASSIGN OWNED BY old_owner TO new_ddb_owner 
REASSIGN OWNED

Pipeline Steps in the Project

Each database in your release pipeline is represented as a step in the Datical DB project. Each step has its own set of connection information, including the user to connect as (the connection user).

At each step, the connection user must be the owner of all schema managed by Datical DB and the owner of all objects in that schema.

Choosing a User to Manage PostgreSQL Databases

If you already have a user with the requisite ownership, you can configure Datical DB to connect as that user and skip the following steps.

Creating a User to Manage PostgreSQL Databases

If you want to create a new user to be the Datical DB connection user, then you have two options:

1. Ensure that user is a superuser (on-prem Postgres)
2. Transfer ownership of all existing schema to be managed by Datical DB and all objects in those schema.

The following sections show how to create a user, grant the required permissions, and transfer ownership of database objects. 

1. Creating the Datical User and Granting the Required Permissions

Log in as a superuser and run the following commands, substituting user and database name appropriately.

CREATE USER "datical_user" WITH PASSWORD 'password';
GRANT CREATE, CONNECT, TEMPORARY ON DATABASE "your_database_name" TO "datical_user" WITH GRANT OPTION;

Note: It is important to add @<server-name> after the user name for the Azure Postgres connection in DbDef like so:

CREATE USER "datical_user@<server_name>" WITH PASSWORD 'password';
GRANT CREATE, CONNECT, TEMPORARY ON DATABASE "your_database_name" TO "datical_user" WITH GRANT OPTION;

2. Transferring Ownership of Database Objects to the Datical User

You must either log in as a superuser or the object owner to transfer ownership.

1. Make the current owner a member of the Datical user role.

   • Log in as a superuser or the Datical user and grant membership:

     GRANT "datical_user" to "current_owner";

2. Transfer ownership of database objects.

   • To transfer ownership of a schema:
     
     

     ALTER SCHEMA "schema_name" OWNER TO "datical_user";

   • To transfer ALL objects on a database to another user:
     
     

     REASSIGN OWNED BY "current_owner" TO "datical_user";

   • To transfer a single object on a database to another user, please refer to the appropriate ALTER statement for that object type in the Postgres documentation.

Limitations

• See Known Issues in /wiki/spaces/D19Q2/pages/848725720 and release notes for maintenance releases. 

Function Type Support On Cloud Environments

This is general information about Amazon support for these functions. The user with the highest level of permissions on Aurora/RDS (rds_superuser) does not have the capability to create certain functions. This is the case whether you are using Datical or not!

Function Type	Community Edition	Cloud (Aurora/RDS)
query language functions
procedural language functions (TRUSTED)
procedural language functions (UNTRUSTED)
internal functions
C-language functions