Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0045514Openbravo ERPA. Platformpublic2020-11-05 15:522020-11-27 07:25
Reportershuehner 
Assigned Toshuehner 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionPR20Q4Fixed in VersionPR20Q4 
Merge Request Statusapproved
Review Assigned To
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0045514: Cannot run and create.database twice against AWS RDS database: ERROR: must be owner of database openbravo
DescriptionUsing Amazon RDS postgres variant running ant create.database a second time fails with error
/opt/OpenbravoERP/src-db/database/build-create.xml:104: org.postgresql.util.PSQLException: ERROR: must be owner of database openbravo

Cause is the 'postgres' aka masteruser from RDS with role rds_superuser does not allow 'drop database' if a database not owned by this user.

That is different behavior from 'postgres' superuser in standard PostgreSQL which allows doing that.
Steps To ReproduceCreate Amazon RDS environment (i.e. v12)
configure Openbravo.properties:
- bbdd.url to point to 'endpoint' for RDS instance
- user + userPassword i.e. tad/tad (not important)
- systemUser = <username of masteruser on RDS creation>
- systemPassword = <password of masteruser>

ant create.database (first time)
ant create.database (2nd time)

Observe the permissions error on 2nd run when the 'drop database openbravo' SQL command is executed.
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
blocks design defect 0045385PR21Q1 closed shuehner Cannot run and create.database twice against AWS RDS database: ERROR: must be owner of database openbravo 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2020-11-25 18:23shuehnerTypedesign defect => backport
2020-11-25 18:23shuehnerTarget VersionPR21Q1 => PR20Q4
2020-11-26 13:33hgbotMerge Request Status => open
2020-11-26 13:33hgbotNote Added: 0124631
2020-11-27 07:25hgbotMerge Request Statusopen => approved
2020-11-27 07:25hgbotNote Added: 0124640
2020-11-27 07:25hgbotResolutionopen => fixed
2020-11-27 07:25hgbotStatusscheduled => closed
2020-11-27 07:25hgbotFixed in Version => PR20Q4
2020-11-27 07:25hgbotNote Added: 0124641

Notes
(0124631)
hgbot   
2020-11-26 13:33   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/247 [^]
(0124640)
hgbot   
2020-11-27 07:25   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/247 [^]
(0124641)
hgbot   
2020-11-27 07:25   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: 83c2b1f5a1c93d9a3f1e6186ce4970a0d29feb3b
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 2020-11-26T13:25:05+01:00
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/83c2b1f5a1c93d9a3f1e6186ce4970a0d29feb3b [^]

Fixes ISSUE-45514: Fix repeated create.database error with RDS

Amazon RDS (managed postgres) behaves different with regards of
permissions for 'DROP DATABASE' run by a user which is not the owner of
the database:

The 'ant create.database' command does DROP the configured database (if
it exists) before creating it again (to allow to run the command
repeatedly). That 'DROP DATABASE' is run with the configured bbdd.systemUser
which is typically the PostgreSQL superuser.

Standard PostgreSQL allows the database superuser (typically called
postgres) to drop any database (also if those are owned by different
users like 'tad'

Amazon RDS has a masteruser (with rds_admin role granted) which is the
equivalent of the postgres superuser but which is not allowed to DROP
databases not owned by itself (but it is allowed to take over ownership
of any databasE)

To make that work transparently for the user take over the ownership of
the database just before the 'DROP DATABASE' command to avoid that
permission error.
The 'ALTER DATABASE' command does not have an option 'IF EXISTS' so to
not make it fail in the first use of 'ant create.database' where the
database does not yet exist check for the database to exist before
running the command with an plpgsql DO block.

As the database is being DROPped right after the ownership change does
not have any permanent side-effect. Do avoid the database owner being
change permanently run the ALTER command in the same transaction as the
DROP command. That avoid the different owner leaking in case of the DROP
failing (i.e. when 1 common user is used to create several databases as
it is used ometimes on some developer systems)

---
M src-db/database/build-create.xml
---