[AMBARI-18226] Remove Redundant Embedded Postgres SQL

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.5.0
• Assignee: Andrew Onischuk
• Reporter: Andrew Onischuk
• Created: 22/Aug/16 09:38
• Updated: 16/Sep/16 15:48
• Resolved: 16/Sep/16 10:54

Description

There are currently two SQL files which are being used to initialized Postgres
databases.

• [Ambari-DDL-Postgres-CREATE.sql](https://github.com/apache/ambari/blob/trunk/ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql)
• [Ambari-DDL-Postgres-EMBEDDED-CREATE.sql](https://github.com/apache/ambari/blob/trunk/ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql)

There should be no need to duplicate all of the SQL DDL/DML between these two
files. It's error-prone and cumbersome to maintain.

Instead, the problem seems to be that the embedded SQL doesn't actually switch
users after it bootstraps everything:

CREATE DATABASE :dbname;
\connect :dbname;

ALTER ROLE :username LOGIN ENCRYPTED PASSWORD :password;
CREATE ROLE :username LOGIN ENCRYPTED PASSWORD :password;

GRANT ALL PRIVILEGES ON DATABASE :dbname TO :username;

CREATE SCHEMA ambari AUTHORIZATION :username;
ALTER SCHEMA ambari OWNER TO :username;
ALTER ROLE :username SET search_path TO 'ambari';

-----create tables and grant privileges to db user--------
CREATE TABLE ambari.stack(
stack_id BIGINT NOT NULL,
...
GRANT ALL PRIVILEGES ON TABLE ambari.stack TO :username;

This causes several problems:

• Because tables are being creating from the `postgres` user instead of `:username`, they need to be altered to have privileges granted.
• Because tables are being creating from the `postgres` user instead of `:username`, the default `search_path` is wrong and needs to be prefixed to all calls.

Instead, the embedded SQL should leverage the remote SQL for all of the table
creation and data seeding. The embedded SQL should only be responsible for
bootstrapping the database, schema, and user.

Issue Links

• links to: ReviewBoard
• links to: ReviewBoard