Обсуждение: Extension vs Implementing Wire Protocol
Hi all,
We need to apply some data management policies on read. We are considering two approaches
- Implement a postgres extension.
- Implement a proxy that implements the PostgreSQL wire protocol.
We're trying to decide which approach to take and are leaning towards implementing it as an open source extension. Mostly what we need is to be able to apply simple row level transformations after query execution before data is returned to the client. Our hope is we can do this without messing with cursors or execution as we don't want it to be brittle across postgres versions. It would be super cool if it could apply to foreign data wrappers as well.
Examples of behaviors we'd like to have:
- 'SELECT * FROM table;' should return masked versions of the columns based on policy for that specific user.
- 'SELECT * FROM table;' should return just the columns accessible to a specific user.
Questions:
- Are there easily extensible (ideally open-source) proxies that already implement the Postgres protocol that we could modify/extend to support this?
- Does the extension framework support post-query execution transformations before returning the result set such that it is compatible with postgres clients (transparently).
This seems like a useful extension and we'd be happy to build it and open source it so that other folks could benefit. Also, please let me know if I got the wrong mailing list and should take discussion elsewhere.
Matthew
Matthew Tamayo-Rios schrieb am 20.07.2020 um 22:13: > Examples of behaviors we'd like to have: > > * 'SELECT * FROM table;' should return masked versions of the columns based on policy for that specific user. > * 'SELECT * FROM table;' should return just the columns accessible to a specific user. > > Questions: > > 1. Are there easily extensible (ideally open-source) proxies that > already implement the Postgres protocol that we could modify/extend > to support this? You might want to look this extension: https://postgresql-anonymizer.readthedocs.io/en/latest/ > 2. Does the extension framework support post-query execution > transformations before returning the result set such that it is > compatible with postgres clients (transparently). In general you could probably achieve both use-cases with rewrite rules and/or views (both being "pre-query" rather than post-query though)
On Mon, Jul 20, 2020 at 10:36:26PM +0200, Thomas Kellerer wrote: > > * 'SELECT * FROM table;' should return masked versions of the columns based on policy for that specific user. > > * 'SELECT * FROM table;' should return just the columns accessible to a specific user. > > > > Questions: > > > > 1. Are there easily extensible (ideally open-source) proxies that > > already implement the Postgres protocol that we could modify/extend > > to support this? > > You might want to look this extension: > > https://postgresql-anonymizer.readthedocs.io/en/latest/ or https://github.com/marcmunro/veil Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B