4

Can I connect to a Heroku Postgres database via an web/application without the risk of dropping a table?

I'm building a Heroku application for a third party which uses Heroku Postgres for the backend. The third party are very security sensitive so I'm looking at applying "Layered security" throughout the application. So for example checking for SQL injection attacks at the web/application layer. Applying a "Layered security" approach I should also secure the database in case a potential SQL injection attack is missed, which might drop a database table.

In other systems I have built there would be a minimum of two users in the database. Firstly the database administrator who creates/drops tables, index, triggers, etc and the application user who would run with less privileges than the database administrator who could only insert and update records for example.

Within the Heroku Postgres setup there doesn't appear to be a way to create another user with less privileges (without the “drop table” option). So the application must connect with the default Heroku Postgres user and therefore the risk of a “drop table” might exist.

I'm running the Heroku Postgres Crane add-on.

Has anyone come up against this or got any creative work arounds for this scenario?

Mark Sivill
  • 825
  • 1
  • 9
  • 18
  • One could argue that if "The third party are *very* security sensitive" you shouldn't be using a cloud provider at all. How many people at Heroku or their upstream provider AWS are able to access the data? – rjmunro Feb 02 '16 at 14:37

1 Answers1

4

With Heroku Postgres you do only have a single account to connect with. One option that does exist for this type of functionality is to create a follower on Heroku Postgres. A follower is asynchronously kept up to date (usually only a second or so behind) and is read only. This would allow you to grant access to the follower to those that need it while not providing them with the details for the leader db.

CraigKerstiens
  • 5,906
  • 1
  • 25
  • 28
  • Thanks @CraigKerstiens the follower database suggestion works well in scenarios where the least privilege requirement is read-only access. Unfortunately in my scenario the least privilege requirement is where the user needs read and write access to the database (and nothing more) – Mark Sivill Mar 18 '13 at 17:23
  • This would also be useful for monitoring solutions (in that case a read-only user). This user should also be used by pg-extras to avoid disaster scenarios (too paranoid?). – Jökull May 01 '13 at 10:54
  • 2
    It should be noted that these usernames and passwords are the same as for the master DB! The account exists due to replication so there is no way to change it's credentials. – reconbot May 07 '13 at 15:53