[jira] [Created] (AMBARI-18226) Remove Redundant Embedded Postgres SQL

[Andrew Onischuk (JIRA)]

Andrew Onischuk created AMBARI-18226:
----------------------------------------

             Summary: Remove Redundant Embedded Postgres SQL
                 Key: AMBARI-18226
                 URL: https://issues.apache.org/jira/browse/AMBARI-18226
             Project: Ambari
          Issue Type: Bug
            Reporter: Andrew Onischuk
            Assignee: Andrew Onischuk
             Fix For: 2.5.0
         Attachments: AMBARI-18226.patch

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.





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)