I was looking at information_schema.role_table_grants when I saw public in grantee column, then I've checked at information_schema.enabled_roles but this role_name does not exist.
=> Who or what is the public role?
Add a comment
|
2 Answers
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
