CODEWITHSUNDEEP
postgresql
Peter
Peter

Reputation: 2404

Issue creating read-only user in PostgreSQL that results in user with greater permission with public schema

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!

Upvotes: 0

Views: 3281

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246808

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

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Upvotes: 1

Related Questions