How to hide the column from SELECT * statement?

user_16974979 0
Viewed 539

Is it possible to hide a column in a database table on a way that it is not selectable by the SELECT * statement? (In case you need details, my database is PostgreSQL, latest version).

[Additional info]

So, every table by default has several hidden columns. They do not get selected when you execute the SELECT * statement. Those columns are added automatically by the server when creating a table. So, it has to be possible to do this.

I have seen many questions on Stackoverflow specifying to use explicit column names in the SELECT statement instead of the * wildcard in order to 'hide' columns. But this filtering happens when constructing a query at the client side. I know about that, I sure can use this to 'hide' the column from the results at the client side. But, what I am interested to know is is it possible to do this on the server, so that column is hidden for * wildcard like many other hidden columns which every table has which are hidden by default.

If you need an example, suppose there is a table named TABLE and columns named COLUMN SHOWN BY DEFAULT and COLUMN HIDDEN BY DEFAULT. What would you do to hide COLUMN HIDDEN BY DEFAULT from the SELECT * query when issued on the TABLE table?

Is this even possible or is it only internal feature of the SQL server which only SQL server can use to hide those hidden by default columns which every table has?

This is only hypothetical question. I have no requirement to actually hide a column on that way. I am just into learning some more advanced tricks.

select postgresql sql
1 Answers

The canonical answer is to use a view:

CREATE VIEW tab_without_col AS
SELECT col1, col2, ... /* all columns except the ones you want to "hide" */
FROM tab WITH (security_barrier = on);

Then grant the user the SELECT privilege on the view, but not on the underlying table.

security_barrier makes sure that security cannot be subverted with cunningly crafted functions.

user_64643080
Related