0

Based on the example below, it seems like it would not be possible?

Let us say you have two tables in your PostgreSQL database (D): table A and table B. The policy is that anyone can read or insert into table A and table B. The PostgreSQL database (D) exists on a PostgreSQL database server (S)

You have a version 1.00 of a Python server (Blue) that connects to that PostgreSQL database (D) via the PostgreSQL server (S).

You deploy the new version (1.10) of your Python server (Green). It connects to the same PostgreSQL database (D) via the same PostgreSQL server (S). In the process of deploying the new version of the Python server, you change the policy via a migration. Now, anyone can only read or insert into table A.

Hence, by the end of this deployment, you have two versions of the Python server running (Blue and Green), each of which is connected to the same PostgreSQL database (D) via the same PostgreSQL server (S). As covered above, the PostgreSQL database (D) now has a policy that only allows reading or inserting on table A. The ability to read or insert into table B is now eliminated.

One user attempts to insert into table B via the (Blue) Python server. It will get a permission error because it is trying to insert into table B but the database policy does not allow for that. This is a breaking change.

If the authorization was provided by the server, and not by the database, it would seem this problem would go away.

  • Blue would allow the insert into table B
  • Green would not allow the insert into table B

Hence, no breaking change would occur.

6
  • 1
    I find your example hard to follow unfortunately. Specifically, what does this even mean? - "Both Green and Blue run at the same time for an hour.". If you change a Row-Level Security policy and / or its logic, then whatever changes you make are immediate. There is no lingering old version.
    – J.D.
    May 25, 2023 at 21:38
  • I edited the question to hopefully make it clearer! Basically, the two versions of the server are running at the same time. And that was what I figured - there does not seem to be a good way to apply different versions of the policies to different versions of the server. May 25, 2023 at 22:18
  • So the "server" is something that is not the "database"? Since row-level security is defined in the database, no matter how many servers of what colour connect to that database, they will all be subject to the same policy, no?
    – mustaccio
    May 25, 2023 at 23:56
  • I think to mustaccio's point, could you please clarify your database instance architecture?...do you have 2 database servers?...if so, do they each have a copy of the same database? Or are there two database servers, and one accesses the database that lives on the other through some sort of means like a Linked Server? Row-Level Security is an object that lives at the database level, as mustaccio mentioned.
    – J.D.
    May 26, 2023 at 0:28
  • Yup! I updated the question for more context on the architecture. But to restate - one database within one database server that two Python servers of different versions (Blue and Green) connect to. And mustaccio I agree with the question you posed at the end of your comment. May 26, 2023 at 1:16

1 Answer 1

1

one database within one database server that two Python servers of different versions (Blue and Green) connect to.

Ok, understood, you have two application servers connecting to the same database server and same database.

One user attempts to insert into table B via the (Blue) Python server. It will get a permission error because it is trying to insert into table B but the database policy does not allow for that. This is a breaking change.

Row-Level Security allows you to define the logic you want to use to allow or deny DQL and DML statements against an object, e.g. table. Usually that logic is based on the users connecting to the database. But, in your case, it sounds like you want it to be dependent on the application server connecting to the database, which is also do-able.

Speaking about SQL Server specifically (but I'm sure PostgreSQL has a similar methodology), there's a system function called HOST_NAME() which will return the name of the machine that's connected to the database server. In your case, this would be the Python application servers.

In your Row-Level Security logic, you would check that function to determine if the server connecting has access or not. I'd recommend storing the Python server names in a secured table, so that they can be managed more easily (as opposed to hard-coding them in the Row-Level Security policy).

This would allow you to allow one Python server access, while denying the other, in real time.

2
  • OK, great - that was along the line I was thinking - conditional row level security policies based on the server - thanks for the help! May 26, 2023 at 1:57
  • @rationaltiger24 No problem! That's pretty much the whole point of Row-Level Security, is to be conditional on some data criteria.
    – J.D.
    May 26, 2023 at 1:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.