In this tutorial, I will explain how to manage users and permissions in PostgreSQL in an efficient and organized manner.
PostgreSQL revolves around the concept of roles, which are used to define user permissions. When you first install PostgreSQL on macOS, a role is automatically created based on your macOS username, along with a list of permissions granted. It is important to note that in PostgreSQL, there are no users, only roles.
To access PostgreSQL with your macOS username, simply run the command psql postgres
in your terminal. This will automatically log you in and grant access to the role that was created. You can verify this by using the \du
command, which displays the role attributes.
When creating a new role, you can use the CREATE ROLE
command. For example, to create a role named “testing”, you can execute the following command:
CREATE ROLE testing;
By default, the newly created role in the example above does not have the ability to login. To grant login privileges, you can add the WITH LOGIN
attribute during role creation.
CREATE ROLE testing WITH LOGIN;
If you need to remove a role and add it again with the WITH LOGIN
attribute, you can do so using the DROP ROLE
and CREATE ROLE
commands.
Adding a password to a role is an essential step in ensuring security. You can assign a password to a role using the PASSWORD
keyword in the CREATE ROLE
command like this:
CREATE ROLE <role> WITH LOGIN PASSWORD '<password>';
Alternatively, you can use the CREATE USER
command to define roles with the LOGIN
attribute automatically added. This effectively creates users that are allowed to login:
CREATE USER <role> PASSWORD '<password>';
If you want to add or modify a role attribute for an existing role, you can use the ALTER ROLE
command. For example, to add the LOGIN
attribute to a role named <username>
:
ALTER ROLE <role> WITH LOGIN;
PostgreSQL provides several built-in role attributes that offer different levels of permissions and capabilities. Some important role attributes include:
SUPERUSER
/NOSUPERUSER
: Grants or revokes superuser permissions.CREATEDB
/NOCREATEDB
: Allows or restricts the ability to create new databases.CREATEROLE
/NOCREATEROLE
: Allows or restricts the ability to create new roles.CREATEUSER
/NOCREATEUSER
: Allows or restricts the ability to create new users.INHERIT
/NOINHERIT
: Allows or restricts the ability to inherit privileges.REPLICATION
/NOREPLICATION
: Grants or denies replication permissions.
Unlike some database systems, PostgreSQL does not have predefined groups of users. Instead, you can create roles with specific permissions and assign those roles to other roles to achieve a similar effect. Roles can inherit permissions from other roles if the INHERIT
attribute is set.
To create a group role, use the same syntax as creating a regular role:
CREATE ROLE <groupname>;
Once the group role is created, you can assign roles to it using the GRANT
command:
GRANT <groupname> TO <role>;
For example, you can create a user role named “flavio”, a group role named “employee”, and assign the user role to the group role:
CREATE USER flavio PASSWORD 'superSecret123$';
CREATE ROLE employee;
GRANT employee TO flavio;
To remove a role from a group role, you can use the REVOKE
command:
REVOKE <groupname> FROM <username>;
To enable the inheritance of attributes from a group role to its members, make sure to create the group role with the INHERIT
attribute:
CREATE ROLE employee WITH CREATEDB INHERIT;
Now, when you create a new role and assign it to the group role, it will inherit the attributes:
CREATE ROLE flavio;
GRANT employee TO flavio;
These are the basics of managing user permissions and roles in PostgreSQL. By effectively utilizing roles and their attributes, you can organize and control access to your PostgreSQL database.