Postgres 9.4.12 locally and 9.4.7 on AWS RDS.
There are two database users:
untrusted_dev (the latter to mirror AWS RDS:
NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION).
app is granted
app_user (a group role) permissions.
app_user has CRUD permissions on all the normal app tables and functions, but cannot drop tables and so on.
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
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?