Re: CREATE ROLE IF NOT EXISTS

Greetings,

* Mark Dilger (mark(dot)dilger(at)enterprisedb(dot)com) wrote:
> > On Nov 9, 2021, at 7:36 AM, David Christensen <david(dot)christensen(at)crunchydata(dot)com> wrote:
> > If CINE semantics are at issue, what about the CREATE OR REPLACE semantics with some sort of merge into the existing role? I don't care strongly about which approach is taken, just think the overall "make this role exist in this form" without an error is useful in my own work, and CINE was easier to implement as a first pass.
>
> CREATE OR REPLACE might be a better option, not with the "merge into the existing role" part, but rather as drop+create. If a malicious actor has already added other roles to the role, or created a table with a malicious trigger definition, the drop part will fail, which is good from a security viewpoint. Of course, the drop portion will also fail under other conditions which don't entail any security concerns, but maybe they could be addressed in a series of follow-on patches?
>
> I understand this idea is not as useful for creating idempotent scripts, but maybe it gets you part of the way there?

If it's actually drop+create then, no, that isn't really useful because
it'll fail when that role owns objects (see my other email). If we can
avoid that issue then CREATE OR REPLACE might work, we just need to make
sure that we document what is, and isn't, done in such a case.

Thanks,

Stephen