Subject: Protect extension' internal tables - how?

From: Nikita Malakhov <[email-masked]>
Date: Sat, 15 Jul 2023 23:57:30 +0300

Hi hackers!

While working on an extension I encountered a quite tricky question -
the extension (with functions in C) creates tables during function calls,
these tables must be protected from direct users' queries, at the same
time they must remain accessible for all functions of this extension
for all users allowed to use this extension.

Could you please advise or give some hint on what is the correct (and
secure) way to implement this?

Currently I use the owner of the extension as owner when creating
such a table inside the function, but maybe there are some pitfalls
in this kind of solution?

Thanks in advance.

-- 
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/

---

From: Aleksander Alekseev <[email-masked]>
Date: Mon, 17 Jul 2023 15:48:58 +0300

Hi,

> in this kind of solution?

If the goal is to protect the user from an _accidental_ access to the
tables, placing them into a separate schema _my_extension_private or
something will be enough.

Otherwise consider using corresponding access control abilities of
PostgreSQL and creating functions with SECURITY DEFINER [1]. Be
mindful that your functions will become a target for privilege
escalation, so you should be extra careful with the implementation.

[1]: https://www.postgresql.org/docs/current/sql-createfunction.html

-- 
Best regards,
Aleksander Alekseev

---

From: Nikita Malakhov <[email-masked]>
Date: Tue, 18 Jul 2023 14:19:44 +0300

Hi,

Aleksander, thank you very much.
Tables are already placed into special schema, but there are some
dynamically
created tables and the goal is to protect all these tables from direct
insert, update
and delete operations from users. I've read about the SECURITY DEFINER,
it will do the trick.