Understanding Postgres Roles & Privileges

One of the main features of PostgreSQL is an access control system that allows administrators to assign roles and permissions.

In this article, we will discover what this actually means and explain step by step how these privileges and roles are created, assigned, revoked and how their hierarchy is related.

Understanding PostgreSQL Roles

Roles reflect who has access to database resources and to what extent. This makes it possible to separate different types of users, e.g. administrators, managers, employees, and control their interactions with data.

This results in the actual possibility of granting access to only those resources that an employee needs for work.

There are two types of roles:

🙋‍ User Roles: These roles represent individual users and their access privileges. User roles can log in and perform actions within the database.

👥 Group Roles: Also known as "roles," group roles are collections of user roles or other group roles. To simplify, let's assume that group roles are folders that store different user roles (files) or other group roles (different folders).

Creating Roles

Use the CREATE ROLE statement. For example:

CREATE ROLE my_user LOGIN PASSWORD 'my_password';

This will create a user role named "my_user" with a password. The LOGIN keyword allows the role to log in.

Assigning Privileges

Roles can be granted various privileges to interact with database objects. Privileges include among others SELECT, INSERT, UPDATE, DELETE, and many others. You can grant privileges using the GRANT statement. For instance:

GRANT SELECT, INSERT ON my_table TO my_user;

This grants the "my_user" role the ability to perform SELECT and INSERT operations on "my_table."

Role Hierarchy

PostgreSQL allows you to define role hierarchies, where one role can inherit privileges from another. This simplifies the management of permissions and roles. For example:

CREATE ROLE manager;
CREATE ROLE employee;
GRANT manager TO employee;

In this scenario, the "employee" role inherits the privileges of the "manager" role.

Controlling Access with Privileges

PostgreSQL provides several privilege levels, including:

1. Database-level Privileges: These privileges apply to the entire database.
2. Schema-level Privileges: Privileges can be granted at the schema level, allowing fine-grained control over specific schemas within a database.
3. Table-level Privileges: For even more granularity, you can grant privileges at the table level, specifying which actions are allowed on individual tables.

Revoking Privileges

To remove privileges, you can use the REVOKE statement. For example:

REVOKE SELECT ON my_table FROM my_user;

This revokes the SELECT privilege from the "my_user" role on the "my_table."

Using the DEFAULT Privilege

You can also set default privileges for a role using the ALTER ROLE statement. Default privileges define what privileges are granted automatically when new objects are created within the schema.

Security Best Practices

• Principle of The Least Privilege

Grant the minimum necessary privileges to each role to reduce the potential for security breaches.

• Regularly Review and Update Privileges

Regularly review and update role privileges to align with changing business needs and user roles

• Audit and Monitoring

PostgreSQL provides tools for auditing and monitoring, such as logging and third-party extensions, to keep track of role activities and changes in access control.

Frequently Asked Questions

Fixing role "postgres" does not exist

To check for the existence of this role, execute the following command within the PostgreSQL interactive shell using \du:

\du

The relevant line appears as follows:

 Role name |            Attributes             | Member of 
-----------+-----------------------------------+-----------
 postgres  | Superuser, Create role, Create DB | {}        

• Ensure that you have at least one role with superuser privileges; otherwise, you may encounter issues.
• If such a role exists, you can use it to log in.
• To verify permissions, examine the output of the \l command, which should match those of the superuser postgres on my Ubuntu system.
• If your setup uses the user as the superuser, you can attempt to log in using this command:

sudo -u user psql user

If user is indeed the database superuser, you can create another DB superuser and a private, empty database for them using the following commands:

CREATE USER postgres SUPERUSER;
CREATE DATABASE postgres WITH OWNER postgres;

Grant All Privileges in PostgreSQL

To grant all privileges on all tables in a specific schema to a user in PostgreSQL, you can use the GRANT command with the ALL PRIVILEGES keyword. Here's the SQL command to grant all privileges on all tables in a schema:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA your_schema TO your_user;

Let's brake this command down into parts:

• GRANT: This keyword indicates that you are granting privileges.
• ALL PRIVILEGES: This specifies that you want to grant all available privileges, including SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and others.
• ON ALL TABLES: This part of the command indicates that the privileges should apply to all tables in the specified schema.
• IN SCHEMA your_schema: Replace 'your_schema' with the name of the schema where you want to grant the privileges.
• TO your_user: Replace 'your_user' with the name of the user or role to whom you want to grant these privileges.

Remember to be cautious when granting such extensive privileges, and ensure you trust the user or "role" receiving them.

Conclusion

PostgreSQL allows you to manage access control in the database. By defining roles, granting and revoking privileges, and following security best practices, you can ensure that your database is safe and users have the appropriate level of access to do their jobs.