A Service for Creating Read-Only Users on Existing Databases

In this article, we explain how to create read only users on existing database services such as MySQL and Postgres, then we show how to combine this with the Brooklyn Service Broker for Cloud Foundry to allow applications to access such a database.

Installation

First we need to update Brooklyn with the entity, so let’s build the entity from source:

$ git clone https://github.com/brooklyncentral/dbaccess-entity.git
$ cd dbaccess-entity
$ mvn clean assembly:assembly -DskipTests

Assuming you have Brooklyn installed in the directory $BROOKLYN_HOME, put the resulting jar into the dropins folder

$ mv target/dbaccess-0.1.0-SNAPSHOT.jar $BROOKLYN_HOME/lib/dropins

Also make sure that there are jdbc drivers for both PostgreSQL and MySQL there too

$ wget https://jdbc.postgresql.org/download/postgresql-9.4-1203.jdbc4.jar -P $BROOKLYN_HOME/lib/dropins
$ wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.36.tar.gz
$ tar zxvf mysql-connector-java-5.1.36.tar.gz
$ mv mysql-connector-java-5.1.36/mysql-connector-java-5.1.36-bin.jar $BROOKLYN_HOME/lib/dropins

then launch brooklyn

$ $BROOKLYN_HOME/bin/brooklyn launch

Creating users with Brooklyn

With the credentials for your database, launch a blueprint using the following as a template and filling in the dbaccess.endpoint.url, dbaccess.admin.user, dbaccess.admin.password, and dbaccess.database to which you would like a new read only user to access.

services:
- type: io.cloudsoft.dbaccess.PostgresDatabaseAccessEntity
  brooklyn.config:
    dbaccess.endpoint.url: postgresql://xxx.xxx.xxx.xxx:5432/
    dbaccess.admin.user: admin
    dbaccess.admin.password:password
    dbaccess.database: db

or if using MySQL:

services:
- type: io.cloudsoft.dbaccess.MySqlDatabaseAccessEntity
  brooklyn.config:
    dbaccess.endpoint.url: mysql://xxx.xxx.xxx.xxx:3316/
    dbaccess.admin.user: admin
    dbaccess.admin.password:password
    dbaccess.database: db


This will create a new read-only user, whose username and password is generated. Should you wish to specify these yourself you can use the following config keys in your blueprint:

dbaccess.username
dbaccess.password

Importing into Cloud Foundry

We are now ready to make catalog items that can be picked up by the Brooklyn Service Broker. We need to be careful, however, that these admin credentials are not leaked to consumers of the service by the Service Broker. To do this we make two catalog items, the first is hidden from the Service Broker and the second, which is used by the Broker, references the first.

Under broker.config set hidden to true:

brooklyn.catalog:
  id: com.cloudsoft.development.dbaccess
  version: 1.0
  description: Provides admin credentials for the DB database
  displayName: DB credentials
  iconUrl: classpath://postgresql
  itemType: template

brooklyn.config:
  broker.config:
    hidden: true

services:
- type: io.cloudsoft.dbaccess.PostgresDatabaseAccessEntity
  brooklyn.config:
    dbaccess.endpoint.url: postgresql://xxx.xxx.xxx.xxx:5432/
    dbaccess.admin.user: admin
    dbaccess.admin.password: password
    dbaccess.database: db

Next, we create a catalog item that defines a single plan, standard, and uses a propagator to map the datastore.url sensor to uri, which is the expected field in the credentials map passed to PostgreSQL consumers in Cloud Foundry.

brooklyn.catalog:
  id: com.cloudsoft.development.dbaccess.user
  version: 1.0
  description: Provides access to the DB database
  displayName: DB Access
  iconUrl: classpath://postgresql
  itemType: template

brooklyn.config:
  broker.config:
    sensor.whitelist:
    - uri
    plans:
    - name: standard
      description: Creates a read-only user on the db
      plan.config: {}

services:
- type: com.cloudsoft.development.dbaccess:1.0
  brooklyn.enrichers:
  - type: org.apache.brooklyn.enricher.stock.Propagator
    brooklyn.config:
      sensorMapping:
        $brooklyn:sensor("datastore.url"): $brooklyn:sensor("uri")

Next, we enable the service:

$ cf service-access                                                                                  
Getting service access as admin...
broker: brooklyn
   service            plan       access   orgs        
   br_db_access       standard   none 

$ cf enable-service-access br_db_access
Enabling access to all plans of service br_db_access for all orgs as admin...
OK

Then, we can create a new service:

$ cf create-service br_db_access standard postgresql
Creating service instance postgresql in org me / space development as admin...
OK

Create in progress. Use 'cf services' or 'cf service postgresql' to check operation status.

And when we bind it to an app we get the credentials:

$ cf bind-service postgres-app postgresql
Binding service postgresql to app postgres-app in org me / space development as admin...
OK
TIP: Use 'cf restage postgres-app' to ensure your env variable changes take effect

$ cf env postgres-app  
Getting env variables for app postgres-app in org me / space development as admin...
OK

System-Provided:
{
 "VCAP_SERVICES": {
  "br_db_access": [
   {
    "credentials": {
     "uri": "postgresql://xxx.xxx.xxx.xxx:5432/db?user=user_e89eck\u0026password=PeugtuHnuESq"
    },
    "label": "br_db_access",
    "name": "postgresql",
    "plan": "standard",
    "tags": []
   }
  ]
 }
}

Conclusion

The new DBAccess Entity allows Brooklyn to create Read-Only users on existing databases. We have shown how you can use this in Cloud Foundry with the Brooklyn Service Broker to allow your applications to consume from existing databases without manually creating a new user and registering a User-Provided service.