PROXY Protocol In Postgres

by ADMIN 27 views

Introduction

In the world of database management, security and transparency are crucial aspects to consider. One of the ways to achieve this is by implementing the PROXY protocol in Postgres. This protocol allows for the forwarding of client IP addresses, which is essential for various use cases, such as auditing and security monitoring. However, implementing the PROXY protocol in Postgres is not a straightforward task, especially when considering the complexities of pooling and permissioning.

Understanding the PROXY Protocol

The PROXY protocol is a well-known protocol that allows for the forwarding of client IP addresses. It is primarily used in load balancers and proxies to pass the original client IP address to the server. In the context of Postgres, the PROXY protocol can be used to forward the client IP address from the proxy to the Postgres server.

Current Limitations

Currently, the Neon proxy knows the client's IP address, but there is no way to expose this in Postgres. The pg_stat_activity view only shows the IP address of the proxy, not the original client IP address. This limitation makes it challenging to implement the PROXY protocol in Postgres.

Neon Proxy

Focusing on the Neon proxy, we can update Postgres to support the well-known PROXY protocol V2 to forward along the client IP. This would work fine for TCP and WS-based connections. However, this is not a straightforward task, as it requires updating the Postgres configuration to support the PROXY protocol.

Pooling

Poolers cause issues when implementing the PROXY protocol in Postgres. They will need some way to update this setting every time the backend session changes to a new client session. This could be in the form of a new frontend protocol message which poolers can send when switching sessions.

Permissioning

We should be able to control in Postgres who can send the PROXY protocol header or the session-switch method. Otherwise, different Postgres deployment architectures could be vulnerable to users spoofing their sessions, which could mislead certain audit tooling.

Example Use Case

For instance, the session-switch mode cannot be used by the Neon proxy for TCP or WS, because after startup, we are a transparent proxy and do not filter messages. A client could then send this message themselves, which would not be ideal. However, the Neon proxy does also have a connection pool for SQL over HTTP - which makes this tricky. We cannot "just" change the behavior depending on who is connecting.

Solution

This might be a case where Neon exposes 2 Postgres ports.

  • One port for transparent proxied connections, which MUST receive only a PROXY protocol header, and MUST fail if receiving a SessionSwitch method
  • One port for pooled proxied connections, which SHOULD NOT receive a PROXY protocol header, but SHOULD receive a SessionSwitch message
    • these pooled proxies MUST NOT forward SessionSwitch messages directly, instead they SHOULD implement their own handling of this system, to support neonproxy->pgbouncer->postgres

Benefits

Implementing the PROXY protocol in Postgres has several benefits, including:

  • Enhanced security: By forwarding the client IP address we can ensure that the correct IP address is associated with each session.
  • Improved transparency: The PROXY protocol allows for the forwarding of client IP addresses, which is essential for various use cases, such as auditing and security monitoring.
  • Better scalability: By supporting the PROXY protocol, we can improve the scalability of our Postgres deployment.

Conclusion

In conclusion, implementing the PROXY protocol in Postgres is a complex task that requires careful consideration of pooling and permissioning. However, the benefits of implementing the PROXY protocol, including enhanced security and transparency, make it a worthwhile investment. By exposing 2 Postgres ports, we can ensure that the correct IP address is associated with each session, and that the PROXY protocol is implemented correctly.

Future Work

Future work includes:

  • Implementing the PROXY protocol in Postgres
  • Developing a new frontend protocol message for poolers to send when switching sessions
  • Implementing permissioning controls to ensure that only authorized users can send the PROXY protocol header or the session-switch method

References

  • [1] PROXY protocol specification
  • [2] Postgres documentation on the PROXY protocol
  • [3] Neon proxy documentation on the PROXY protocol

Appendix

A. PROXY Protocol Specification

The PROXY protocol is a well-known protocol that allows for the forwarding of client IP addresses. It is primarily used in load balancers and proxies to pass the original client IP address to the server.

