On Wed, Jun 22, 2022 at 11:53 PM Simon Riggs <si...@2ndquadrant.com> wrote:
> Postgres currently supports column level SELECT privileges. > > 1. If we want to confirm a credit card number, we can issue SELECT 1 > FROM customer WHERE stored_card_number = '1234 5678 5344 7733' > > 2. If we want to look for card fraud, we need to be able to use the > full card number to join to transaction data and look up blocked card > lists etc.. > > 3. We want to block the direct retrieval of card numbers for > additional security. > In some cases, we might want to return an answer like '**** ***** **** > 7733' > > We can't do all of the above with current facilities inside the database. > > The ability to mask output for data in certain cases, for the purpose > of security, is known lately as data redaction, or column-level data > redaction. > > The best way to support this requirement would be to allow columns to > have an additional "output formatting function". This would be > executed only when data is about to be returned by a query. All other > uses of that would not restrict the data. > > This would have other uses as well, such as default report formats, so > we can store financial amounts as NUMERIC, but format them on > retrieval as $12,345.78 etc.. > > Suggested user interface would be... > FORMAT functionname(parameters, if any) > > e.g. > CREATE TABLE customer > ( id ... > ... > , stored_card_number NUMERIC FORMAT pci_card_number_redaction() > ... > ); > > We'd need to implement something to allow pg_dump to ignore format > functions. I suggest the best way to do that is by providing a BACKUP > role that can be delegated to other users. We would then allow a > parameter for SET output_formatting = on | off, which can only be set > by superuser and BACKUP role, then have pg_dump issue SET > output_formatting = off explicitly when it runs. > > Do we want redaction in PostgreSQL? > Do we want it generalised into output format functions? > > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > > -- > Sent via pgsql-hackers mailing list (pgsql-hack...@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > > > Hi, Do we still have some interest in this? People generally like that the idea, if yes I am happy to work on that and can send the complete design first. -- Ibrar Ahmed
