-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/51284/
-----------------------------------------------------------
(Updated Aug. 25, 2016, 12:49 p.m.)
Review request for Ambari, Jonathan Hurley and Vitalyi Brodetskyi.
Bugs: AMBARI-18226
https://issues.apache.org/jira/browse/AMBARI-18226
Repository: ambari
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.
Diffs (updated)
-----
ambari-server/src/main/python/ambari_server/dbConfiguration_linux.py c595e41
ambari-server/src/main/python/ambari_server/serverConfiguration.py 65bf55c
ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql
43bdef9
ambari-server/src/main/resources/scripts/change_owner.sh 6f0ac3d
ambari-server/src/test/python/TestAmbariServer.py a45a4bd
Diff: https://reviews.apache.org/r/51284/diff/
Testing
-------
mvn clean test
Thanks,
Andrew Onischuk
