Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Question Unanswered: How to create a role only for executing functions

    The context is that I am writing a web application, and the web server and database server are on separate machines. I am using java 1.5 and PostgreSQL8.4.

    I want to use a role in the java code in the application to call functions in database, and this role call only execute the functions, but can not see the table structures.

    Is this possible? If it is, how can I do it?

    Thanks.

  2. #2
    Join Date
    May 2008
    Posts
    277
    You would do this no differently than setting other permissions.

    Create your role as normal. Then REVOKE ALL privileges on tables, views, etc. for this role, and GRANT EXECUTE on the functions you want the role to be able to execute.

    One caveat: I believe you'll have to make your functions as SECURITY DEFINER, since functions are normally run with the same privileges as the user calling them.

    As far as I know, if a role can login to the database, there's no way to actually prevent that role from at least seeing what other objects (tables, databases, etc.) exist in the database. So they'll still be able to see your table structure, but not the data in the tables.

  3. #3
    Join Date
    Jul 2010
    Posts
    2
    Thank you very much for your help.

    It is working the way you just described. I can live with the fact that the role can see the table structure but not the data.

Posting Permissions

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