[
https://issues.apache.org/jira/browse/AMBARI-18226?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15496441#comment-15496441
]
Hudson commented on AMBARI-18226:
---------------------------------
FAILURE: Integrated in Jenkins build Ambari-trunk-Commit #5683 (See
[https://builds.apache.org/job/Ambari-trunk-Commit/5683/])
AMBARI-18226. Remove Redundant Embedded Postgres SQL (aonishuk) (aonishuk:
[http://git-wip-us.apache.org/repos/asf?p=ambari.git&a=commit&h=9dbcac6d5c30cd30d90e5643d87c0010d91317be])
* (edit) ambari-server/src/main/python/ambari_server/serverConfiguration.py
* (delete) ambari-server/src/main/resources/scripts/change_owner.sh
* (edit) ambari-server/src/main/python/ambari_server/dbConfiguration_linux.py
* (edit)
ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql
* (edit) ambari-server/src/test/python/TestAmbariServer.py
> 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, 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)
