PostgreSQL, ReadOnly User and PgAdmin

Question

Could someone help with the doubt below please?

I have one database in PostgreSQL with some tables created with administrator user. Then I created one View to concatenate some informations. I prefer to use PgAdmin to work with PostgreSQL because I'm not familiar with SQL syntax to manage the server... and now:

How can I create a user that can only see this View when he connects to database server?
How can I set this user as read only in this View?

Thank you so much!!!

Does this answer your question? [How do you create a read-only user in PostgreSQL?](https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql) — abcalphabet, Sep 18 '20 at 16:06

score 0 · Answer 1 · answered Sep 18 '20 at 14:06

First, you need to REVOKE USAGE on the public schema from everybody and grant it to the other users:

REVOKE USAGE, CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA public TO ddl_user;
GRANT USAGE  ON SCHEMA public TO write_user;

Now create the view in a new schema and allow the new user only access to that schema and view:

CREATE SCHEMA viewschema;
CREATE ROLE only_view LOGIN;
GRANT USAGE ON SCHEMA viewschema TO only_view;
ALTER ROLE only_view SET search_path = viewschema;
CREATE VIEW viewschema.myview AS SELECT ...;
GRANT SELECT ON viewschema.myview TO only_view;

PostgreSQL, ReadOnly User and PgAdmin

1 Answers1