Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0045385Openbravo ERPA. Platformpublic2020-11-05 15:522020-11-27 07:23
Reportershuehner 
Assigned Toshuehner 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionPR21Q1Fixed in VersionPR21Q1 
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
Summary0045385: 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
depends on backport 0045514PR20Q4 closed shuehner Cannot run and create.database twice against AWS RDS database: ERROR: must be owner of database openbravo 
causes defect 0051718 closed shuehner install.source fails with "DROP DATABASE cannot be executed within a pipeline" with postgres latest minor releases 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2020-11-05 15:52shuehnerNew Issue
2020-11-05 15:52shuehnerAssigned To => shuehner
2020-11-05 15:52shuehnerOBNetwork customer => No
2020-11-05 15:52shuehnerModules => Core
2020-11-05 15:52shuehnerTriggers an Emergency Pack => No
2020-11-25 18:23shuehnerStatusnew => scheduled
2020-11-25 18:27hgbotMerge Request Status => open
2020-11-25 18:27hgbotNote Added: 0124621
2020-11-27 07:23hgbotMerge Request Statusopen => approved
2020-11-27 07:23hgbotNote Added: 0124638
2020-11-27 07:23hgbotResolutionopen => fixed
2020-11-27 07:23hgbotStatusscheduled => closed
2020-11-27 07:23hgbotFixed in Version => PR21Q1
2020-11-27 07:23hgbotNote Added: 0124639
2023-02-28 12:27shuehnerRelationship addedcauses 0051718

Notes
(0124621)
hgbot   
2020-11-25 18:27   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/245 [^]
(0124638)
hgbot   
2020-11-27 07:23   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/245 [^]
(0124639)
hgbot   
2020-11-27 07:23   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: 6e413f3e92249eb8555b75dba53552674db51526
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 2020-11-25T17:15:25+00:00
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/6e413f3e92249eb8555b75dba53552674db51526 [^]

Fixes ISSUE-45385: 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
---