34

I created a superuser portal with the following command

create role portal with superuser password 'portal'

When I tried to login into postgres with portal user I am getting the following error in rails.

FATAL:  role "portal" is not permitted to log in

I can't follow what is going on.

of course I can alter user with login command to enable portal user to login. I would like to understand why super user can't login.

Antony
  • 443
  • 1
  • 4
  • 5
  • 2
    Because a role can never log in. You should have used create user if you want something that can log in. –  Jan 28 '14 at 07:56
  • 1
    @a_horse_with_no_name To be precise, there is no difference between a role and a user but there is between CREATE ROLE and CREATE USER, see Craig's answer. – András Váczi Jan 28 '14 at 20:49

2 Answers2

61

You didn't specify the LOGIN right:

ALTER ROLE portal WITH LOGIN;

If you use CREATE USER instead of CREATE ROLE the LOGIN right is granted automatically; otherwise you must specify it in the WITH clause of the CREATE statement.

Superusers do not inherently have the LOGIN right; a permissions check is still performed for superuser for this right. The reason for this is so that you can have a superuser role that you can SET ROLE to, but cannot be connected as directly.

Craig Ringer
  • 56,343
  • 5
  • 158
  • 190
5
CREATE ROLE 'portal' WITH LOGIN SUPERUSER PASSWORD 'portal';
Kit
  • 51
  • 1
  • 1
  • Welcome to the site! Your answer lacks an explanation - if you look around, good answers always tell you not only what but also why to do. With this in view, what does your answer add to the one from Craig? – András Váczi Sep 07 '15 at 08:19