6 ms·

> In the future we can connect these logs to slack so you can get slack alerts when a developer accidentally DROPs a table. What can you do about getting me a

> In the future we can connect these logs to slack so you can get slack alerts when a developer accidentally DROPs a table. What can you do about getting me a slack notice BEFORE a developer accidentally drops a table? ;)

kvaranasi_ 3y ago
Haha, we need Neuralink chips then.
alFReD-NSH 3y ago
Don't give them the DROP permission and have them go through a (automated) process to elevate their privilege when they need it.
kvaranasi_ 3y ago
Hey wait, you have a point though. We can put our custom locks onto tables that doesn't let anyone delete a particular table. Let me think over it. Saving this comment for later. Thanks for the idea man.
- dewey 3y ago
  Uhm, why would you not use PG permissions for that? That's what they are for.
  - kvaranasi_ 3y ago
    I am assuming that the developer has necessary permissions. If he/she accidentally deletes the table, then that's a problem.
    - dewey 3y ago
      That means that the process is wrong. it sounds like they are doing day to day work with credentials that can drop tables, which is dangerous and should probably be done through code-reviewed migration tooling.
      - kvaranasi_ 3y ago
        Ok, yes this makes sense.
  - kvaranasi_ 3y ago
    Of course I could be wrong. Please share with me your thoughts.
    - hobs 3y ago
      People using triggers for this just don't understand proper security - its a little harder but having your deployment process have the ability to drop tables and having your general user having read only query access (or insert powers on your own schema which nobody else can read) is stuff DBAs figured out in 90s and we keep forgetting.
      - gourabmi 3y ago
        What's a good place to start learning about these patterns ?
        hobs 3y ago
        Well, it depends on your database, but honestly the knowledge was handed down to me by database enthusiasts of the times before. I was studying SQL Server at the time, so Kendra Little, Brent Ozar, that whole crew were a good starting point. Once you learn the basics of the DCL for your database, a lot of the stuff just becomes a natural extension, and almost always you're just talking about the official documentation. Usually you have a server and a database level identity, and manipulating those usually break out the connection vs object level permissions. Once you understand the maps of those and grouping users, you can do stuff like detect a user did a bad thing and add them to a group of users who are default denied from the database, insert a row in a table, and roll back their changes. A logon trigger that does rude things to people's sessions, or certainty that your developers can view some parts of some data without exposing your secrets.
palijer 3y ago
Haha, exactly, but if you have developers who can drop production tables without the org knowing in advance and reviewing the change, you already have a big problem.
palijer 3y ago
Haha, exactly, but if you have developers who can drop production tables without the org knowing in advance and reviewing the change, you already have a big problem. All our db operations like this go through our regular code review process for the db maintainers team, and instead of dropping tables, we just rename them with a prefix "to_be_dropped" as a scream test, then actually drop them a month later or so. Recovery is much simpler for renaming than dropping.
- kvaranasi_ 3y ago
  This is a nice idea to check if any dependencies are there on the table.
  - palijer 3y ago
    Haha, yeah, it's a hack we figured out the hard way, but it is super simple and effective.
nswanberg 3y ago
Triboulet: "A noble has threatened to hang me!" The Monarch: "Don't worry! If he hangs you I'll have him beheaded fifteen minutes later." Triboulet: "Well, would it be possible to behead him 15 minutes before?
- kvaranasi_ 3y ago
  Hahahah. I literally Googled who Triboulet was.
codegeek 3y ago
"BEFORE a developer accidentally drops a table?" Ideally, you don't want developer(s) to have access to destruct data especially things like DROP. Now, a lot of small businesses may not have the resource to have separate DBAs etc but the rule still applies. This is more a control problem than a code problem. Having said that, you could write triggers to stop DELETE or DROPs from happening if you cannot control the access.
- kvaranasi_ 3y ago
  Interesting idea to use Triggers.
- tracker1 3y ago
  I've made it a personal rule for a while now, that I generally won't touch production once it is "online" ... I'll work initial setup, but after that, I'm happy to be on a screen sharing session walking someone else through what to do, so I don't have access and cannot touch it directly. Usually in favor of a CI/CD pipeline that will do regular releases. This at least lowers risk, but doesn't strictly prevent a DROP in a db/migration script. All the same, I don't touch prod servers, and especially don't touch client prod servers.
dom_hutton 3y ago
Modify your schema through migrations that go through code review :p
_flux 3y ago
Well, I guess you could use https://www.postgresql.org/docs/16/sql-createeventtrigger.html https://www.postgresql.org/docs/16/sql-createeventtrigger.ht... to trigger on sql_drop Maybe it could send a Slack message which you (some someone else) would need to react with (I think this site malforms that white checkmark unicode character though..) before it continues, or times out after a while with an error!
- kvaranasi_ 3y ago
  This is great. I plan on providing Postgres trigger templates that developers can easily load on Rocketgraph.
- kvaranasi_ 3y ago
  Postgres is one of the most amazing inventions that exit truly.
koliber 3y ago
If you want to find the developer who will drop your table by accident, ask your devs to raise their hands if they ever dropped a table by accident. Those that don’t raise their hand are the ones you watch carefully.
- tetha 3y ago
  Aye. A similar rite of passage: Any admin who hasn't nuked a server yet isn't working for long yet, or at their effective limit and is too careful. We all have a few of these. My first one was a jenkins node with a "mv $WORKSPACE/ $WORKSPACE/bin" and no "set -u" :) Or accidentially wiping all SSH keys from a server due to a typo in the config management. That was fun. But it's a good test for the mistake culture of the team and the resilience of their infrastructure. We had juniors nuke systems and no one but us and the monitoring noticed besides maybe a short hickup during a leader failover. That's pretty cool - and also a good horror story in a few words: "When you start ansible, and 3 minutes later on-call says 'Hi.' on slack".
- tracker1 3y ago
  Nope... but did check in the prod settings for the "local" config while working on a production bug close to the end of my contract. A couple weeks later another dev had dropped a production table as he'd used the local/dev connection string. Fortunately, every service request was logged and re-playable... so the DB was restored from backup, and every service request re-ran in order. Lost about a day of productivity for everyone using the app/service. As to the config, this at least was pretty common for Web.*.config files in the .Net space in the mid 00s. I was never a fan of it, and even with the newer options in the space, prefer simpler environment variables these days.
deleted 3y ago
[deleted]
lazyant 3y ago
Like others have said, this is a control problem in prod (not using migrations in code and renaming instead of dropping). Anyways, as an fun exercise to get exactly what's asked, I wonder if this could be used https://tembo.io/blog/introducing-pg-later/ https://tembo.io/blog/introducing-pg-later/ together with a trigger to trap the DROP, alert on Slack and run later if not aborted.
- jrockway 3y ago
  Migrations are not your safety net, backups are. Migrations, like all code, are full of bugs.
deleted 3y ago
[deleted]

kvaranasi_ 3y ago

Haha, we need Neuralink chips then.

alFReD-NSH 3y ago

Don't give them the DROP permission and have them go through a (automated) process to elevate their privilege when they need it.

kvaranasi_ 3y ago

Hey wait, you have a point though. We can put our custom locks onto tables that doesn't let anyone delete a particular table. Let me think over it. Saving this comment for later. Thanks for the idea man.

dewey 3y ago
Uhm, why would you not use PG permissions for that? That's what they are for.
- kvaranasi_ 3y ago
  I am assuming that the developer has necessary permissions. If he/she accidentally deletes the table, then that's a problem.
  - dewey 3y ago
    That means that the process is wrong. it sounds like they are doing day to day work with credentials that can drop tables, which is dangerous and should probably be done through code-reviewed migration tooling.
    - kvaranasi_ 3y ago
      Ok, yes this makes sense.
- kvaranasi_ 3y ago
  Of course I could be wrong. Please share with me your thoughts.
  - hobs 3y ago
    People using triggers for this just don't understand proper security - its a little harder but having your deployment process have the ability to drop tables and having your general user having read only query access (or insert powers on your own schema which nobody else can read) is stuff DBAs figured out in 90s and we keep forgetting.
    - gourabmi 3y ago
      What's a good place to start learning about these patterns ?
      - hobs 3y ago
        Well, it depends on your database, but honestly the knowledge was handed down to me by database enthusiasts of the times before. I was studying SQL Server at the time, so Kendra Little, Brent Ozar, that whole crew were a good starting point. Once you learn the basics of the DCL for your database, a lot of the stuff just becomes a natural extension, and almost always you're just talking about the official documentation. Usually you have a server and a database level identity, and manipulating those usually break out the connection vs object level permissions. Once you understand the maps of those and grouping users, you can do stuff like detect a user did a bad thing and add them to a group of users who are default denied from the database, insert a row in a table, and roll back their changes. A logon trigger that does rude things to people's sessions, or certainty that your developers can view some parts of some data without exposing your secrets.

palijer 3y ago

Haha, exactly, but if you have developers who can drop production tables without the org knowing in advance and reviewing the change, you already have a big problem.

palijer 3y ago

Haha, exactly, but if you have developers who can drop production tables without the org knowing in advance and reviewing the change, you already have a big problem. All our db operations like this go through our regular code review process for the db maintainers team, and instead of dropping tables, we just rename them with a prefix "to_be_dropped" as a scream test, then actually drop them a month later or so. Recovery is much simpler for renaming than dropping.

kvaranasi_ 3y ago
This is a nice idea to check if any dependencies are there on the table.
- palijer 3y ago
  Haha, yeah, it's a hack we figured out the hard way, but it is super simple and effective.

nswanberg 3y ago

Triboulet: "A noble has threatened to hang me!" The Monarch: "Don't worry! If he hangs you I'll have him beheaded fifteen minutes later." Triboulet: "Well, would it be possible to behead him 15 minutes before?

kvaranasi_ 3y ago
Hahahah. I literally Googled who Triboulet was.

codegeek 3y ago

"BEFORE a developer accidentally drops a table?" Ideally, you don't want developer(s) to have access to destruct data especially things like DROP. Now, a lot of small businesses may not have the resource to have separate DBAs etc but the rule still applies. This is more a control problem than a code problem. Having said that, you could write triggers to stop DELETE or DROPs from happening if you cannot control the access.

kvaranasi_ 3y ago
Interesting idea to use Triggers.
tracker1 3y ago
I've made it a personal rule for a while now, that I generally won't touch production once it is "online" ... I'll work initial setup, but after that, I'm happy to be on a screen sharing session walking someone else through what to do, so I don't have access and cannot touch it directly. Usually in favor of a CI/CD pipeline that will do regular releases. This at least lowers risk, but doesn't strictly prevent a DROP in a db/migration script. All the same, I don't touch prod servers, and especially don't touch client prod servers.

dom_hutton 3y ago

Modify your schema through migrations that go through code review :p

_flux 3y ago

Well, I guess you could use https://www.postgresql.org/docs/16/sql-createeventtrigger.html https://www.postgresql.org/docs/16/sql-createeventtrigger.ht... to trigger on sql_drop Maybe it could send a Slack message which you (some someone else) would need to react with (I think this site malforms that white checkmark unicode character though..) before it continues, or times out after a while with an error!

kvaranasi_ 3y ago
This is great. I plan on providing Postgres trigger templates that developers can easily load on Rocketgraph.
kvaranasi_ 3y ago
Postgres is one of the most amazing inventions that exit truly.

koliber 3y ago

If you want to find the developer who will drop your table by accident, ask your devs to raise their hands if they ever dropped a table by accident. Those that don’t raise their hand are the ones you watch carefully.

tetha 3y ago
Aye. A similar rite of passage: Any admin who hasn't nuked a server yet isn't working for long yet, or at their effective limit and is too careful. We all have a few of these. My first one was a jenkins node with a "mv $WORKSPACE/ $WORKSPACE/bin" and no "set -u" :) Or accidentially wiping all SSH keys from a server due to a typo in the config management. That was fun. But it's a good test for the mistake culture of the team and the resilience of their infrastructure. We had juniors nuke systems and no one but us and the monitoring noticed besides maybe a short hickup during a leader failover. That's pretty cool - and also a good horror story in a few words: "When you start ansible, and 3 minutes later on-call says 'Hi.' on slack".
tracker1 3y ago
Nope... but did check in the prod settings for the "local" config while working on a production bug close to the end of my contract. A couple weeks later another dev had dropped a production table as he'd used the local/dev connection string. Fortunately, every service request was logged and re-playable... so the DB was restored from backup, and every service request re-ran in order. Lost about a day of productivity for everyone using the app/service. As to the config, this at least was pretty common for Web.*.config files in the .Net space in the mid 00s. I was never a fan of it, and even with the newer options in the space, prefer simpler environment variables these days.

deleted 3y ago

[deleted]

lazyant 3y ago

Like others have said, this is a control problem in prod (not using migrations in code and renaming instead of dropping). Anyways, as an fun exercise to get exactly what's asked, I wonder if this could be used https://tembo.io/blog/introducing-pg-later/ https://tembo.io/blog/introducing-pg-later/ together with a trigger to trap the DROP, alert on Slack and run later if not aborted.

jrockway 3y ago
Migrations are not your safety net, backups are. Migrations, like all code, are full of bugs.

deleted 3y ago

[deleted]