`createDb()` errors with `must be member of role "<my_db_name>"` (on AWS RDS)

[molomby]

I have an issue where my the createDb() function (on my instance of the PostgresDatabaseManager) results in the error:

error: must be member of role "<my_db_name>"

(where "<my_db_name>" is the name of the DB I'm creating).

The error doesn't occur in my dev environment (MacOS 10.13.3 running PosgreSQL 9.6.5), only in staging, which is a AWS RDS instance running PostgreSQL 9.6.6.

The problem sounds exactly like this stackoverflow question. The accepted answer, of adding the superuser to the role created for the new DB, also works for me. Eg..

await knexDbManager.knexInstance().raw(`GRANT ${database} TO ${superUser}`);
await knexDbManager.createDb();

Might be worth adding that GRANT statement to the createDb() function.

[elhigu]

Do you have separate superuser for creating databases etc. and database owner user, who has only access to that one DB?

I haven't been using this for creating databases on RDS so I haven't encountered this problem myself, but indeed stackoverflow issue did seem like this case.

I'm not sure if adding that role always implicitly is the correct way to go. Maybe just recipe for RDS is enough.

[molomby]

Yeah, my app drops/recreates the operational role and db without a problem in dev. Pretty sure that's all good.

Ok... looks like the rds_superuser role that's added to pgSQL RDS instances when they're launched is more restricted than the normal superuser account you'd get with a pgSQL instance. As a result, when creating new DB objects, the rds_superuser account needs to be granted access to them explicitly.

So yeah, it's just a wrinkle of how RDS works.

I agree that always explicitly granting the superuser access to new roles doesn't feel right.

[mlsad3]

Thanks for the workaround code, @molomby