CODEWITHSUNDEEP
databasepostgresqlsecurity
Pavunkumar
Pavunkumar

Reputation: 5345

Create hidden column in postgresql

Can we create hidden column in the table. It should be listed only when I specify column name explicitly in select statement.

Upvotes: 2

Views: 12859

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324911

No, there is no supported and safe way to create a hidden column that's listed only when you specify the column name explicitly. PostgreSQL doesn't provide any user-accessible way to hide user-defined columns from the * wildcard.

You could use any user interface layer / query builder of your choice to do this, though.

(PostgreSQL actually does have hidden columns, as you'll see if you select ctid,xmin,xmax from some_table, but it doesn't allow users to add to the set of hidden columns. It is possible to modify the system catalogs directly to trick PostgreSQl into thinking that a user defined column is a hidden system column, but it's a really bad idea to mess directly with the catalogs, so I won't explain how in detail. If you insist on doing this, read the documentation on pg_attribute ... but understand that you're creating a giant foot gun.).

You can set column permissions so a user can only select some columns, though again you can't say "all except this one", you have to say "I want them to be able to see these ones".

Update: @maybeWeCouldStealAVan has the most sensible suggestion: you probably want a view. Mark that answer as correct, not mine.

Upvotes: 5

maybeWeCouldStealAVan
maybeWeCouldStealAVan

Reputation: 15610

You can effectively do this by creating a view and selecting only the columns you wish to show.

Upvotes: 5

Related Questions