On Tue, Jan 10, 2023 at 8:47 PM Tom Lane <t...@sss.pgh.pa.us> wrote: > [ squint ... ] Are you sure it's not a security *hazard*, though?
I think you have to squint pretty hard to find a security hazard here. The effect of this GUC is to control the set of privileges that a CREATEROLE user automatically grants to themselves. But granting yourself privileges does not normally lead to any sort of security privilege. It's not completely impossible, I believe. For example, suppose that I, as a CREATEROLE user who is not a superuser, execute "CREATE ROLE shifty". I then set up a schema that shifty can access and I cannot. I then put that schema into my search_path despite the fact that I haven't given myself access to it. Now, depending on the value of this setting, I might either implicitly inherit shifty's privileges, or I might not. So, if I was expecting that I wouldn't, and I do, then I have now created a situation where if I do more dumb things I could execute some shifty code that lets that shifty user take over my account. But, you know, if I'm that dumb, I could also hit myself in the head with a hammer and the shifty guy could use the fact that I'm unconscious to fish the sticky note out of my wallet where, presumably, I keep my database password. The bigger point here, I think, is that this GUC only controls default privileges -- and we already have a system for default privileges that allows any user to give away privileges on virtually any object that they create to anyone. Nothing about that system is superuser-only. This system is far more restricted in its scope. It only allows you to give privileges to yourself, not anyone else, and only if you're a CREATEROLE user who is not a SUPERUSER. It seems a bit crazy to say that it's not a hazard for Alice to automatically grant every permission in the book to Emil every time she creates a table or schema or type or sequence or a function, but it is a hazard if Bob can grant INHERIT and SET to himself on roles that he creates. That said, in my original design, this was controlled via a different mechanism which was superuser-only. I was informed that made no sense, so I changed it. Now here we are. -- Robert Haas EDB: http://www.enterprisedb.com