3

In searching for examples of ALTER and GRANT commands I notice there are times where superuser is granted, but login is not.

Moreover, it turns out that if you GRANT SELECT privileges to a role but not LOGIN, they can't execute select queries anyway as it depends on login.

So what would be the use for a nologin superuser, or indeed a nologin role full stop? Is the only reason when the role is used as a group?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
jsstuball
  • 4,104
  • 7
  • 33
  • 63

1 Answers1

3

There are two things a role can do even if it cannot login:

  • It can own objects.

  • It can have other roles as members, that is, it can act as a user group.

As far as I can tell, a superuser owner only makes a difference with functions that are declared SECURITY DEFINER. Such functions run in the context of the functions's owner, that is, with superuser privileges. Powerful, but dangerous.

Being a member of a superuser role allows you to assume superuser privileges temporarily by issuing

SET ROLE the_su_role;

You can return to be a mortal user with

RESET ROLE;

That can be useful if you want to be able to issue superuser commands, but don't want to expose yourself to the risk of using a superuser all the time, similar to the su and sudo commands on UNIX.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks for the swift reply. And owning objects would be of no use at all if a role could not also act as a group, right? – jsstuball Mar 11 '19 at 10:30
  • It might be precisely be the use case that the object owner cannot log in (and hence cannot `ALTER` or `DROP` the objects). But for that it makes no difference if it is a superuser or not. – Laurenz Albe Mar 11 '19 at 10:40