I was looking at
information_schema.role_table_grants when I saw
grantee column, then I've checked at
information_schema.enabled_roles but this
role_name does not exist.
=> Who or what is the
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.
While this is true it is not the full picture. Public also acts as an implicit role that other roles belong to and that has its own permissions not always reflected and reported that get inherited.
By default it gives create permission on the public schema. when you don't remove this all the other correct steps to create a read only user results in that user also being able to create new objects in the public schema and then due to ownership put data in them. To prevent this
REVOKE ALL ON SCHEMA public FROM PUBLIC;
Similarly it also gives permission on database level, to remove use
REVOKE ALL ON DATABASE all_database FROM PUBLIC;
Good article here: https://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf