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.

Sign up for GitHub

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

Jump to bottom

Postgres 15: permissions errors on public schema #9717

Closed
donsizemore opened this issue Jul 19, 2023 · 0 comments · Fixed by #9877
Closed

Postgres 15: permissions errors on public schema #9717

donsizemore opened this issue Jul 19, 2023 · 0 comments · Fixed by #9877
Milestone
6.0

Comments

@donsizemore
Copy link
Contributor

donsizemore commented Jul 19, 2023 •
edited
Loading

Public schema permissions are much stricter in Postgres 15; we'll need to modify the installer and/or guides to accommodate this change.

I bumped FlyWay to 9.20.1 to explicitly support Postgres 15 for good measure, but:

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration  failed
-----------------
SQL State  : 42501
Error Code : 0
Message    : ERROR: permission denied for schema public
  Position: 14
Location   :  ()
Line       : 1
Statement  : CREATE TABLE "public"."flyway_schema_history" (
    "installed_rank" INT NOT NULL,
    "version" VARCHAR(50),
    "description" VARCHAR(200) NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "script" VARCHAR(1000) NOT NULL,
    "checksum" INTEGER,
    "installed_by" VARCHAR(100) NOT NULL,
    "installed_on" TIMESTAMP NOT NULL DEFAULT now(),
    "execution_time" INTEGER NOT NULL,
    "success" BOOLEAN NOT NULL
)
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for schema public
  Position: 14

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: Migration  failed
-----------------
SQL State  : 42501
Error Code : 0
Message    : ERROR: permission denied for schema public
  Position: 14
Location   :  ()
Line       : 1
Statement  : CREATE TABLE "public"."flyway_schema_history" (
    "installed_rank" INT NOT NULL,
    "version" VARCHAR(50),
    "description" VARCHAR(200) NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "script" VARCHAR(1000) NOT NULL,
    "checksum" INTEGER,
    "installed_by" VARCHAR(100) NOT NULL,
    "installed_on" TIMESTAMP NOT NULL DEFAULT now(),
    "execution_time" INTEGER NOT NULL,
    "success" BOOLEAN NOT NULL
)
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for schema public
  Position: 14

To resolve, we probably want the installer (and Dataverse-Ansible) to:
GRANT ALL ON SCHEMA public TO dvndb unless we just want to grant CREATE?
This was referenced Jul 24, 2023
Closed
Closed
donsizemore pushed a commit to uncch-rdmc/dataverse that referenced this issue Aug 30, 2023
IQSS#9717 bump Flyway, augment installer to support PostgreSQL 15
2a60973
donsizemore pushed a commit to uncch-rdmc/dataverse that referenced this issue Sep 1, 2023
IQSS#9717 allow force flag when Ansible/RDS are involved
c812260
donsizemore pushed a commit that referenced this issue Sep 5, 2023
#9717 support Postgres versions 14 and 15
53b8f93
@donsizemore donsizemore mentioned this issue Sep 5, 2023
Merged
pdurbin added a commit that referenced this issue Sep 7, 2023
@pdurbin
add print about PostgreSQL 15 or higher GRANT #9717
8bb38c4
pdurbin added a commit that referenced this issue Sep 7, 2023
@pdurbin
document Postgres 15 public schema change #9717
19c62ab
donsizemore pushed a commit that referenced this issue Sep 7, 2023
#9717 grant CREATE instead of ALL per pdurbin
f71274e
@pdurbin pdurbin added this to the 6.0 milestone Sep 8, 2023
pdurbin added a commit that referenced this issue Sep 8, 2023
@pdurbin
mention postgres in release note #9717 #9860
12613be
pdurbin added a commit that referenced this issue Sep 12, 2023
@pdurbin
Revert "#9717 grant CREATE instead of ALL per pdurbin"
918f1fb 
This reverts commit f71274e.
jeromeroucou pushed a commit to Recherche-Data-Gouv/dataverse that referenced this issue Sep 14, 2023
@pdurbin @jeromeroucou
Revert "IQSS#9717 grant CREATE instead of ALL per pdurbin"
553bf4b 
This reverts commit f71274e.
stevenwinship pushed a commit that referenced this issue Jun 3, 2024
@stevenferey @jeromeroucou
@prsridha @pdurbin @GPortas @bencomp
Allow merging of accounts that are members of the same group (#9909)
26be8e1 
* Remove unused import

* Merge users with same groups

* Added additional line in Permalinks config

Added an additional line to restart Payara after changing settings in Permalinks section

* Revert "#9717 grant CREATE instead of ALL per pdurbin"

This reverts commit f71274e.

* CREATE instead of ALL for public schema

* Added: getZipDownloadLimit and getEmbargoEnabled API info endpoints

* Added: docs for new info API endpoints

* Fixed: missing guides reference in config.rst

* Changed: :MaxEmbargoDurationInMonths setting directly exposed via API info endpoint

* Changed: updated release notes

* Changed: private Info.java method renamed

* stub out page on API design, esp paths #9880

* remove embargo example, no longer used in #9881

* typo #9880

* Remove unused GPL-licensed code

For unknown reasons, in 2009 several files from the JDK were copied into
the Dataverse codebase, instead of referenced.
It appears that these classes weren't really used.

* Removed unused code

---------

Co-authored-by: Jérôme ROUCOU <jerome.roucou@inrae.fr>
Co-authored-by: Pradyumna Sridhara <95268690+prsridha@users.noreply.github.com>
Co-authored-by: Philip Durbin <philip_durbin@harvard.edu>
Co-authored-by: GPortas <hey@gportas.me>
Co-authored-by: bencomp <ben@companjen.name>
Co-authored-by: jeromeroucou <jeromeroucou@users.noreply.github.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
6.0
Development

Successfully merging a pull request may close this issue.

support Postgres versions 14 and 15 IQSS/dataverse
2 participants
@pdurbin @donsizemore