3 ms·

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

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.

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.