PostgreSQL Database Security – Prevent Column Writing

postgresql

I am using a Postgres db. I am concerned if someone gets in my DB, they could change the price of my products to $0 and download my products.

Am I able to prevent writing / editing of that column except by a specific user?

I'm in a rails app, if it matters.

Best Answer

I am concerned if someone gets in my DB

Well, don't let them.

Am I able to prevent writing / editing of that column except by a specific user?

If your tables aren't owned by the user the application runs as, then yes, you can use column-level GRANTs to give partial access to tables.

So you'd GRANT everyone the right to use the other columns, and only grant one particular database user the right to edit that particular column.

However:

  • This only works with database users created with CREATE USER or CREATE ROLE. If your application does its own user management with ordinary database tables, PostgreSQL's permissions cannot be used.

  • Rails likes to have the application user own the tables so it can do migrations etc. You cannot restrict the rights of the owner.

... so in practice, I suspect that the answer will be "no, not with Rails, do it at the application level".

There's not really enough detail here to say more.

Related Question