Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: Using functions for security

    I've been trying to create a function which returns data from a query. Selecting from the function when logged in as a user which has execute on the function but not on the underlying tables gives me a permissions error, reporting that I don't have permissions on the underlying table. Granting permissions on the underlying table makes the function work, but, as a significant purpose of the function is to prevent the user from being able to see the underlying tables, this is not a particularly happy situation.

    Can one use functions in pg/plsql to prevent users from seeing the underlying tables? I was under the impression that one could.

    Thanks,

  2. #2
    Join Date
    May 2008
    Posts
    277
    You need to declare the function SECURITY DEFINER.

    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: CREATE FUNCTION

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    It is more common to use views for this purpose.

  4. #4
    Join Date
    May 2008
    Posts
    277
    If _that's_ all he's trying to accomplish, then yes, I would agree.

  5. #5
    Join Date
    Oct 2009
    Posts
    2
    Thanks. Yes, I want the option to create logic that can't always be placed in views, etc.

  6. #6
    Join Date
    May 2008
    Posts
    277
    Actually, you could probably accomplish this using views and rules.

    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: The Rule System

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •