Require membership to `privileged_role` to create extensions under `privileged_extensions`

[soedirgo]

This is for consistency with other privileged_role features, such as creating publications, FDWs, etc.

[steve-chavez]

This would remove the need for the supautils.privileged_extensions whitelist right? Only membership to privileged_role would be required.

[soedirgo]

We still need supautils.privileged_extensions because there are some superuser-only extensions we purposefully exclude from it.

[steve-chavez]

supautils.privileged_extensions because there are some superuser-only extensions we purposefully exclude from it.

Which ones? In that case I think it'd be better to have a blacklist approach.

[soedirgo]

Better to be explicit; a blacklist approach risks us forgetting to add stuff in and expose us to privesc vulnerabilities.

[steve-chavez]

Better to be explicit; a blacklist approach risks us forgetting to add stuff in and expose us to privesc vulnerabilities.

If each time a new extension is supported you have to add it to this long list, then that doesn't create more "consciousness" at all.

It'd be much better to have something like supautils.superuser_only_extensions and be conscious if a new extension has to be added there. Additionally, that would unlock more possibilities. Maybe we can identify what are the things that enable privesc on the extensions (say, it has security definer functions) automatically, requiring that those extensions be added to supautils.superuser_only_extensions.

That would be much more useful than what pgextwlist offers. And would reduce complexity of the deployments, like on https://github.com/supabase/postgres (which is already complex).

---

If the whitelist is not gone, then what would be the benefit of this issue?

So right now any user can create an extension, not just the privileged_role? Why is that a problem?

[soedirgo]

If each time a new extension is supported you have to add it to this long list, then that doesn't create more "consciousness" at all.

It does, because you wouldn't be able to CREATE EXTENSION it without the extension being trusted=true or superuser=false. You're forced to be aware of the config because the extension won't work without adding it there.

If the whitelist is not gone, then what would be the benefit of this issue?

So right now any user can create an extension, not just the privileged_role? Why is that a problem?

As you said here it's just for consistency with how the other pseudo-superuser configs work. I don't see it as a problem atm, and if it does we can cross that bridge when we get to it. I don't think this is important right now and am fine with just closing this issue.

[steve-chavez]

Might be possible to fix #123 with just GetOuterUserId(), but this issue would be a prereq.

[steve-chavez]

This is a problem currently because the CREATE EXTENSION supautils logic is also applied to superusers. It's not ignored for super as the rest of the functionality.

[soedirgo]

It needs to run as superuser, or at least the extension custom scripts need to run unconditionally. We need to be able to assert invariants based on what’s in these scripts (e.g. applying grants)

[steve-chavez]

We need to be able to assert invariants based on what’s in these scripts (e.g. applying grants)

Why does superuser need to apply grants? It's the privileged role the one that the user will use to create extension.

On which cases there's reliance on superuser creating extensions?

[soedirgo]

For example this revoke needs to run on extension creation whether the user running CREATE EXTENSION is superuser or not. If it's skipped we risk exposing vulnerabilities.

For supautils.extensions_parameter_overrides (e.g. for ensuring pg_cron is always created in the pg_catalog schema) it's not strictly necessary as long as we're careful, but it's still better to be safe and make unhappy states impossible to get to.