I need to create a user in PostgreSQL with the least privileges possible. So, the user would only be allowed to do queries on specific tables and views. That user should not be allowed to get any additional info, especially not see the view definitions. How can this be done?

  • From where are you afraid that they might get information on the view definition? – Lennart May 5 at 15:04
  • The database is used in the backend of a web server. If the web server gets hacked, then the views could be accessed from there. – Savage Reader May 5 at 15:44
  • The backend connects to the database which is on a separate server. – Savage Reader May 5 at 15:45
  • What bad do you expect to happen if someone sees the view definition? – mustaccio May 5 at 15:45
  • They could steal the views and use them themselves. Basically, imaging that these views are intellectual property that we want to protect. – Savage Reader May 5 at 15:47

You cannot hide the view definition from the user without modifying the database metadata in a way that will break things. But it is no problem to show the user the view definition.

What is more important is to define the view with security_barrier = true so that the user cannot use functions with side effects to get at the underlying tables.

If you want to hide the query used to extract the data, your best choice might be to write a C function that produces the data. If the user has no access to the database server, that should be safe.

| improve this answer | |
  • Does the same answer apply to materialised views? – Savage Reader May 13 at 18:28
  • @SavageReader Yes, it is the same for materialized views. – Laurenz Albe May 13 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.