Search code examples
postgresqldatabase-permissionssuperuser

Superuser nologin roles?


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?


Solution

  • 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.