Postgres 9.4.12 locally and 9.4.7 on AWS RDS.

There are two database users: app and untrusted_dev (the latter to mirror AWS RDS: NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION). app is granted app_user (a group role) permissions.

1) app_user has CRUD permissions on all the normal app tables and functions, but cannot drop tables and so on.

2) untrusted_dev is used for schema migrations.

3) Before/after migration hooks revoke and then re-grant CONNECT privileges for app_user (hence the group role). The before hook also kicks everyone out:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname='the_app_db' AND pid <> pg_backend_pid();

As I understand it, this separation is good from a security point of view, so that if a user somehow gains control of the app user, their permissions are limited.

And running a migration on AWS RDS (with app users connected) does successfully boot them out. Yet, running it on a local Postgres 9.4.12 instance (with app users connected) gives the error in the title in response to the pg_terminate_backend() call.

Why? (I would expect the same behaviour locally and with RDS. After all, RDS's untrusted_dev's only difference is that it has rds_superuser, but that's not actually a superuser.)

Also, if it's right that I get this error, how should a migration work if you need to boot users, but have no superuser permissions and have a separate login explicitly for the regular users whom you are trying to boot?

  • I've also tried making both the public schema and the postgres database owned by untrusted_dev like they are in RDS. Same error still. – Kev Jul 11 '17 at 18:21
  • Does the "have the same role" part from the question/answer you linked to, apply in your situation? – mustaccio Jul 11 '17 at 18:57
  • @mustaccio It can't, if I want to maintain a separate between regular users (app) and the more privileged logins used for migration (untrusted_dev) per best practices. That's kind of the crux of my question: How are you supposed to follow best practices here without getting an error? – Kev Jul 11 '17 at 18:59
  • @mustaccio Another very strange point, in that question, it's AWS RDS that gives the error. For me, I get the error locally, and AWS RDS works fine! Hmm... – Kev Jul 11 '17 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.