D
D
Dannerty2019-04-05 10:03:36
PostgreSQL
Dannerty, 2019-04-05 10:03:36

How to configure row protection policy in PostgreSQL?

Hello. Tell me, is it possible to implement the protection of rows in tables in my case?
There is a productive server in which there is a table of settings, this table stores the settings of all additional servers, indicating the UUID of the server in the "Server" field.
There are also schema-servers for each of the servers, which have the same settings table, but it stores the settings of only a particular server.
User-mapping is set to one account for all servers.
I would like to somehow limit the change of rows from the database of servers in the productive database that are not related to a specific server.
Something like

CREATE POLICY security_ros ON settings_table
  USING (server = (select server from settings_table group by server))

This option does not work, because apparently the condition is executed on a productive server, and there returns more than one line.
Is it possible to set up a similar policy, but to use individual server tables?
Ideally, it should look like this, well, it seems to me:
1. On a productive server, the settings of all other servers are stored in the 'settings_table' table, the uuid of all the others are indicated in the 'server' field.
2. On the server 'server_1' in the 'settings_table' table there are only settings related to this server, i.e. there is only one uuid in the 'server' field, there are no others.
3. The server 'server_1' makes updating its settings in its database and productive:
update settings_table set value = 1 where parameter = 'pararmeter'
update product_server.settings_table set value = 1 where parameter = 'parameter'

4. When executing a request to update the settings on the production server, a filter that uses the uuid from the server table 'server_1' should work, because it is unique there, and will only allow you to change the setting that applies to the given uuid, without changing the others with the same 'parameter' field name.
By analogy with a similar request:
update product_server.settings_table set value = 1 where parameter = 'parameter' and server = 'uuid_server'

This is necessary so that from each individual server you can view or change the settings in the base of the productive server, only those that apply to it.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question