> adrian.kla...@aklaver.com wrote: > >> neerajmr12...@gmail.com wrote: >> >> Is there anyway that we can restrict a user from seeing the schema >> structure. I can restrict the user from accessing the data 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 retrofit without a re-write. Design (and document the practice) to encapsulate the database functionality (i.e. the business functions that the client side app must perform) behind an API exposed as user-defined functions that return query results for SELECT operations and outcome statuses (e.g. "success", "This nickname is taken. Try a different one", "Unexpected error. Report incident 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 are thin jackets to invoke the real work-doing functions that are hidden from the client. Allow clients to authorize ONLY as 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 list the API functions and whatever \df and \sf show. (notice that \d will reveal nothing.)But doing this reveals only the names of the functions that are called (which will be identical to the jacket names—so no risk here) and the name(s) of the 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 modular software construction (where the entire database is a top-level module in the over all app's decomposition). It brings the security benefit that I sketched along with all the other famous benefits of modular programming—esp. e.g. the client is shielded from table design changes.
