Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
molomby opened this issue Feb 28, 2018 · 3 comments
Open

Comments

@molomby
Copy link

molomby commented Feb 28, 2018

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
Copy link

elhigu commented Feb 28, 2018

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
Copy link
Author

molomby commented Feb 28, 2018

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
Copy link

mlsad3 commented Mar 5, 2019

Thanks for the workaround code, @molomby

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants