Want to clone a role, but am getting "can't login" message after doing so I’m using Postgres 9.5 on Ubuntu 14.04.  I would like to create a role (user) that has all the same privileges and access to a database that an existing role in my system has.  I thought I could pull that off by creating that user and then granting the other role to him…

postgres=# create role myuser;
CREATE ROLE
postgres=# GRANT rails TO myuser;
GRANT ROLE


but then when I login to that user, it turns out I don’t even have login privileges.

myuser@myproject:~$ psql mydb_production
psql: FATAL:  role "myuser" is not permitted to log in


What is the proper way to clone a role to another user (e.g. copy the privileges)?
The answer is simple, but may be very surprising:


  CREATE USER is now an alias for CREATE ROLE. The only difference is
  that when the command is spelled CREATE USER, LOGIN is assumed by
  default, whereas NOLOGIN is assumed when the command is spelled 
  CREATE ROLE.


So use CREATE USER instead of CREATE ROLE.

There is one more equally tricky aspect of this. Even if the role you grant to the new role (in your example, rails) has the LOGIN option, myuser won't inherit it.  The reason is that GRANT only gives permissions, while LOGIN is an attribute of the user/role:

CREATE USER rails; -- means it has LOGIN
CREATE ROLE myuser;
GRANT rails TO myuser;

\du rails
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 rails     |            | {}

\du myuser  
            List of roles
 Role name |  Attributes  | Member of 
-----------+--------------+-----------
 myuser    | Cannot login | {rails}


Note: if you are using one of the trust and peer authentication methods in pg_hba.conf, it's possibly a good idea to rethink that.  If you are using LDAP (the last idea I have, given that you don't specify a password), then ignore this.