Description
When a ROLE is created in PG, it may inherit privileges from other roles. It specially inherits all privileges from the PUBLIC role.
From the DOCs:
"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.".
If your application is implementing a whitelist logic (most of the cases), this may represent a leak of authorization. So all roles must be created using the NOINHERIT clause, but there is another gotcha: creating a role and them altering it to NOINHERIT is a safer option than creating a role with NOINHERIT.
-- OK if there is no graphile_visitor already in your system
CREATE ROLE graphile_visitor NOINHERIT;
-- A better option, specially during development if you use dropdb to "reset" your application
CREATE ROLE graphile_visitor;
ALTER ROLE graphile_visitor NOINHERIT;
When a ROLE is created, it's not stored into the database itself, but inside a private catalog called pg_user. If graphile_visitor by accident was crate without the NOINHERIT, dropping the database won't erase it, so adding the NOINHERIT to its creation clause afterward will have no effect at all.
-- this is a mistake
CREATE ROLE graphile_visitor;
-- this isn't a bug fix
CREATE ROLE graphile_visitor NOINHERIT;
This happens because, when it runs the second query, PG will identify that a role with the same name already exists so it will skip the CREATE ROLE. Keeping creation and alteration separated will ensure the intended behavior.