Please don't top-post, and trim some of the cruft while you are at it...
On Wednesday, April 19, 2017, Ron Ben <
ronb910@walla.co.il> wrote:
OK. I think I found a bug in PostgreSQL (9.3).
No, it's just that roles and grants are complicated.
When I do:
CREATE ROLE ronb
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
Everything works. I can create the schemas and upload the backup correclty.
If you define a superuser the rest are redundant...
But if I do:
CREATE ROLE "ronb" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users TO "ronb";
CREATE ROLE users
SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
It doesn't work. ronb user still can't create schemas eventhogh the role users give ronb permission to do so.
The GRANT here is worthless.
Not sure where your schema reference is coming from but none of the items specified during the create role command are inheritable. Only grants (object permissions) are. If Ron were to "set user to users" they would gain superuser and could then do what they need. But if they remain calling themselves "ronb" they will remain limited. The notes section of the create role docs emphasize this dynamic.
Also, PostgreSQL doesn't show the GRANTS of role in the same order as they were given (In PgAdmin). It sort them alphabeticly which is highly confusing!
If for example you run "alter table x add column" you know that the new column is added last (if you refresh the table you will see it last).
But if you add another GRANT statment to user it won't be in the last.. you have no way of knowing the correct order of GRANTS.
Grants don't have any significant order - they are purely additive so either you've got it or you don't.
David J.
