11

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?

9

Right from the manual:

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.

7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.