PostgreSQL User Permissions: A Guide to Managing Roles | Tech Wiki Online

PostgreSQL User Permissions: A Guide to Managing Roles

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.

tags: [“PostgreSQL”, “user permissions”, “role management”]