Обсуждение: Protect extension' internal tables - how?
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.
Hi, > 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? 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
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.