> adrian.klaver@aklaver.com wrote:
>
>> neerajmr12219@gmail.com wrote:
>>
>> Is there anyway that we can restrict a user from seeing the schema structure. I can restrict the user from accessing
thedata in the schema but the user is still able to see the table names and what all columns are present in them.
>
> No.
Here’s something that you can do, Neeraj. But you have to design your app this way from the start. It'd be hard to
retrofitwithout a re-write.
Design (and document the practice) to encapsulate the database functionality (i.e. the business functions that the
clientside app must perform) behind an API exposed as user-defined functions that return query results for SELECT
operationsand outcome statuses (e.g. "success", "This nickname is taken. Try a different one", "Unexpected error.
Reportincident ID NNNNN to Support"). JSON is a convenient representation for all possible return values.
Use a regime of users, schemas, and privilege grants (functions having "security definer" mode) to implement the
functionality.Create a dedicated user-and-schema to expose the API and nothing else. This will own only functions that
arethin jackets to invoke the real work-doing functions that are hidden from the client. Allow clients to authorize
ONLYas the API-owning user. Grant "execute" on its functions to what's needed elsewhere.
I've prototyped this scheme. It seems to work as designed. A client that connects with psql (or any other tool) can
listthe API functions and whatever \df and \sf show. (notice that \d will reveal nothing.)But doing this reveals only
thenames of the functions that are called (which will be identical to the jacket names—so no risk here) and the name(s)
ofthe schema(s) where they live (so a minor theoretical risk here).
Full disclosure: I've never done this in anger.
Note: I believe this approach to be nothing other than the application of the time-honored principles (decades old) of
modularsoftware construction (where the entire database is a top-level module in the over all app's decomposition). It
bringsthe security benefit that I sketched along with all the other famous benefits of modular programming—esp. e.g.
theclient is shielded from table design changes.
