Roles and privileges
From clusters, databases, schemas, and objects, let's move on to people who use them, also known as users. In PostgreSQL, a role is almost the same as a user because a role can be a user or a group of users. The CREATE USER
command is equivalent to CREATE ROLE
except that CREATE USER
implies the LOGIN
privilege, whereas CREATE ROLE
does not. So, if we need to create a user who can log in, we should use CREATE ROLE
. Take a look at the difference in the following command:
postgres=# CREATE USER my_user; CREATE ROLE postgres=# CREATE ROLE my_role; CREATE ROLE postgres=# \q [postgres@MyCentOS ~]$ psql -U my_user -d postgres psql (9.3.0) Type "help" for help.
A user can log in:
postgres=> \q [postgres@MyCentOS ~]$ psql -U my_role -d postgres FATAL: role "my_role" is not permitted to log in psql: FATAL: role "my_role" is not permitted to log in
A role can't log in. We have to explicitly provide login privileges:
[postgres@MyCentOS ~]$ psql psql (9.3.0) Type "help" for help...