B. Postgres Documentation on the PROXY Protocol

The Postgres documentation provides information on how to implement the PROXY protocol in Postgres.

C. Neon Proxy Documentation on the PROXY Protocol

Q: What is the PROXY protocol?

A: The PROXY protocol is a well-known protocol that allows for the forwarding of client IP addresses. It is primarily used in load balancers and proxies to pass the original client IP address to the server.

Q: Why is the PROXY protocol important in Postgres?

A: The PROXY protocol is important in Postgres because it allows for the forwarding of client IP addresses, which is essential for various use cases, such as auditing and security monitoring.

Q: How does the PROXY protocol work in Postgres?

A: The PROXY protocol works in Postgres by allowing the proxy to forward the client IP address to the Postgres server. This is done by sending a PROXY protocol header with the client IP address.

Q: What are the benefits of implementing the PROXY protocol in Postgres?

A: The benefits of implementing the PROXY protocol in Postgres include:

  • Enhanced security: By forwarding the client IP address, we can ensure that the correct IP address is associated with each session.
  • Improved transparency: The PROXY protocol allows for the forwarding of client IP addresses, which is essential for various use cases, such as auditing and security monitoring.
  • Better scalability: By supporting the PROXY protocol, we can improve the scalability of our Postgres deployment.

Q: How do I implement the PROXY protocol in Postgres?

A: To implement the PROXY protocol in Postgres, you will need to update the Postgres configuration to support the PROXY protocol. This will involve updating the pg_stat_activity view to show the client IP address.

Q: What are the challenges of implementing the PROXY protocol in Postgres?

A: The challenges of implementing the PROXY protocol in Postgres include:

  • Pooling: Poolers cause issues when implementing the PROXY protocol in Postgres. They will need some way to update this setting every time the backend session changes to a new client session.
  • Permissioning: We should be able to control in Postgres who can send the PROXY protocol header or the session-switch method. Otherwise, different Postgres deployment architectures could be vulnerable to users spoofing their sessions.

Q: How do I handle pooling and permissioning in Postgres?

A: To handle pooling and permissioning in Postgres, you will need to implement a new frontend protocol message for poolers to send when switching sessions. You will also need to implement permissioning controls to ensure that only authorized users can send the PROXY protocol header or the session-switch method.

Q: What are the best practices for implementing the PROXY protocol in Postgres?

A: The best practices for implementing the PROXY protocol in Postgres include:

  • Implementing the PROXY protocol in a way that is transparent to the user.
  • Ensuring that the PROXY protocol is implemented correctly to avoid any issues with pooling and permissioning.
  • Testing the PROXY protocol thoroughly to ensure that it is working correctly.

Q: What are the common mistakes to avoid when implementing the PROXY protocol in Postgres?

A: The common mistakes to avoid when implementing the PROXY protocol in Postgres include:

  • Not implementing the PROXY protocol correctly, which can lead to issues with pooling and permissioning.
  • Not testing the PROXY protocol thoroughly, which can lead to issues with the PROXY protocol not working correctly.
  • Not implementing permissioning controls, which can lead to users spoofing their sessions.

Q: How do I troubleshoot issues with the PROXY protocol in Postgres?

A: To troubleshoot issues with the PROXY protocol in Postgres, you will need to:

  • Check the Postgres logs to see if there are any errors related to the PROXY protocol.
  • Check the pg_stat_activity view to see if the client IP address is being forwarded correctly.
  • Test the PROXY protocol thoroughly to ensure that it is working correctly.

Q: What are the future plans for the PROXY protocol in Postgres?

A: The future plans for the PROXY protocol in Postgres include:

  • Implementing the PROXY protocol in a way that is transparent to the user.
  • Ensuring that the PROXY protocol is implemented correctly to avoid any issues with pooling and permissioning.
  • Testing the PROXY protocol thoroughly to ensure that it is working correctly.