Why isn’t row level security enabled for Postgres views?

by

Basically because it wasn’t possible to retroactively change how views work. I’d like to be able to support SECURITY INVOKER (or equivalent) for views but as far as I know no such feature presently exists.

You can filter access to the view its self with row security normally.

The tables accessed by the view will also have their row security rules applied. However, they’ll see the current_user as the view creator because views access tables (and other views) with the rights of the user who created/owns the view.

Maybe it’d be worth raising this on pgsql-hackers if you’re willing to step in and help with development of the feature you need, or pgsql-general otherwise?

That said, while views access tables as the creating user and change current_user accordingly, they don’t prevent you from using custom GUCs, the session_user, or other contextual information in row security policies. You can use row security with views, just not (usefully) to filter based on current_user.

More Related Contents:

  1. Optimize query with OFFSET on large table
  2. Return rows from INSERT with ON CONFLICT without needing to update
  3. Refactor a PL/pgSQL function to return the output of various SELECT queries
  4. Is SELECT or INSERT in a function prone to race conditions?
  5. Index for finding an element in a JSON array
  6. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  7. Is it possible to make a recursive SQL query?
  8. Way to try multiple SELECTs till a result is available?
  9. Why is using ‘*’ to build a view bad?
  10. Column ‘mary’ does not exist
  11. Get top row(s) with highest value, with ties
  12. Double colon `::` notation in SQL
  13. Delete parent if it’s not referenced by any other child
  14. PostgreSQL wildcard LIKE for any of a list of words
  15. Query that ignore the spaces
  16. Export specific rows from a PostgreSQL table as INSERT SQL script
  17. SQL query to get most recent row for each instance of a given key
  18. Postgres constraint for unique datetime range
  19. Postgres function returning table not returning data in columns
  20. PostgreSQL create table if not exists
  21. Get values from first and last row per group
  22. How can I add a column that doesn’t allow nulls in a Postgresql database?
  23. Create PostgreSQL ROLE (user) if it doesn’t exist
  24. Operator does not exist: json = json
  25. Function with SQL query has no destination for result data
  26. Postgres INTERVAL using value from table
  27. Crosstab with a large or undefined number of categories
  28. Truncate timestamp to arbitrary intervals
  29. Conditional lead/lag function PostgreSQL?
  30. Is there a postgres CLOSEST operator?

Leave a Comment