Executing a function in another database doesn't work

[BorislavZlatanov]

I've created two separate databases for timetable and for my app. This is for enhanced security. This issue seems very similar: #164

Here is the command I use for creating the job:
SELECT timetable.add_job('myjob','* * * * *','SELECT public.fetch_data()','["--dbname=my_app_db"]'::jsonb);

pg_timetable says: function public.fetch_data() does not exist (SQLSTATE 42883)] [txid:1067] Task execution failed

But I can verify the function exists in the my_app_db database, in the public schema, and I can successfully execute it from terminal.

This is on Ubuntu 20.04, Postgres 14, pg_timetable 5.6.

[pashagolub]

This issue seems very similar: #164

No, it's absolutely not. That issue is for an ancient version of schema. There is no even such a table anymore.

'["--dbname=my_app_db"]'::jsonb

This is not gonna work. These suppose to be parameters/arguments for tasks, just like function parameters/arguments.

Check the sample on how to establish remote task. There is no way to create a remote job with timetable.add_job() function at the moment

[BorislavZlatanov]

Oh, sorry. Here is what I am attempting to accomplish. I have a database for my app. I want to add pg_timetable to my app, but I want to isolate the role that pg_timetable uses. I want to isolate that role for better security, so that if an attacker manages to compromise the role, they will only get limited access to the pg_timetable schema and will not get access to my app's tables and functions.

So what I was thinking is that I can create a separate database just to be used by pg_timetable. In this way I'd have two databases on the same Postgres server - one for my app and one for pg_timetable. If the role used by pg_timetable gets compromised, it will be more difficult for the attacker to take over the other database.

Does this make sense?

After further reflection, I am thinking that there is a better approach than the above. If it would be possible to use the Postgres peer authentication method, instead of creating a role with a password, then there is no concern about the role becoming compromised. I am also using postgREST and it has the ability to use peer authentication (see the last bullet point).

Since I am creating a kind of an app template which I hope many other devs will use for creating apps, I want to minimize the possibility of a compromise, and that possibility unfortunately exists when we use passwords. Devs set weak passwords, don't handle passwords properly, don't use secure ways to share them, and so on (many compromises like that have already happened). But the peer authentication method makes it so we don't have to use a password. Do you think it would make sense to add this authentication method to pg_timetable so we don't have to create a role with a password?

[pashagolub]

No problem! :) I've been there.

Having a dedicated database for pg_timetable makes sense, for example if you have several different clients (tenants) and all of them use their own database.

In your case you have only one database. And pg_timetable operates in a separate timetable schema. So having a special role (e.g. scheduler) owning timetable schema is a good choice.

Then you can grant some permissions on database objects to scheduler to operate. Or you can use task.run_as option to run some tasks under another role.

Long story short: I think in your case it's better to put everything in one database.

Do you think it would make sense to add this authentication method to pg_timetable so we don't have to create a role with a password?

AFAIK every application can use peer auth method (correct me if I'm wrong, never checked this), since it's just an obtaining the client's operating system user name from the kernel and using it as the allowed database user name, e.g.

sudo -u scheduler pg_timetable --config=config.yaml ....

[pashagolub]

It's not that simple as I wrote but it's still possible to use Unix sockets in connection string. So something like this should work:

sudo -u scheduler pg_timetable --clientname=somefoo postgres://scheduler/dbname?host=/tmp

[pashagolub]

Ok, I've tested this on my Ubuntu-20 and here is what I have:

> sudo -u postgres ./pg_timetable --clientname=somefoo postgres:///timetable?host=/var/run/postgresql
2023-12-07 13:47:31.531 [INFO] [sid:2143469293] Starting new session...
2023-12-07 13:47:31.631 [INFO] Database connection established
2023-12-07 13:47:31.646 [INFO] Accepting asynchronous chains execution requests...
2023-12-07 13:47:31.650 [INFO] [count:0] Retrieve scheduled chains to run @reboot
2023-12-07 13:47:31.655 [INFO] [count:0] Retrieve interval chains to run
2023-12-07 13:47:31.670 [INFO] [count:1] Retrieve scheduled chains to run
2023-12-07 13:47:31.671 [INFO] [chain:7] Starting chain
2023-12-07 13:47:31.685 [INFO] [chain:7] [task:510] [txid:7130] Starting task
2023-12-07 13:47:31.687 [INFO] [chain:7] [task:510] [txid:7130] Task executed successfully
...

Here database is timetable, user is postgres, unix socket location is /var/run/postgresql

[BorislavZlatanov]

Thank you very much for your help! This worked.

I just removed ./ in front of pg_timetable, and I created a dedicated OS user with the same username as the Postgres role, so that the peer authentication can work. So I have:

useradd -ms /bin/bash scheduler
[...]
sudo -u scheduler pg_timetable --clientname=myapp postgres:///mydb?host=/var/run/postgresql &

With an ampersand at the end so that it doesn't take over the terminal.

Thanks again, I really enjoy this solution and enhanced security for this use case.