Home / server / Questions / 1132728
Accepted
red888
Asked: 2023-06-07 00:35:16 +0800 CST

Why can't I create this role with perms to another role

  • 772

This doesn't work

CREATE ROLE "role-one" LOGIN;
CREATE ROLE "other_role" LOGIN WITH "role-one";

I get this error

ERROR:  unrecognized role option "role-one"
LINE 1: CREATE ROLE "other_role" WITH "role-one";

What am I doing wrong? I can't find examples online that make it clear to me what the syntax is. From what I have seen this should work.

How do I create a new role with LOGIN and add it to another role named role-one?

postgresql

1 Answers

  1. Best Answer

    Because you use a wrong syntax. Let me cite the manual:

    CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:
...
   | IN ROLE role_name [, ...]
   | ROLE role_name [, ...]
...

    IN ROLE role_name

    The IN ROLE clause lists one or more existing roles to which the new role will be immediately added as a new member. (Note that there is no option to add the new role as an administrator; use a separate GRANT command to do that.)

    ROLE role_name

    The ROLE clause lists one or more existing roles which are automatically added as members of the new role. (This in effect makes the new role a “group”.) ...

    (I skipped uninteresting parts).

    So, correct syntax would be either:

    • if you want other_role to be a member of role-one:
    CREATE ROLE "role-one" LOGIN;
CREATE ROLE "other_role" LOGIN IN ROLE "role-one";
    • if you want role-one to become a member of other_role (the reverse):
    CREATE ROLE "role-one" LOGIN;
CREATE ROLE "other_role" LOGIN ROLE "role-one";

    Notice keywords ROLE and IN ROLE which you skipped, apparently.

    There is also a way to create role and grant a membership in two distinct commands, which is perfectly explained with detailed example, again, in another manual page:

    CREATE ROLE "role-one" LOGIN;
CREATE ROLE "other_role" LOGIN;
GRANT "role-one" TO "other_role";

    In this case, other_role will be a member of role-one. To do the reverse thing, swap them in the last GRANT.

    Also, unrelated advices:

    • don't name roles "other_role" and "role-one". Either first one should be "other-role" or second "role_one", so both contain dash or underscore. Be consistent. This really helps to keep things straight and easier;
    • if you are making role a "group", consider adding it without LOGIN option. Let it be strictly a group.
    • 0