Unanswered: keeping internal data hidden from users
I'm trying to implement an abstract table that has some columns of data supplied/maintained by an external user and other columns that are for internal use only (admin type stuff), for which I don't even want their existence visible to the external user.
For security reasons, I want to keep the external user's visibility limited only to the few columns in only the one table he needs to maintain.
I'm using the psql \describe commands as my benchmark to external visibility.
I've implemented my abstract table as two DB tables with rules to keep them in sync., but the problem is that I can still get descriptions of the internal tables (as well as other tables outside the schema to which he is supposedly limited if he knows the right name...???).
I've tried schemas, rules, and views with various privileges for the external user and can't seem to get the internal data hidden yet linked.
The way we got around it was revoking all privs for tables from the end users.
Instead, the users only had access to tables via functions, defined with SECURITY DEFINER.
This was, the functions would run and the users (or other applications) could use the functions to do INSERT. DELETE, UPDATE and SELECT, but the functions had complete control over who did what and when.
The other nice benefit was that the underlying structure could change without needing to update any external software.