3 ms·

> To make it easier to take advantage of PostgreSQL’s extensibility, PostgreSQL 13 adds the concept of a "trusted extension," which allows database users to ins

> To make it easier to take advantage of PostgreSQL’s extensibility, PostgreSQL 13 adds the concept of a "trusted extension," which allows database users to install extensions that a superuser marks as "trusted." Certain built-in extensions are marked as trusted by default, including pgcrypto, tablefunc, hstore, and more. Wow, that should make setting up a new installation easier. It looks like trigrams are marked as trusted (my favorite extension). I think this is the full list: https://blog.dbi-services.com/postgresql-13-trusted-extensions/ https://blog.dbi-services.com/postgresql-13-trusted-extensio...

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.
claytonjy 6y ago
This is great; no more need for pgextwlist then? Would have saved me a day or two quite recently...

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.

claytonjy 6y ago

This is great; no more need for pgextwlist then? Would have saved me a day or two quite recently...