psycopg2 error: missing role during database creation

[sflynn-itsc]

I was asked to post this issue here by a colleague who will be following up with a pull request. Actual user/role has been "REDACTED" for github: @amarouane-ABDELHAK here is the issue you requested.

The following error occurs during automated Cumulus deployments (e.g., using Earthdata Bamboo CICD) that include ORCA:

Error: Lambda function (ghrcwuat_db_deploy) returned error: ({"errorMessage": "(psycopg2.errors.UndefinedObject) role \"REDACTED\" does not exist\n\n[SQL: \n        CREATE DATABASE ghrcwuat_orca\n            OWNER REDACTED\n            TEMPLATE template1\n            ENCODING 'UTF8';\n    ]\n(Background on this error at: http://sqlalche.me/e/14/f405)", "errorType": "ProgrammingError", "stackTrace": ["  File \"/var/task/db_deploy.py\", line 51, in handler\n    return task(config)\n", "  File \"/var/task/db_deploy.py\", line 81, in task\n    connection.execute(orca_sql.app_database_sql(config[\"user_database\"], config[\"admin_username\"]))\n", "  File \"/var/task/sqlalchemy/future/engine.py\", line 296, in execute\n    statement, parameters, execution_options or NO_OPTIONS\n", "  File \"/var/task/sqlalchemy/engine/base.py\", line 1521, in _execute_20\n    return meth(self, args_10style, kwargs_10style, execution_options)\n", "  File \"/var/task/sqlalchemy/sql/elements.py\", line 314, in _execute_on_connection\n    self, multiparams, params, execution_options\n", "  File \"/var/task/sqlalchemy/engine/base.py\", line 1400, in _execute_clauseelement\n    cache_hit=cache_hit,\n", "  File \"/var/task/sqlalchemy/engine/base.py\", line 1750, in _execute_context\n    e, statement, parameters, cursor, context\n", "  File \"/var/task/sqlalchemy/engine/base.py\", line 1931, in _handle_dbapi_exception\n    sqlalchemy_exception, with_traceback=exc_info[2], from_=e\n", "  File \"/var/task/sqlalchemy/util/compat.py\", line 211, in raise_\n    raise exception\n", "  File \"/var/task/sqlalchemy/engine/base.py\", line 1707, in _execute_context\n    cursor, statement, parameters, context\n", "  File \"/var/task/sqlalchemy/engine/default.py\", line 716, in do_execute\n    cursor.execute(statement, parameters)\n"]})
26-May-2022 15:18:56    
26-May-2022 15:18:56      on .terraform/modules/orca/modules/lambdas/main.tf line 461, in data "aws_lambda_invocation" "db_migration":
26-May-2022 15:18:56     461: data "aws_lambda_invocation" "db_migration" {
26-May-2022 15:18:56    
26-May-2022 15:18:56    
26-May-2022 15:18:56    Releasing state lock. This may take a few moments...
26-May-2022 15:18:56    Command exited with non-zero
26-May-2022 15:18:56    Command exited with non-zero

The troubleshooting process we engaged in is as follows:
Logged onto a UAT ECS instance and used the following commands to create and enter a postgres docker container on that instance as a troubleshooting measure:

sudo docker pull postgres
sudo docker run -it --rm postgres bash 

After that, inside the postgres docker container, used the following commands to view the users for the UAT RDS DB Cluster's postgres DB. The redacted values can be gotten from AWS Console > AWS Secrets Manager > Secrets > cumulus_rds_db_clusterxxxxxxxxxxxxxxxxxxxxxxxxxx > Scroll down > Click "Retrieve Secret Value" button:

psql -h redacted-host-secret-value     -U <redacted-username-secret-value> <redacted-database-secret-value> 

<A password prompt appears. Use the redacted-password-secret-value for it>

Issued the following command at the ensuing postgres prompt:

\du+ 

This resulted in a table displaying, among other info, the fact that the redacted-username has the following attribues:

"Create role, Create DB"