graphile / graphile/graphile.github.io

Clarify the importance of NOINHERIT to properly create a role on the DOCS

Aperta
#110 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub
enhancement help wanted important very_important
Lingua principale
SCSS
Stelle
27
Fork
126
Metriche di merge delle PR
Nessuna PR unita negli ultimi 30g

Descrizione

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](https://www.postgresql.org/docs/9.5/sql-grant.html):
"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.

Guida per i contributori

Nessuna guida per i contributori indicizzata per questo repository

Valutazione

Questa issue non è ancora stata valutata.

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.