3 ms·

I wish they had a more declarative way for provisioning (and de provisioning) users and databases. I'd like to define these at startup and have them reconciled

I wish they had a more declarative way for provisioning (and de provisioning) users and databases. I'd like to define these at startup and have them reconciled automatically without having to do a deployment+psql dance to manage them.

convolvatron 3y ago
that's kind of an odd thing to say. all of that state is really held in Postgres tables. all of the various tools just manipulate those. you could quite trivially write a tool that would take a big yaml or something and splat that.
- mh- 3y ago
  they could, but I can hardly begrudge them wishing for first-party tooling to do that.
  - sbuttgereit 3y ago
    I'm not sure I understand the requirement here. Something different than (below)? https://www.postgresql.org/docs/current/app-createdb.html https://www.postgresql.org/docs/current/app-createdb.html https://www.postgresql.org/docs/current/app-createuser.html https://www.postgresql.org/docs/current/app-createuser.html (others https://www.postgresql.org/docs/current/reference-client.html https://www.postgresql.org/docs/current/reference-client.htm...) I never use these myself, but it seems there are ways to get there.
- candiddevmike 3y ago
  I'd rather PostgreSQL handle this so I don't have to create a privileged user and write a custom reconcilation loop.
klysm 3y ago
My initial reaction was in agreement, but you’re almost certainly going to need a mechanism to manage schema changes. It makes sense to use the same system for both
- nijave 3y ago
  Schema changes happen inside a database on the server but usually you need the DB and user provisioned first (generally using a superuser and the existing/default DB) You can use the same tool, however it needs to connect to 1 DB, do the DB creation and role setup, then reconnect to a new DB. If you have a single app or DB in a server, it can make sense to usually schema management but it's more complicated if multiple apps have their own DBs sharing the same server. The last few places I worked had scripts that ran before schema management as a superuser then used the application framework schema management (migrations) thereafter.
  - klysm 3y ago
    Yeah I have the same awkward process for provisioning databases. Another difficulty is configuration of passwords/auth which varies by server typically. Generally speaking I don’t want my migration process to have the ability to assume the super user role. The variance in the script prevents using the typical migration frameworks built into the app, and it’s the wrong responsibility - the app migrations have a contract with whatever database is being connected to about which users and schemas exist. I’m still not sure a declarative mechanism is desirable though because the database “setup” is still subject to change after initial provisioning: new schemas/roles/etc will be required and we need an imperative API to execute those changes transactionally with precision.

convolvatron 3y ago

that's kind of an odd thing to say. all of that state is really held in Postgres tables. all of the various tools just manipulate those. you could quite trivially write a tool that would take a big yaml or something and splat that.

mh- 3y ago
they could, but I can hardly begrudge them wishing for first-party tooling to do that.
- sbuttgereit 3y ago
  I'm not sure I understand the requirement here. Something different than (below)? https://www.postgresql.org/docs/current/app-createdb.html https://www.postgresql.org/docs/current/app-createdb.html https://www.postgresql.org/docs/current/app-createuser.html https://www.postgresql.org/docs/current/app-createuser.html (others https://www.postgresql.org/docs/current/reference-client.html https://www.postgresql.org/docs/current/reference-client.htm...) I never use these myself, but it seems there are ways to get there.
candiddevmike 3y ago
I'd rather PostgreSQL handle this so I don't have to create a privileged user and write a custom reconcilation loop.

klysm 3y ago

My initial reaction was in agreement, but you’re almost certainly going to need a mechanism to manage schema changes. It makes sense to use the same system for both

nijave 3y ago
Schema changes happen inside a database on the server but usually you need the DB and user provisioned first (generally using a superuser and the existing/default DB) You can use the same tool, however it needs to connect to 1 DB, do the DB creation and role setup, then reconnect to a new DB. If you have a single app or DB in a server, it can make sense to usually schema management but it's more complicated if multiple apps have their own DBs sharing the same server. The last few places I worked had scripts that ran before schema management as a superuser then used the application framework schema management (migrations) thereafter.
- klysm 3y ago
  Yeah I have the same awkward process for provisioning databases. Another difficulty is configuration of passwords/auth which varies by server typically. Generally speaking I don’t want my migration process to have the ability to assume the super user role. The variance in the script prevents using the typical migration frameworks built into the app, and it’s the wrong responsibility - the app migrations have a contract with whatever database is being connected to about which users and schemas exist. I’m still not sure a declarative mechanism is desirable though because the database “setup” is still subject to change after initial provisioning: new schemas/roles/etc will be required and we need an imperative API to execute those changes transactionally with precision.