Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Installer script doesn't work with PostgreSQL 9.6

Description

I upgraded PostgreSQL to 9.6.0, and I found that the install script doesn't work anymore because of the following error:

09:10:44.826 [Main] INFO org.opennms.core.schema.Migrator - validating database version * using 'postgres' as the PostgreSQL user for OpenNMS * using 'opennms' as the PostgreSQL database name for OpenNMS 09:10:44.922 [Main] INFO org.opennms.core.schema.Migrator - validating database version 09:10:44.933 [Main] INFO org.opennms.core.schema.Migrator - creating OpenNMS user, if necessary java.lang.reflect.InvocationTargetException at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.opennms.bootstrap.Bootstrap$4.run(Bootstrap.java:460) at java.lang.Thread.run(Thread.java:745) Caused by: org.opennms.core.schema.MigrationException: an error occurred creating the OpenNMS user at org.opennms.core.schema.Migrator.createUser(Migrator.java:339) at org.opennms.core.schema.Migrator.prepareDatabase(Migrator.java:447) at org.opennms.install.Installer.install(Installer.java:254) at org.opennms.install.Installer.main(Installer.java:989) ... 6 more Caused by: org.postgresql.util.PSQLException: ERROR: unrecognized role option "createuser" Position: 54 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:321) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:313) at org.opennms.core.schema.Migrator.createUser(Migrator.java:337) ... 9 more

It turns out that the release notes for 9.6.0 has:

Remove the long-deprecated CREATEUSER/NOCREATEUSER options from CREATE ROLE and allied commands (Tom Lane) CREATEUSER actually meant SUPERUSER, for ancient backwards-compatibility reasons. This has been a constant source of confusion for people who (reasonably) expect it to mean CREATEROLE. It has been deprecated for ten years now, so fix the problem by removing it.

Source: https://www.postgresql.org/docs/9.6/static/release-9-6.html

Acceptance / Success Criteria

None

Lucidchart Diagrams

Activity

Show:

Alejandro Galue October 3, 2016 at 11:18 AM

Alejandro Galue October 3, 2016 at 11:18 AM

I've removed CREATEDB and CREATEROLE from the "CREATE USER" statement, and the installer still works. In fact, here is the state after running the installer:

agalue-mbp:bin agalue$ psql -c '\du' List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- agalue | Superuser, Create role, Create DB | {} opennms | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} agalue-mbp:bin agalue$ psql -c '\l' List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- agalue | agalue | UTF8 | en_US.UTF-8 | en_US.UTF-8 | opennms | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | opennms=CTc/postgres postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows)

Now, the opennms user is created with no special privileges, and the DB is properly created as well (the opennms user has privileges over it). I can run OpenNMS and it works as expected.

Alejandro Galue October 3, 2016 at 10:34 AM

For the CREATEDB, that's a good question, and probably you're right but I have to check the installer code to be sure that the opennms DB is created by the superuser role (i.e. the postgres user) and not by the opennms role.

Ron Roskens October 3, 2016 at 10:29 AM

That was a fun rabbit trail to chase down.

Commit d6531f47dd3900eae43a5d0b9e63f7db70c62e6c (June 2006) added opennms-install/src/main/java/org/opennms/install/Installer.java which had the "CREATE USER" statement for the opennms user that included CREATEUSER. Comments indicate some of the file was based on an earlier install.pl script.

$ git log --pretty=format: --name-only --diff-filter=A | sort -u |grep install.pl
tools/run/install.pl

The tools/run/install.pl script was added back in May 2002 and had those arguments. Maybe it was needed back in the 7.X days of PostgreSQL?

If we're considering removing the CREATEROLE privilige, then I guess another question would be: Does the opennms user really require the CREATEDB privilege too? Doesn't the migrator use the admin user to create the opennms database?

Alejandro Galue October 3, 2016 at 10:15 AM

Upgrading the PostgreSQL JDBC Drivers to 9.4.1211 has fixed the issue with the weird am.amcanorder column (which BTW doesn't exist in OpenNMS).

Fixed

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

PagerDuty

Created October 3, 2016 at 9:29 AM
Updated October 11, 2016 at 1:46 PM
Resolved October 11, 2016 at 1:46 PM

Flag notifications