[ https://issues.apache.org/jira/browse/AMBARI-18226?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Andrew Onischuk updated AMBARI-18226: ------------------------------------- Status: Patch Available (was: Open) > 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)