I have quite a bit of db experience with Oracle, MSSQL and Sybase. I am trying
PostgreSQL for the first time and I am unable to find a feature in PostgreSQL
that I am used to using in the other database: stored procedure executing with
the rights of the creator, not the user.
I have searched the doc, several online books and the mailing list archives
without success. Perhaps I have not hit on the correct keywords.
In my case, I am trying to create a function that will allow a user to
insert/update records on a table to which the user does not have select, update
or insert privileges. In Oracle, as long as the function owner has grant
privileges(with grant option), you only have to give execute permission on the
function to the user, not select/insert/update on the table.
It seems the recommended way (in Postgres) to hide columns from a user was to
write a view, and grant the user select rights on the view. I also found some
information about using rules on views to allow updates, but I was unsuccessful
in getting this to work without granting the full select/insert/update rights
on the original table.
It seems curious to me that I would be the first person to run across this
issue. Can anyone provide references to how other people have worked around
this issue?
Thank you for your time.
Jason
