Обсуждение: "Freezing" per-role settings
Folks, I noticed a little unimplemented feature which I suspect a lot of people would find useful, namely the ability to "freeze" certain settings for a role. Example: We'd like to create a role called read_only, with eponymous capability. At the moment, we can't do what's below, but I'd like to be able to make it possible. First, we'd issue the following, which doesn't work yet: ALTER ROLE read_only SET transaction_isolation read_only; Then, there's one way via DCL (Data Control Language) REVOKE SET transaction_isolation FROM read_only; Another would be via DDL: ALTER ROLE read_only FREEZE transaction_isolation; I'd think of the reverse of each of these as GRANT and ALTER ... THAW, respectively. Is anyone else interested in such a feature? If so, is it more DCL-ish, or more DDL-ish? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, 2010-09-07 at 11:39 -0700, David Fetter wrote: > We'd like to create a role called read_only, with eponymous > capability. Seems useful. > If so, is it more > DCL-ish, or more DDL-ish? I don't like the idea of a security model relying on the ability (or lack thereof) to set GUCs. Imagine the effects of adding new GUCs, removing old ones, changing a GUC name, or tweaking the behavior slightly. It makes more sense to tie it to the role directly, so DDL. Also, you should put this in the context of previous discussions, which lead to the "ON ALL TABLES IN SCHEMA" feature in 9.0. In particular, that feature only affects existing objects, and you are trying to create some kind of permissions mask which will affect new objects, as well. Regards,Jeff Davis
On Tue, Sep 07, 2010 at 12:41:51PM -0700, Jeff Davis wrote: > On Tue, 2010-09-07 at 11:39 -0700, David Fetter wrote: > > We'd like to create a role called read_only, with eponymous > > capability. > > Seems useful. Great to hear :) > > If so, is it more > > DCL-ish, or more DDL-ish? > > I don't like the idea of a security model relying on the ability (or > lack thereof) to set GUCs. Imagine the effects of adding new GUCs, > removing old ones, changing a GUC name, or tweaking the behavior > slightly. Offhand, I'm not thinking of past examples of mutating/disappearing GUC that people would want to freeze, nor of a new GUC that would negate or substantially alter such freezing. What have I missed? > It makes more sense to tie it to the role directly, so DDL. There are still arguments for making it DCL-ish, in the sense that it is, at least in this case, viewable as a data control issue. > Also, you should put this in the context of previous discussions, which > lead to the "ON ALL TABLES IN SCHEMA" feature in 9.0. In particular, > that feature only affects existing objects, and you are trying to create > some kind of permissions mask which will affect new objects, as well. I guess I can see a case for making "read-only" non-global, but I think a good first try at it would be to make such "freezes" global. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, 2010-09-07 at 13:30 -0700, David Fetter wrote: > Offhand, I'm not thinking of past examples of mutating/disappearing > GUC that people would want to freeze, nor of a new GUC that would > negate or substantially alter such freezing. What have I missed? If you'll allow me to change my argument slightly, it just seems chaotic. We'd be introducing the 100+ GUCs all as potential security features, and it would (presumably) be up to the user whether they considered it a "security feature" or not. I think, in practice, that would confuse users about the security of the system, and we'd be more reluctant to change GUC behavior because someone, somewhere, might have considered it a part of their system's security. Perhaps someone will assume that they can prevent a user from performing joins by disabling and freezing enable_hashjoin/nestloop/mergejoin. Or perhaps someone will try to contain a user to a few schemas by freezing the search_path. Maybe this is a little far-fetched, but the point is that we are quite a ways away from blessing all GUCs with a word like "security". It just seems like the wrong mechanism. > > It makes more sense to tie it to the role directly, so DDL. > > There are still arguments for making it DCL-ish, in the sense that it > is, at least in this case, viewable as a data control issue. I would be more open to it if it didn't rely on GUCs at all. Regards,Jeff Davis
On Tue, Sep 07, 2010 at 02:43:12PM -0700, Jeff Davis wrote: > On Tue, 2010-09-07 at 13:30 -0700, David Fetter wrote: > > Offhand, I'm not thinking of past examples of mutating/disappearing > > GUC that people would want to freeze, nor of a new GUC that would > > negate or substantially alter such freezing. What have I missed? > > If you'll allow me to change my argument slightly, it just seems > chaotic. We'd be introducing the 100+ GUCs all as potential security > features, and it would (presumably) be up to the user whether they > considered it a "security feature" or not. I think, in practice, that > would confuse users about the security of the system, and we'd be more > reluctant to change GUC behavior because someone, somewhere, might have > considered it a part of their system's security. > > Perhaps someone will assume that they can prevent a user from performing > joins by disabling and freezing enable_hashjoin/nestloop/mergejoin. Or > perhaps someone will try to contain a user to a few schemas by freezing > the search_path. Maybe this is a little far-fetched, but the point is > that we are quite a ways away from blessing all GUCs with a word like > "security". > > It just seems like the wrong mechanism. OK :) > > > It makes more sense to tie it to the role directly, so DDL. > > > > There are still arguments for making it DCL-ish, in the sense that > > it is, at least in this case, viewable as a data control issue. > > I would be more open to it if it didn't rely on GUCs at all. There are two problems at hand here, as I see it: the more general problem of "freezing" settings for a given role, and the very specific capability of guaranteeing read-only-ness, which could have large implications in, for example, data warehousing and replication systems. Should we just call them separate problems, look into how to approach the latter one, and table the former? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, 2010-09-07 at 14:49 -0700, David Fetter wrote: > There are two problems at hand here, as I see it: the more general > problem of "freezing" settings for a given role, and the very specific > capability of guaranteeing read-only-ness, which could have large > implications in, for example, data warehousing and replication > systems. > > Should we just call them separate problems, look into how to approach > the latter one, and table the former? That sounds like a good plan. Right now, the only solid use case we have is "read only role", and it's difficult to build a (good) general mechanism from a single use case. Regards,Jeff Davis
Jeff Davis <pgsql@j-davis.com> writes:
> On Tue, 2010-09-07 at 13:30 -0700, David Fetter wrote:
>> Offhand, I'm not thinking of past examples of mutating/disappearing
>> GUC that people would want to freeze, nor of a new GUC that would
>> negate or substantially alter such freezing. What have I missed?
> It just seems like the wrong mechanism.
Yeah, it seems like an ugly and probably basically-wrong solution
to the given problem. And there are a ton of corner cases. For
example, if I "freeze" a user's search_path, what happens if the user
tries to call a function that has a search_path property attached? Does
it matter whether the function is owned by some other userid that maybe
doesn't have a freeze for that value? Similarly, if the user calls a
function that is SECURITY DEFINER to some other role that hasn't got the
freeze flag set, should that function be allowed to change the setting
internally, and if not why not?
For that matter, if user A owns a SECURITY DEFINER function that doesn't
try to set search_path, should a "freeze search_path" applied to user A
somehow result in implicit switches of search_path when that function is
invoked by user B? (Good luck making that one happen without
catastrophic performance degradation, because it would mean looking into
the system catalogs on every function call to see if this
action-at-a-distance should affect this function call.)
And none of this seems to have a lot to do with the original goal,
which IIUC was to make a session read-only, not the activities blamable
on a particular user identity.
regards, tom lane