CODEWITHSUNDEEP
sqlpostgresql
Ken Chan
Ken Chan

Reputation: 90447

Permission access of the table , view and function

I have a View A that will select data from view B . I only want an user ,say Peter , to have read-only access on view A but cannot select from the view B . So I grant him only have the SELECT right on View A and REFERENCES right on View B . It work very nice.

Now , I have a Function A that will select data from a Table B , and I want Peter can execute Function A but cannot select data from the table B. So , I do the same thing which grants Peter only have the EXECUTE right on Function A and REFERENCES right on Table B . But , it cannot work and an error message "ERROR: permission denied for relation TableB" is thrown when peter executes Function A. How come it does not work this time?

Upvotes: 0

Views: 118

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

I suspect you're looking for security definer.

As an aside, be wary of the leaky view problem. It isn't entirely fixed yet.

Upvotes: 1

Related Questions