As part of the initial PR to the metrics-apb that also provisions the aerogear-app-metrics service, a point was raised around best practices when it comes to the management of the Postgres Database.

The best practice would be to follow the principle of least privilege and create a locked down user with the appropriate CRUD privileges for the application database. Here's an example stackexchange answer.

Right now it's not fully clear whether or not we are following best practices.

Currently the Postgres container is defined as follows:

- name: postgresql

      image: "{{ postgres_image }}:{{ postgres_version }}"

      imagePullPolicy: IfNotPresent

      ports:

        - container_port: "{{ postgres_port }}"

      env:

      - name: POSTGRESQL_PASSWORD

        value: "{{ postgres_password }}"

      - name: POSTGRESQL_USER

        value: "{{ postgres_user }}"

      - name: POSTGRESQL_DATABASE

        value: aerogear_mobile_metrics

The same POSTGRESQL_USER credentials are supplied into the metrics service application. We need to determine if that user is in fact a super user or if they are a standard user with a subset of privileges.

The best practice would be to do something similar to these steps:

• Start the Postgres service
• log into the Postgres server using the admin user.
• Create a new user e.g. app_user to be used by the app metrics service
• Create a new database aerogear_mobile_metrics
• Grant SELECT, INSERT, UPDATE, DELETE privileges on the public schema to app_user
• Grant CONNECT privileges on aerogear_mobile_metrics to app_user
• Then the app-metrics service would be passed the app_user credentials when it's started.

This may or may not be necessary depending on whether the POSTGRESQL_USER is a super user or a standard user.

According to some preliminary research by Steven Tobin, it seems the 'user' user we create can only affect the metrics db and isn't a full admin.

The user that is created called user seems to have no rights to anything. Postgres is the default admin and since we dont set an admin password admin only allows local connections.

The 'user' user we create owns the aerogear_mobile_metrics database but nothing else

The goal of this task is to investigate on a low level if there are any potential security issues with the credentials supplied to the application and to identify what changes (if any) would be needed in the metrics-apb and in the aerogear-app-metrics service to fix these issues.