CODEWITHSUNDEEP
postgresql
Erik Cederstrand
Erik Cederstrand

Reputation: 10220

SELECT * when access to some columns is prohibited

I have a table tbl with column-level grants where columns a and b are readable by everyone but column c is only readable by certain roles.

Currently, SELECT * FROM tbl fails for non-privileged users with ERROR: permission denied for relation c. Is there some way I can make the statement return just columns a and b instead of failing?

I know SELECT * is generally discouraged, but this table is used by data scientists who do a lot of ad-hoc queries, either directly in SQL or via various tools that connect to the table.

I know I could create a view, but the table is much more complicated than the example. There are 20-30 columns with different combinations of role access, so it would result in at least as many views.

Upvotes: 0

Views: 44

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

You could define a view on top of this table:

CREATE VIEW tbl_view AS (
    SELECT a, b
    FROM tbl
)

Then, have the data scientists hit this view with a SELECT *. I'm assuming that in practice there are more columns than just a and b. The idea here is that you do the heavy lifting of spelling out the allowed columns, so your consumers do not have to worry about this.

Upvotes: 2

Related Questions