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

Search Discussions

  • Jeff Davis at Sep 7, 2010 at 7:42 pm

    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
  • David Fetter at Sep 7, 2010 at 8:41 pm

    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
  • Jeff Davis at Sep 7, 2010 at 9:43 pm

    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
  • David Fetter at Sep 7, 2010 at 9:49 pm

    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
  • Jeff Davis at Sep 7, 2010 at 10:23 pm

    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
  • Tom Lane at Sep 7, 2010 at 11:44 pm

    Jeff Davis 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 7, '10 at 6:39p
activeSep 7, '10 at 11:44p
posts7
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase