0

I'm learning PostgreSQL and now I'm trying to create a read-only user.

CREATE USER user WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE my-database TO user;
GRANT USAGE ON SCHEMA public to user;
GRANT SELECT ON ALL TABLES IN SCHEMA public to user;

However, I'm having issues using the public schema which has permissions to CREATE databases and others.

I don't want to affect other users or modify the public schema. What would be the best way to GRANT FULL ONLY READ ACCESS?

Thanks from a newbie!

Peter
  • 2,004
  • 2
  • 24
  • 57
  • To create a database the user needs the `CREATEDB` privilege or be a superuser. Your `user` role has neither. Please verify your issue and add details to your question of what your problem is – Patrick Apr 22 '22 at 20:37

1 Answers1

1

You cannot solve this problem without modifying the public schema. You will have to

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks, @Laurenz Albe So what would be the best way to create a read-only user in your experience? – Peter Apr 22 '22 at 21:19
  • Grant `SELECT` permissions on all tables and views and `USAGE` on all schemas, and make sure that no write permissions are granted to `PUBLIC`. – Laurenz Albe Apr 24 '22 at 13:08
  • Thanks again, @Laurenz Albe Excuse my ignorance, but this is not going to affect other users or the functionality of my app, no? – Peter Apr 25 '22 at 16:38
  • It may, if the application tries to create objects in `public`. If it doesn't, it should be no problem. Test it! – Laurenz Albe Apr 25 '22 at 19:56