How can specific fields be blocked in Postgres…

It is not possible to block specific table columns directly in PostgreSQL. Locks are applied to objects such as tables, rows, transactions, etc., but not to individual columns.

To achieve isolation or access control at the column level, the following approaches are used:

Row-level locking: Locking the entire row containing the relevant columns. This is a standard SQL mechanism.

SELECT * FROM my_table WHERE id = 1 FOR UPDATE;
// Locks the row with id=1 for any other UPDATE or DELETE operations

Views with restrictions: Creating views that do not include sensitive columns, and granting users or applications permissions only on these views.
```
CREATE VIEW allowed_view AS
SELECT id, field1, field2 FROM sensitive_table;

GRANT SELECT ON allowed_view TO restricted_user;
```

Rules: Using rules to intercept (INSERT, UPDATE, DELETE) operations and prevent their execution if they affect specific columns. Rules can be complex and impact performance.

CREATE RULE no_update_sensitive_field AS
ON UPDATE TO my_table
WHERE NEW.sensitive_field IS DISTINCT FROM OLD.sensitive_field
DO INSTEAD NOTHING;
// Prevents updating sensitive_field

Triggers: Creating trigger functions and triggers on the table that check for changes in columns and cancel the operation or record changes. Triggers are more flexible than rules.

CREATE OR REPLACE FUNCTION check_sensitive_update()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.sensitive_field IS DISTINCT FROM OLD.sensitive_field THEN
    RAISE EXCEPTION 'Cannot modify sensitive_field';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_sensitive_update
BEFORE UPDATE ON my_table
FOR EACH ROW
EXECUTE FUNCTION check_sensitive_update();

Splitting columns into separate tables: Moving sensitive columns into a separate table with stricter access rights.

CREATE TABLE main_table (
    id SERIAL PRIMARY KEY,
    field1 VARCHAR
);

CREATE TABLE sensitive_data (
    id INT PRIMARY KEY REFERENCES main_table(id),
    sensitive_field VARCHAR
);

GRANT SELECT ON main_table TO app_user;
GRANT SELECT ON sensitive_data TO admin_user;

The choice of method heavily depends on the specific task: whether a temporary lock during a transaction is needed, a permanent restriction on modification, or visibility control. Usually, views and triggers are used for access control and business logic, while row locks are used to ensure data integrity during concurrent access.

How can specific fields be blocked in Postgres?