1

We're managing about 200 instances of postgres on Amazon RDS. The problem that we're constantly experiencing is that our "DBA" accounts can't access stuff. Basic stuff. Tables, views, etc.

Is there some kind of event-based automation, call it a trigger or so, that can handle this automatically? Manual grants are not an option - automation is the only possible solution.

Whenever a new database is created, a new schema is created, or anything and everything that is created - the "DBA" users are granted access to it.

What solutions are there for postgres 9.6 or 10?

Example use case:

  • RDS instance "dev1" has 10 postgres databases

  • So we go in and grant access to everything in those 10 databases to our dbauser

  • Next week, a new deployment takes place that creates an 11th database

  • Following that we get a request to export a table from that 11th database and our user doesn't have access to it.

We want to eliminate that.

Stefan
  • 13
  • 3

1 Answers1

1

You can define event triggers on many DDL events, but there is no event trigger for CREATE DATABASE. This means you will have to include the access rights in the database definition. From this point, there are two ways: one is again event triggers, the other is setting default privileges for the different object types you have. I prefer the latter in any case, for being much cleaner (objects and privileges are defined the same way, that is easy to grasp for developers and other humans, too).

For an example how it could work, see one of my older answers, and there the ALTER DEFAULT PRIVILEGES bits. It's about tables, but you can easily generalize it to other object types.

András Váczi
  • 31,278
  • 13
  • 101
  • 147