Server crashes when watch is used by non-super user

[EyMaddis]

Hi,
when running PostGraphQL with pgWatch enabled, but without access to a super user, the server will not start and crash instead.

Failed to setup watch fixtures in Postgres database ️️⚠️
This is likely because your Postgres user is not a superuser. If the
fixtures already exist, the watch functionality may still work.
error: current transaction is aborted, commands ignored until end of transaction block
    at Connection.parseE (service-postgraphql/node_modules/pg/lib/connection.js:554:11)
    at Connection.parseMessage (service-postgraphql/node_modules/pg/lib/connection.js:381:17)
    at Socket.<anonymous> (service-postgraphql/node_modules/pg/lib/connection.js:117:22)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:189:7)
    at readableAddChunk (_stream_readable.js:176:18)
    at Socket.Readable.push (_stream_readable.js:134:10)
    at TCP.onread (net.js:551:20)

My guess is that there is automatic rollback missing.

Related question: which permissions are required to this to work?

[calebmer]

Only super users can create event triggers which is what we need for watch mode: https://www.postgresql.org/docs/9.6/static/sql-createeventtrigger.html

If you don’t want to run PostGraphQL as a superuser and development, but still want watch mode just run this SQL file: https://github.com/calebmer/postgraphql/blob/master/resources/watch-fixtures.sql

But yes, we definitely should not crash if we fail to run the fixtures.

I’m not entirely sure what is causing this. I opened a PR with my initial thoughts on how to fix it (#369), but then I remembered that the watch fixtures are run in a transaction already: https://github.com/calebmer/postgraphql/blob/master/resources/watch-fixtures.sql

Is your code automatically running SQL commands in a transaction?

[EyMaddis]

Is your code automatically running SQL commands in a transaction?

Not that I know of, I just used PostGraphQL as a library and set it up to another path.

I noticed another thing. I tried running the watch-fixtures.sql before and it failed because with my local PostgreSQL version (9.4) since $libdir/plpgsql was not found. This might have been the error that caused the crash, since I tried starting PostgraphQL with a superuser.
I since updated my installation to 9.6 which fixed the plpgsql issue.

I mention this, because it might be relevant to check the display the error message to the user.

If you don’t want to run PostGraphQL as a superuser and development, but still want watch mode just run this SQL file: https://github.com/calebmer/postgraphql/blob/master/resources/watch-fixtures.sql

This does not work, as it still crashes, even if the trigger already exists.

[calebmer]

This should fix it, I’ll try to get it in a release soon: #371