search

SpriteLink / NIPAP

Neat IP Address Planner - NIPAP is the best open source IPAM in the known universe, challenging classical IP address management (IPAM) systems in many areas.
https://spritelink.github.io/NIPAP/
MIT License
536 stars 132 forks source link

Problems installing with PostgreSQL 11 #1207

Open tgranqvist opened 5 years ago

tgranqvist commented 5 years ago

Hello

I'm having trouble installing the software with PostgreSQL 11. The nipapd would run on a Ubuntu 14.04.5 LTS app server, the database (PostgreSQL 11) on a newly built Ubuntu 18.04.1 LTS. The communication across devices works fine, but when I start nipapd, I get loads of errors like

2019-01-20 20:12:54,742: backend    ERROR    Unable to execute query: permission denied to create extension "ip4r"
HINT:  Must be superuser to create this extension.

and eventually:

2019-01-20 20:12:57,590: backend    ERROR    pgsql: FATAL:  remaining connection slots are reserved for non-replication superuser connections

I noticed when running dpkg-reconfigure to double-check my db stuff, that the createuser command is createuser -S -D -R -w nipap. The option -S is --no-superuser, so is this supposed to work? If I tell dpkg-reconfigure not to automatically install the schema on startup, I get the following error when I start nipapd by hand:

ERROR: hstore extension not found in the database
HINT: You can automatically install required extensions and the nipap schema with --auto-install-db

When I then jump over to the database server and run psql -q -c "CREATE EXTENSION hstore", it tells me: ERROR: extension "hstore" already exists

Any tips on what to do? Should I downgrade to an earlier version of PostgreSQL?

tgranqvist commented 5 years ago

Just an update. I downgraded to 9.4. Still same issue. I recreated the nipap user as superuser with createuser -s -D -R -w nipap Notice the small "s" option, when the setup wizard says to use a capital "S". Now the daemon started ok! Will config the cli and www and see if they work. Will also try dropping the privileges for nipap db user to non-superuser.

tgranqvist commented 5 years ago

Another update: I installed a fresh Ubuntu 18.04 server in VirtualBox, added Postgres v. 10 and Nipap. All works out of the box. I'll try also adding a remote pgsql, to better emulate my production environment. Might have to upgrade my production server. Well, 14.04 is approaching EOL anyways.

tgranqvist commented 5 years ago

With two fresh virtual machines, Ubuntu 18.04 LTS, I get the same behavior as in production: The installation wizard can't create the database. If I run createuser with -s in stead of -S (lower vs upper case letter s), as specified in the wizard, it works. I'm not super happy though having the application access the database as superuser.

createuser -s -D -R -w nipap
psql -q -c "ALTER USER nipap ENCRYPTED PASSWORD 'secret'"
createdb -O nipap nipap
createlang -d nipap plpgsql
psql -q -c "CREATE EXTENSION citext"
psql -q -c "CREATE EXTENSION hstore"
psql -q -c "CREATE EXTENSION ip4r"

After purging, manually cleaning the install, and reinstalling, I finally got the www frontend working.

louishot commented 3 years ago

I got this error at this moment. How did you fix it? @tgranqvist

Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: 2021-03-14 09:22:19,734: backend ERROR Unable to execute query: permission denied to create extension "ip4r" Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: HINT: Must be superuser to create this extension. Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: 2021-03-14 09:22:19,739: backend ERROR Unable to execute query: permission denied to create extension "ip4r" Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: HINT: Must be superuser to create this extension. Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: 2021-03-14 09:22:19,741: backend ERROR pgsql: FATAL: remaining connection slots are reserved for non-replication superuser connections Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: ERROR: Backend unable to connect to database Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: ...fail! Mar 14 09:22:19 ip-172-31-19-8 systemd[1]: nipapd.service: Control process exited, code=exited status=1 Mar 14 09:22:19 ip-172-31-19-8 systemd[1]: nipapd.service: Failed with result 'exit-code'. Mar 14 09:22:19 ip-172-31-19-8 systemd[1]: Failed to start LSB: NIPAP XML-RPC server. r

louishot commented 3 years ago

I got this error at this moment. How did you fix it? @tgranqvist

Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: 2021-03-14 09:22:19,734: backend ERROR Unable to execute query: permission denied to create extension "ip4r" Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: HINT: Must be superuser to create this extension. Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: 2021-03-14 09:22:19,739: backend ERROR Unable to execute query: permission denied to create extension "ip4r" Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: HINT: Must be superuser to create this extension. Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: 2021-03-14 09:22:19,741: backend ERROR pgsql: FATAL: remaining connection slots are reserved for non-replication superuser connections Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: ERROR: Backend unable to connect to database Mar 14 09:22:19 ip-172-31-19-8 nipapd[8924]: ...fail! Mar 14 09:22:19 ip-172-31-19-8 systemd[1]: nipapd.service: Control process exited, code=exited status=1 Mar 14 09:22:19 ip-172-31-19-8 systemd[1]: nipapd.service: Failed with result 'exit-code'. Mar 14 09:22:19 ip-172-31-19-8 systemd[1]: Failed to start LSB: NIPAP XML-RPC server. r

I fixed it https://spritelink.github.io/NIPAP/docs/install-unix.html remove the old user and database then Create a user and database manually (Please backup the existing data in the database if you have)

su postgres
dropdb nipap
dropuser nipap
createuser -s -R -D -W nipap
createdb -O nipap nipap
psql -q -c "CREATE EXTENSION citext"
psql -q -c "CREATE EXTENSION hstore"
psql -q -c "CREATE EXTENSION ip4r"

then try dpkg-reconfigure nipapd