2018-08-23 17:18 GMT+02:00 Nico Williams <n...@cryptonector.com>: > Couldn't this be handled by having a new permission on FUNCTIONs > ("CALL"?) to distinguish EXECUTE? >
I don't understand to this questions. Private functions will be functions still - there is not necessity to call these functions differently. > This would have to be made backwards-compatible, possibly by > automatically granting CALL ON ALL FUNCTIONS to public at schema create > time and/or PG upgrade time, which the schema owner could then REVOKE, > or by requiring CALL permission only functions marked PRIVATE. > > Because initially a schema has no functions, the owner can revoke this > grant to public before creating any functions, thus there would be no > race contition. A race condition could be made less likely still by > having CALL not imply EXECUTE (users would have to have both to > successfully call a given function). > > I would agree that a PRIVATE keyword would be a syntactically convenient > way to say that in its absence then public gets granted CALL on the > given function. But IMO it shouldn't be necessary, and either way > permissions machinery should be involved. > > What do other SQL databases do? Does any have a PRIVATE keyword for > FUNCTIONs? > The private flag, what I propose, has not relations to access rights - the limiting factor is scope. The system of access rights is not touched by this feature. The other databases has not PRIVATE keyword, what I know - but have some different mechanism how to hide some internal API PL/SQL package functions are by default "private" if are not mentioned in package header. > Using permissions has the net effect of making visibility more > fine-grained. > > Regarding \df, I'm not sure that hiding function names one cannot call > is worthwhile, but if it were, then there are several options depending > on whether confidentiality of function names is to be a security > feature: RLS on the pg_catalog.pg_proc table (provides confidentiality), > or having a system view on it or filtering in psql (no real > confidentiality). > Probably private objects (functions) should not be visible in \df .. maybe \dfP or some similar can be used. > > All that said, being able to have PRIVATE schemas, tables, views, > functions, FDWs, variables, maybe even roles, is definitly appealing, > mainly for code organization reasons. > > I didn't understand how PRIVATE would help reduce the need for SECURITY > DEFINER. Can you explain? > sure. Any object marked as private - is not accessible directly .. You want to protect these objects against access in wrong order, .. but sometimes you should to use data (or features) of these objects. Now, you should to use security definer functions and any user can call these functions, and then the access to protected objects is controlled. But you should to use security definer functions. When you use private flag, then this object can be accessed only from function (procedure, ...) from same schema. And if some user doesn't create right for schema, then he hasn't free access to private objects. But this user can use any non private objects from the schema, if has necessary rights, and then the access to private objects is controlled and security definer functions are not necessary. > Nico > -- >