Pivotal Knowledge Base

Follow

Error binding to POSTGRES service: "Service broker error: pq: invalid privilege type SELECT for database"

Environment

AWS Service Broker: 1.1.0

Symptom

After upgrading to version 1.1.0, some users may get this error when trying to bind an app to a postgres database created by the AWS service broker

Server error, status code: 502, error code: 10001, message: Service broker error: pq: invalid privilege type SELECT for database

Cause

Configurable database permissions where added in AWS broker version 1.1.0 via the AWS service broker tile.  What ever permissions are set in this field will get applied to the user for newly created databases

up-bad.jpeg

For example, let's assume the broker creates a new database called "newdb" and a new user called "user1." Given the permissions in the above screen shot the AWS broker would apply for those permissions as followed and get the error observed during the bind operation. This is because postgres does not support setting the SELECT permission on the database for a given user without more specific options. 

postgres=# GRANT SELECT,CREATE,TEMP,CONNECT ON DATABASE newdb TO user1;
ERROR:  invalid privilege type SELECT for database

Resolution

AWS broker version 1.3.0 now sets the default permissions to "CREATE, TEMP, CONNECT." These are the recommended permissions that should be set.  Please update your AWS service broker tile and apply the default settings from 1.3.0 or simply remove the "SELECT" privilege from your existing configuration located in AWS Service Broker Tile -> Settings -> RDS Config -> PostgreSQL Plans -> User Privileges.

up.jpeg

Additional Information

A service broker will not delete a production Database. Only when a Developer with permissions for that Org and Space performs a "cf delete-service" will a database instance be deleted. If a "cf bind-service" has been done to that database instance, even a deletion attempt will fail until the app has been unbound. And if you try to delete the tile with existing service instances, you will get:

"Server error, status code: 400, error code: 270010, message: Can not remove brokers that have associated service instances: aws-services-broker"

Comments

Powered by Zendesk