Postgres Trigger Node Help

[gariva]

HI, @marcus and @agobrecht, or anyone that can help. I have been trying again to use the
postgres trigger node with no luck.

I am trying just the trigger node. The table ‘alertas_rastreos’ has all the permits for the n8n user.
After I put the node into active or use the ‘Listen for Event’ Either nothing happens or I get errors, picture of example attached.

It will be very helpful to be able tu use this feature but I have not been able to make it work.

Im using n8n cloud pro version with postgres database

Some help would be appreciated.
Thanks
Gariva

{
“meta”: {
“instanceId”: “364dbece6fb80028ba36d28be62a2a51f4bd2e94c19a85449b670732f5e635bc”
},
“nodes”: [
{
“parameters”: {
“schema”: {
“__rl”: true,
“mode”: “list”,
“value”: “public”
},
“tableName”: {
“__rl”: true,
“value”: “alertas_rastreo”,
“mode”: “list”,
“cachedResultName”: “alertas_rastreo”
},
“additionalFields”: {}
},
“id”: “28054fd9-96ba-435c-8efc-ff4227c03c4c”,
“name”: “Postgres Trigger”,
“type”: “n8n-nodes-base.postgresTrigger”,
“typeVersion”: 1,
“position”: [
780,
500
],
“credentials”: {
“postgres”: {
“id”: “1”,
“name”: “App DB”
}
}
}
],
“connections”: {}
}

Describe the problem/error/question

What is the error message (if any)?

Please share your workflow

(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)

Share the output returned by the last node

Information on your n8n setup

• n8n version:
• Database (default: SQLite):
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app):
• Operating system:

[n8n]

It looks like your topic is missing some important information. Could you provide the following if applicable.

• n8n version:
• Database (default: SQLite):
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app):
• Operating system:

[Jon]

Hey @gariva,

That looks like there is a permission issue on the database you are using, Is the user you are connecting to the database with the owner of the database or does it just have all privileges assigned?

I found a lot of posts online about the must be owner of relation error, This page could be useful: sql grant - Postgresql: error "must be owner of relation" when changing a owner object - Stack Overflow

[gariva]

@Jon, thanks for the answer.

It is not clear in the documentation what is required, in terms of permisions on the postgres table, for the trigger node to work.
Is it required that the db n8n user for n8n is the onwer of the table for the trigger node to work?

If not then I don’t know what other permit to grant the n8n user. Attached is a screen shot of all the permits granted to the n8n user on the referred table.

It might be a problem for me to change ownership since other tasks, api’s or processes might relay on that.

Thanks.

[Jon]

Hey @gariva,

Looking at the Postgres documentation they only mention that the Trigger privilege is required for adding a trigger to a database but the error you are getting from the database seems to suggest that an owner right might be needed for the relation and to be honest in the world of Postgres I am not sure what that actually is.

I will need to some testing with the trigger node and a database to work out what else could be missing, Does postgres have a log at all maybe there is more context in there.

[gariva]

@Jon, thanks for your time on this topic. I do think postgres keeps logs, but I am no postgres expert and have no access to the console of our postgres server.
If I can help in your tests let me know.
Maybe if there are other users using this feature they can offer some guidance. Do you know any other user of this node?

Regards,
Gariva

[Jon]

Hey @gariva,

I know we have other users using it but I don’t have the details on who they actually are. Do you have a team that manages the database that could take a look? The error you are getting seems to be coming back from Postgres so it could be that the owning user has some kind of lock in place.

[gariva]

@marcus, @agobrecht, Still cant make this work.

I have updated the DB engine version and tried playing with all the parameters available in the Postgres Trigger node.

I get it to work once then it does nothing.

Hope you can help me with this. I been trying this since is was released and I have not been able to make it work. I was hoping the documentation got better but have no seen any change.

Thanks in advanced
gariva

[Jon]

Hey @gariva,

Does that user have access to the table or field the error was referencing?

[gariva]

@Jon thanks for your prompo answer.

On my last test, yesterday, I was using the postgres user who is the owner of the whole db. I did not get the ‘owner’ error this time but still the thing did not work cosistently. I actually did a test 30 min ago and, again, it worked fine for 1 time and then it did not register the new inserts.

Thanks
Gariva

[Jon]

Hey @gariva,

That sounds like progress, When it didn’t pick up new inserts was the workflow active and did anything odd appear in the Postgres logs?

[gariva]

@Jon, Yes the workflow was active.It catched one occurance yesterday and stoped. and then today I modified the parameters, deleted alll the extra fields and let it do the default stuff and it also just catched one occurance and then stopped working.

[gariva]

I dont see the progress…I dont think that using the postgres user in production for n8n to connect to the db is a best practice…

[Jon]

Hey @gariva,

Using the postgres user probably isn’t the best idea but you would need to make sure that the account you use has the trigger privilge as documented by Postgres and it would also need access to all of the databases and tables that it needs to monitor.

[gariva]

@Jon For now I would be happy if it worked with the postgres user.

Tried again.
Deactivated the workflow in n8n.
Went into pgadmin and deleted all traces of the n8n trigger and trigger functions from the DB
Reactivated the workflow.
Generated a record on the monitored table and got the trigger to register the activity and show the execution on the workflow history.
Generated a second record on the monitored table. No response from the trigger. No execution registered.
Went to pgadmin and checked that the trigger and trigger function are still there.

Don’t know what to try next.
Please help.

[Jon]

Hey @gariva,

Have you checked the Postgres logs for any errors?

[gariva]

@Jon Here is the log. I changed the n8n user to n8nworkflowswrite so it be clear what entries are related to n8n.

Basically this is the kind of message that is getting logged.

• 2024-01-30 23:29:48 UTC:10.11.2.217(34094):n8nworkflowswrite@checkups_db:[9873]:LOG: could not receive data from client: Connection reset by peer

From what I can find it appears this could be on the app, n8n, side. Is there an n8n log thatn throw some light on this?

[Jon]

Hey @gariva,

I don’t think we have a debug option for that but it looks like it could be a networking issue, Let me give it a test again here and see what I can find.