0

I granted permission for read_only user in postgres for some columns of a table using 

grant select(col1,col2) on mytable to read_only_user;

I want to use 

SELECT * FROM  mytable

query from read_only_user.But I'm getting permission denied in output.I don't want to create view for this. But somehow I want the select * query to work for that user(not explicitly telling what are the columns like select col1,col2 from mytable).

Help me guys.Thanks in advance...

Anuj.T
  • 1,598
  • 16
  • 31
karthikrsek
  • 1
  • 1
  • Did you grant usage on the schema as well? https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql – NPras Nov 02 '17 at 05:18
  • i can use "select col1,col2 from mytable" this query and it is working . but i want select * query.- @NPras – karthikrsek Nov 02 '17 at 05:21
  • Ah, sorry I misunderstood. So if you have `col1..5`, you want that particular user to only be presented with `col1, col2` when doing a `SELECT *`? I don't believe that's possible in postgre (or any others that I know of). – NPras Nov 02 '17 at 05:30
  • `select *` states you want to see all columns - however you do not have the privilege to see all columns. –  Nov 02 '17 at 06:37
  • @a_horse_with_no_name actually Npras got my point. Do you know any ways to do that? If i have grantend 30 out of 50 column , then every time i need to write the all allowed column names in query. that's why i ask this – karthikrsek Nov 02 '17 at 06:52
  • Create a view with only those columns –  Nov 02 '17 at 06:56

1 Answers1

-1

You cannot do that, and you shouldn't try.

SELECT * should never be used in code, it is only for ad hoc queries.
What are the problems:

  • If the table definition changes, your program will break.
  • You probably retrieve unnecessary columns, which will cause unnecessary data to be processed and may keep PostgreSQL from choosing a better execution plan.
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263