Content #
Create Role #
in order to be allowed to interactively log in, the role must also have the LOGIN option:
CREATE ROLE luca WITH LOGIN PASSWORD 'xxx';
CREATE ROLE luca WITH PASSWORD 'xxx' LOGIN;
Define expired date:
CREATE ROLE luca WITH LOGIN PASSWORD 'xxx' VALID UNTIL '2030-12-25 23:59:59';
Using a role as as a group #
A group is a role that contains other roles. Usually, when you want to create a group, all you need to do is create a role without the LOGIN option and then add all the members one after the other to the containing role. Adding a role to a containing role makes the latter a group.
postgres=# CREATE ROLE book_authors WITH NOLOGIN;
CREATE ROLE
postgres=# CREATE ROLE luca WITH LOGIN PASSWORD 'xxx' IN ROLE book_authors;
CREATE ROLE
postgres=# CREATE ROLE enrico WITH LOGIN PASSWORD 'xxx' IN ROLE book_authors;
CREATE ROLE
PostgreSQL extends the SQL syntax allowing the granting of a role to another role. When you grant a role to another, the latter becomes a member of the former.
postgres=# GRANT book_authors TO enrico;
Every group can have one or more admin members, which are allowed to add new members to the group.
postgres=# CREATE ROLE book_reviewers WITH NOLOGIN ADMIN luca;
The GRANT statement with the WITH ADMIN OPTION clause allows the membership of a role with administrative privileges.
postgres=# GRANT book_reviewers TO enrico WITH ADMIN OPTION;
Removing an existing role #
postgres=# DROP ROLE IF EXIST this_role_does_not_exist;
Inspecting existing roles #
get information about what role you are running:
postgres=# SELECT current_role;
psql provides the special \du (describe users) command to list all the available roles within the system:
\du
querying the pg_roles catalog:
SELECT rolname, rolcanlogin, rolconnlimit, rolpassword FROM pg_roles WHERE rolname = 'luca';
The special catalog pg_authid represents the backbone for the pg_roles information, and can be queried with the very same statement, but reports the user password (as encrypted text).
SELECT rolname, rolcanlogin, rolconnlimit, rolpassword FROM pg_authid WHERE rolname = 'luca';