Permission Denied when selecting data from a view with read-only access a newly created object in Postgres

Question

I have a postgres SQL database which I created a readonly user and has read access to existing objects. I created a view and now I need to give the read access to the view. once I gave access to the view, it shows Permission denied for View..

This is what I have done.

grant select on public."View_Name" to "readonlyuser@test_XXXXXX"

Any help is appreciated.

` EXECUTE AS user = 'readonlyuser@test_XXXXXX' go SELECT * FROM go ` — Aswin, Jun 26 '23 at 08:18

score 0 · Answer 1 · answered Jun 28 '23 at 07:17

Did you create the view after granting the privileges to readonlyuser?

If so, it is not entitled to the same rights as other views. This is due to the meaning of GRANT... ON which is "on view that exist." Permissions are kept on the item in PostgreSQL. All of an object's permissions are lost if it is dropped. Even if you subsequently create an object with the same name, it will still be distinct and have the default rights since it is a separate object.

I tried with below query, and it worked for me:

--Create view
CREATE VIEW pulic.”demov” AS SELECT * FROM demotb; 
--Grant select on view to user
GRANT SELECT ON pulic.”demov” TO readonly;

After this login with database with this readonly user and try to select the view.

To include views, you create in the future, you can use:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonlyuser;

I did the steps, still shows permission denied. in the grant vizard it does not show the assign privileges? any idea? — SOUser, Jun 30 '23 at 12:45
I used below to grant select permission on the schema GRANT SELECT ON ALL TABLES IN SCHEMA public TO "user"; — SOUser, Jul 10 '23 at 01:19

Permission Denied when selecting data from a view with read-only access a newly created object in Postgres

1 Answers1