[Feature request] Worker nodes read only mode

[Demy076]

• Feature Request: Read-Only Mode for Worker Nodes in PostgreSQL Citus

Description:
Implement a read-only mode for worker nodes in PostgreSQL Citus to enhance security and data integrity. This feature should ensure that worker nodes can only read data, but cannot perform any write operations.

Background:
In distributed PostgreSQL Citus clusters, worker nodes handle significant amounts of data processing. In certain scenarios, it is crucial to ensure that these worker nodes do not alter the data they are processing. This prevents unintended modifications and improves the security posture of the system, especially when dealing with sensitive or critical data. By implementing a read-only mode, we can restrict the write access of worker nodes without affecting their ability to perform read operations. Primary concern is that when some servers host web servers that make use of this said Postgres CITUS cluster, that if the database is at risk of a security leak, that none of the workers can modify the data also.

Acceptance Criteria:

1. A configuration option to enable read-only mode on worker nodes.
2. Worker nodes in read-only mode should be able to:

• Execute SELECT queries.
• Participate in distributed queries as read-only participants.

3. Worker nodes in read-only mode should be restricted from:

• Executing INSERT, UPDATE, DELETE, and other data modification commands.
• Performing schema changes.

4. Documentation updates to include:

• How to enable and configure read-only mode.
• Limitations and expected behavior when read-only mode is enabled.
• Use cases and scenarios for enabling read-only mode.

5. The ability to configure read-only mode on a per-node basis.
6. Runtime configuration changes should only be executed by the DDL coordinator (primary coordinator).

Customization and Configuration:

Configuring Read-Only Mode

1. Enabling Read-Only Mode Globally:

To enable read-only mode for all worker nodes in the cluster, add the following setting to the postgresql.conf file on each worker node:

# Enable read-only mode
citus.read_only_mode = 'on'

Alternatively, this setting can be applied at runtime using the following SQL command, which should only be executed by the DDL coordinator (primary coordinator):

ALTER SYSTEM SET citus.read_only_mode = 'on';
SELECT pg_reload_conf();

2. Enabling Read-Only Mode Per Node:

To enable read-only mode on specific worker nodes, use the following steps:

1. Node-Level Configuration:
   Add the read-only setting in the postgresql.conf file of the specific worker node:

# Enable read-only mode on this specific worker node
citus.read_only_mode = 'on'

2. Runtime Configuration:
   If a more dynamic configuration is required, use the following SQL command on the specific worker node, executed by the DDL coordinator (primary coordinator):

ALTER SYSTEM SET citus.read_only_mode = 'on';
SELECT pg_reload_conf();

3. Advanced Customization:

For more granular control, consider implementing a role-based approach where specific roles have read-only access. This can be combined with the node-level configuration for added security:

-- Create a read-only role
CREATE ROLE readonly_role WITH LOGIN;

-- Grant read-only permissions to the role
GRANT CONNECT ON DATABASE mydatabase TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;

Assign this role to users or applications that require read-only access.

Use Cases:

1. Data Analytics: When worker nodes are used for analytical queries that do not require data modification.
2. Reporting: Ensuring that reporting queries do not alter the underlying data.
3. Security: Enhancing security by preventing data tampering on worker nodes processing sensitive information.

Additional Information:

• This feature should be backward compatible with existing configurations where read-only mode is not enabled.
• Performance impacts should be considered and minimized.
• The implementation should be thoroughly documented to guide users on configuring and using the feature effectively.

References:

• PostgreSQL Citus documentation
• Security best practices for distributed databases---

By implementing these features and providing detailed configuration options, PostgreSQL Citus can offer enhanced security and data integrity for distributed systems.

You:

• Feature Request: Read-Only Mode for Worker Nodes in PostgreSQL Citus
  Description:
  Implement a read-only mode for worker nodes in PostgreSQL Citus to enhance security and data integrity. This feature should ensure that worker nodes can only read data, but cannot perform any write operations.

Background:
In distributed PostgreSQL Citus clusters, worker nodes handle significant amounts of data processing. In certain scenarios, it is crucial to ensure that these worker nodes do not alter the data they are processing. This prevents unintended modifications and improves the security posture of the system, especially when dealing with sensitive or critical data. By implementing a read-only mode, we can restrict the write access of worker nodes without affecting their ability to perform read operations.

Acceptance Criteria:

A configuration option to enable read-only mode on worker nodes.
Worker nodes in read-only mode should be able to:
Execute SELECT queries.
Participate in distributed queries as read-only participants.
Worker nodes in read-only mode should be restricted from:
Executing INSERT, UPDATE, DELETE, and other data modification commands.
Performing schema changes.
Documentation updates to include:
How to enable and configure read-only mode.
Limitations and expected behavior when read-only mode is enabled.
Use cases and scenarios for enabling read-only mode.
The ability to configure read-only mode on a per-node basis.
Runtime configuration changes should only be executed by the DDL coordinator (primary coordinator).
Customization and Configuration:

Configuring Read-Only Mode

1. Enabling Read-Only Mode Globally:
   To enable read-only mode for all worker nodes in the cluster, add the following setting to the postgresql.conf file on each worker node:

# Enable read-only mode
citus.read_only_mode = 'on'

Alternatively, this setting can be applied at runtime using the following SQL command, which should only be executed by the DDL coordinator (primary coordinator):

ALTER SYSTEM SET citus.read_only_mode = 'on';
SELECT pg_reload_conf();

2. Enabling Read-Only Mode Per Node:
   To enable read-only mode on specific worker nodes, use the following steps:

Node-Level Configuration:
Add the read-only setting in the postgresql.conf file of the specific worker node:

# Enable read-only mode on this specific worker node
citus.read_only_mode = 'on'

Runtime Configuration:
If a more dynamic configuration is required, use the following SQL command on the specific worker node, executed by the DDL coordinator (primary coordinator):

ALTER SYSTEM SET citus.read_only_mode = 'on';
SELECT pg_reload_conf();

3. Advanced Customization:
   For more granular control, consider implementing a role-based approach where specific roles have read-only access. This can be combined with the node-level configuration for added security:

-- Create a read-only role
CREATE ROLE readonly_role WITH LOGIN;

-- Grant read-only permissions to the role
GRANT CONNECT ON DATABASE mydatabase TO readonly_role;
GRANT USAGE ON SCHEMA public TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;
Assign this role to users or applications that require read-only access.

Use Cases:

• Data Analytics: When worker nodes are used for analytical queries that do not require data modification.

• Reporting: Ensuring that reporting queries do not alter the underlying data.

• Security: Enhancing security by preventing data tampering on worker nodes processing sensitive information.

• Additional Information:

This feature should be backward compatible with existing configurations where read-only mode is not enabled.

[Demy076]

The primary runtime executed on the primary coordinator suggest every node in the cluster corresponds to the read-only setting