Do you want to hide column values in PostgreSQL?  We had that exact need, allow our DevOps engineers to run selects from a table and obfuscate some of the column values that are returned.  For us, this masking of data generally applies to Personally Identifiable Information (PII).  There are a couple of methods to achieve this goal.  The first is to only grant SELECT on the columns you want them to be able to read.  In this method not only will you hide column values, but you will also hide the column itself. Below is an example of limiting which columns a user can read.

Now we should not be able to read from the table at all when we log in as the test_user role.

Next, we can grant SELECT only on the ID and YES_READ columns.

Now we can select the ID and YES_READ columns, but not the NO_READ columns. In my opinion, the resulting error message can be confusing because we do have permissions on the table, just not on one of the columns.  This methods will hide column values and is more secure than method 2.

Good, but not perfect.

This solution was less than desirable for our DevOps engineers because they wanted to run queries “as-is” without any modifications.  So we came up with a different option that’s not perfect but closer.  We obfuscated the data with search path, schemas, and views.  Like in the previous example we will create a table that we want to mask and a new user that we want to make the data from.

Alternatively, you could revoke the privileges at the schema level if you are applying this logic to a broad set of tables.

Next, we’re going to create a view that has the restricted view of the data.

Now we can grant the permissions necessary to read the data for our DevOps engineers.  Here I am granting access to everything in the protect_pii schema, but you could also grant select on just the one table as well.

Finally, if you set the user’s search_path they will try your new non-PII schema first and select from your non-PII view.  The code below tells the user’s session for queries that do not include a schema name try finding the object in the protect_pii schema first followed by the user’s own schema and then finally in the public schema.

Now your users can run selects against the protected tables, see all the columns, but not be able to view the PII dataset.

Optionally you could not include the PII column in your view, but then your query could not be run “as-is” when the select explicitly calls out column names.  Also be aware of the protected column’s datatype.  In this example, we set it to text with ::TEXT, but you may need to set a different datatype depending on the underlying table.