On Wed, Oct 12, 2022 at 4:59 PM Nathan Bossart <nathandboss...@gmail.com> wrote: > I'm not sure about tying the ownership stuff to this new SET privilege. > While you noted some practical advantages, I'd expect users to find it kind > of surprising. Also, for predefined roles, I think you need to be careful > about distributing ADMIN, as anyone with ADMIN on a predefined role can > just GRANT SET to work around the restrictions. I don't have a better > idea, though, so perhaps neither of these things is a deal-breaker.
Right, I think if you give ADMIN away to someone, that's it: they can grant that role to whoever they want in whatever mode they want, including themselves. That seems more or less intentional to me, though. Giving someone ADMIN OPTION on a role is basically making them an administrator of that role, and then it is not surprising that they can access its privileges. I agree with your other caveat about it being potentially surprising, but I think it's not worse than a lot of other somewhat surprising things that we handle by documenting them. And I don't have a better idea either. > I was > tempted to suggest using ADMIN instead of SET for the ownership stuff, but > that wouldn't be backward-compatible, and you'd still be able to work > around it to some extent with SET (e.g., SET ROLE followed by CREATE > DATABASE). I think that would be way worse. Giving ADMIN OPTION on a role is like making someone the owner of the object, whereas giving someone INHERIT or SET on a role is just a privilege to use the object. -- Robert Haas EDB: http://www.enterprisedb.com