2 ms·
Yep. Every time I've tried to run pg_restore as a non-superuser (e.g. against a DBaaS instance, where only the cloud-host's ops staff has true superuser privile
by derefr 6y ago
Yep. Every time I've tried to run pg_restore as a non-superuser (e.g. against a DBaaS instance, where only the cloud-host's ops staff has true superuser privileges), the inclusion of CREATE EXTENSION commands in the restore stream blocks the whole process. IIRC they added a specific flag in pg_restore to skip CREATE EXTENSION calls, just so people could do this. Now – given that in DBaaS environments, "trusted" extensions would be the only ones exposed to the user anyway — you'll likely be able to pg_restore as an arbitrary user in these envs with ease!
But that's not the only gain from this. The need to be a superuser to CREATE EXTENSION was the last thing standing in the way of many SQL client applications initializing their own schema objects (i.e. running DDL migrations) as a limited user (e.g. one with DDL privileges limited to a particular schema.)
-----
This is the first time that I feel PG is maybe approaching the point where users can truly "do their own thing" within the DB, without a superuser-privileged account interceding to help them along.
As such, PG13 will be a big boon for DB data security... a few versions from now, when it's old enough to be mandated as the minimum requirement for these stacks :)
Right now, apps are either deployed to use a PG user with ~full privileges over the DB (even if it's not technically a superuser), where migrations are run automatically on runtime startup, as the same account that accesses data at runtime (exploit vector!); or apps are deployed to use a DML-only user in prod, but migrations get run manually by ops staff under a separate, fully-privileged user/superuser.
I bet that, once people start to depend on this change, we'll start to see applications that offer the ability to configure them with one migration-time DB user, and a separate runtime DB user; neither of which are fully-privileged; and where the migration-running happens in a separate context (e.g. as a sidecar container) designed to allow migrations to be run automatically, while isolating the migration-time DB creds from the runtime environment.