{"id":31054,"date":"2024-11-10T16:32:15","date_gmt":"2024-11-10T15:32:15","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=31054"},"modified":"2025-03-04T11:03:40","modified_gmt":"2025-03-04T10:03:40","slug":"postgresql-security-with-builtin-roles-and-functions","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-security-with-builtin-roles-and-functions\/","title":{"rendered":"Postgresql Security with Builtin Roles and functions."},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

Introduction<\/h2>\n\n\n\n

As PostgreSQL continues to gain traction in enterprise-grade applications, I am increasingly tasked and asked about implementing robust security policies. And, as most current DBA positions are multi-systems nowadays, I wanted to make a quick summary on roles and how they can be leveraged to implement those policies. For those accustomed to managing accounts through Active Directory Security Groups in SQL Server or leveraging packages in Oracle, transitioning to PostgreSQL’s security model can present challenges. This blog explores the built-in roles and permission management techniques available “out of the box” in PostgreSQL, providing practical examples for DBAs aiming to secure their “newly” favorite open-source system \ud83d\ude42<\/p>\n\n\n\n

\n\n\n\n

Understanding Roles and Privileges in PostgreSQL<\/h2>\n\n\n\n

In PostgreSQL, roles encompass both users and groups, serving as the cornerstone for a comprehensive user access management system. A role can own database objects and be granted privileges to interact with objects owned by other roles. Roles with login privileges are effectively users, while those without function similarly to groups, but this is just by convention. Additionally, roles can possess attributes like LOGIN<\/code>, SUPERUSER<\/code>, CREATEDB<\/code>, or CREATEROLE<\/code>, further defining their capabilities within the database environment.<\/p>\n\n\n\n

Orthogonality of Roles:<\/strong>
Roles have an orthogonality property, meaning each role has a distinct set of privileges. Modifying one role only impacts that role, maintaining a clear separation of permissions. The inheritance property allows roles to inherit privileges from other roles without breaking this rule, providing flexibility in permission management.<\/p>\n\n\n\n

Privileges in PostgreSQL:<\/strong>
Privileges are defined access rights to database objects, including common actions like SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, DELETE<\/code>, and EXECUTE<\/code>. The system uses GRANT<\/code> and REVOKE<\/code> commands to manage these privileges, allowing fine-grained control over who can do what within the database.<\/p>\n\n\n\n

To review existing roles, you can query the system catalogs:<\/p>\n\n\n

\nSELECT rolname FROM pg_roles;\nSELECT usename FROM pg_shadow;\n<\/pre><\/div>\n\n\n
PostgreSQL: Documentation:\u00a0pg_shadow<\/a>
PostgreSQL: Documentation: pg_roles<\/a><\/p>\n\n\n\n
\n\n\n\n
Hierarchical Role System and Practical Examples<\/h2>\n\n\n\n
PostgreSQL’s role system supports membership, facilitating a hierarchical approach to permissions. Roles can inherit privileges from parent roles, streamlining permission management. Let’s explore practical scenarios demonstrating how to create roles with specific privileges, manage role memberships, and leverage GRANT<\/code> and REVOKE<\/code> for precise access control.<\/p>\n\n\n\n
Example: Managing Permissions for Dev and QA Teams<\/strong><\/h3>\n\n\n\n
1. Create Roles for Teams<\/strong><\/p>\n\n\n
\n-- Create team roles\nCREATE ROLE dev_team;\nCREATE ROLE qa_team;\n<\/pre><\/div>\n\n\n
2. Create Individual User Roles and Assign Them to Teams<\/strong><\/p>\n\n\n
\n-- Create user roles with login privileges\nCREATE ROLE emma LOGIN PASSWORD 'emma_pass';\nCREATE ROLE mirka LOGIN PASSWORD 'mirka_pass';\n\n-- Assign users to their respective teams\nGRANT dev_team TO emma;\nGRANT qa_team TO mirka;\n<\/pre><\/div>\n\n\n
3. Assign Privileges to Team Roles<\/strong><\/p>\n\n\n
\n-- Grant privileges to team roles\nGRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO dev_team;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO qa_team;\n<\/pre><\/div>\n\n\n
In this setup, Alice and Bob inherit the privileges of their respective teams due to the default INHERIT<\/code> attribute.<\/p>\n\n\n\n
\n\n\n\n
Advanced Role Management: INHERIT and NOINHERIT<\/h2>\n\n\n\n
The INHERIT<\/code> attribute plays a key role in PostgreSQL\u2019s permission system, allowing roles to automatically inherit privileges from roles they are members of. Conversely, the NOINHERIT<\/code> attribute and the SET ROLE<\/code> command offers flexibility in access control, enabling scenarios where privileges need to be tightly controlled.<\/p>\n\n\n\n
INHERIT (Default Behavior)<\/strong><\/h3>\n\n\n\n
A role with the INHERIT<\/code> attribute automatically inherits all the privileges of the roles it is a member of. For example, if role A<\/strong> is a member of role B<\/strong>, and role B<\/strong> has the SELECT<\/code> privilege on a table, role A<\/strong> will also be able to select from that table due to inheritance.<\/p>\n\n\n\n
NOINHERIT<\/strong><\/h3>\n\n\n\n
If a role is created with the NOINHERIT<\/code> attribute, it does not automatically inherit privileges from roles it is a member of. However, the role can still use the SET ROLE<\/code> command to temporarily assume the privileges of another role it’s a member of.<\/p>\n\n\n\n
Creating and Modifying Roles with INHERIT or NOINHERIT<\/strong><\/p>\n\n\n\n
To create a role with NOINHERIT<\/code>:<\/p>\n\n\n
\nCREATE ROLE auditor WITH LOGIN NOINHERIT;\n<\/pre><\/div>\n\n\n
To alter an existing role to NOINHERIT<\/code>:<\/p>\n\n\n
\nALTER ROLE auditor NOINHERIT;\n<\/pre><\/div>\n\n\n
Using SET ROLE to Assume Privileges<\/strong><\/p>\n\n\n\n
Even if a role is defined with NOINHERIT<\/code>, it can assume the privileges of another role it’s a member of using the SET ROLE<\/code> command:<\/p>\n\n\n
\n-- Assume the privileges of another role\nSET ROLE dev_team;\n\n-- Perform actions with dev_team privileges\n\n-- Revert back to original role\nRESET ROLE;\n<\/pre><\/div>\n\n\n
Practical Use Case<\/strong><\/h3>\n\n\n\n
The NOINHERIT<\/code> option is particularly useful in scenarios where you want to tightly control access to certain operations or objects. For example, you might create an audit_role<\/code> with access to sensitive logs and only allow certain users to assume this role explicitly when needed, instead of automatically inheriting those privileges.<\/p>\n\n\n\n
By carefully designing your role hierarchy and using the INHERIT<\/code> and NOINHERIT<\/code> attributes appropriately, you can create a robust security model that provides flexibility while ensuring that access to critical or sensitive operations and data is tightly controlled.<\/p>\n\n\n\n
\n\n\n\n
Best Practices for Secure and Efficient Permission Management<\/h2>\n\n\n\n
Adopting the principle of least privilege<\/strong>, utilizing groups for role organization, and conducting regular audits are pivotal strategies for maintaining a secure and efficient permission environment. These practices help minimize risks and ensure that roles are aligned with current security policies and operational requirements.<\/p>\n\n\n\n
Implementing the Principle of Least Privilege<\/strong><\/h3>\n\n\n\n
This principle involves granting users only the permissions they need to perform their tasks. While it can be challenging to implement, it’s crucial for database security.<\/p>\n\n\n\n