search

karenc / db-migrator

GNU Affero General Public License v3.0
4 stars 5 forks source link

Need mechanism to request db superuser connection #38

Closed reedstrm closed 7 years ago

reedstrm commented 7 years ago

There are some schema changes that can only be done by a db superuser (e.g. creating functions in an untrusted language, like plpythonu) However, it's bad practice to make all your connections as superuser. I propose a superuser() context manager script, over in https://github.com/Connexions/cnx-db/pull/23 and @pumazi suggested it should probably properly live in db-migrator proper. If so, I'd add some sort of option to provide a different super-user name (defaulting to postgres) at runtime.

reedstrm commented 7 years ago

BTW, this woulds still be useful ;-)

reedstrm commented 7 years ago

Code is in: https://github.com/Connexions/cnx-db/pull/23 , which uses the "must exist" superuser postgres. Probably better to allow for a configurable user for that , defaulting to postgres.

karenc commented 7 years ago

@reedstrm The only problem I see with this approach is it is a new connection, if what runs in the superuser() block depends on what happens before it, it's not going to be committed to the database yet (I think?). We might have a partially migrated database as well, because the superuser() stuff might succeed while the code after that might fail and not get committed.

karenc commented 7 years ago

But that's something to keep in mind when writing the migrations, implementing superuser() in db-migrator isn't a problem.