Postgresql Security with Builtin Roles and functions.

Introduction

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 🙂

Understanding Roles and Privileges in PostgreSQL

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, SUPERUSER, CREATEDB, or CREATEROLE, further defining their capabilities within the database environment.

Orthogonality of Roles:
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.

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

To review existing roles, you can query the system catalogs:

SELECT rolname FROM pg_roles;
SELECT usename FROM pg_shadow;

PostgreSQL: Documentation: pg_shadow
PostgreSQL: Documentation: pg_roles

Hierarchical Role System and Practical Examples

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 and REVOKE for precise access control.

Example: Managing Permissions for Dev and QA Teams

1. Create Roles for Teams

-- Create team roles
CREATE ROLE dev_team;
CREATE ROLE qa_team;

2. Create Individual User Roles and Assign Them to Teams

-- Create user roles with login privileges
CREATE ROLE emma LOGIN PASSWORD 'emma_pass';
CREATE ROLE mirka LOGIN PASSWORD 'mirka_pass';

-- Assign users to their respective teams
GRANT dev_team TO emma;
GRANT qa_team TO mirka;

3. Assign Privileges to Team Roles

-- Grant privileges to team roles
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO dev_team;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO qa_team;

In this setup, Alice and Bob inherit the privileges of their respective teams due to the default INHERIT attribute.

Advanced Role Management: INHERIT and NOINHERIT

The INHERIT attribute plays a key role in PostgreSQL’s permission system, allowing roles to automatically inherit privileges from roles they are members of. Conversely, the NOINHERIT attribute and the SET ROLE command offers flexibility in access control, enabling scenarios where privileges need to be tightly controlled.

INHERIT (Default Behavior)

A role with the INHERIT attribute automatically inherits all the privileges of the roles it is a member of. For example, if role A is a member of role B, and role B has the SELECT privilege on a table, role A will also be able to select from that table due to inheritance.

NOINHERIT

If a role is created with the NOINHERIT attribute, it does not automatically inherit privileges from roles it is a member of. However, the role can still use the SET ROLE command to temporarily assume the privileges of another role it’s a member of.

Creating and Modifying Roles with INHERIT or NOINHERIT

To create a role with NOINHERIT:

CREATE ROLE auditor WITH LOGIN NOINHERIT;

To alter an existing role to NOINHERIT:

ALTER ROLE auditor NOINHERIT;

Using SET ROLE to Assume Privileges

Even if a role is defined with NOINHERIT, it can assume the privileges of another role it’s a member of using the SET ROLE command:

-- Assume the privileges of another role
SET ROLE dev_team;

-- Perform actions with dev_team privileges

-- Revert back to original role
RESET ROLE;

Practical Use Case

The NOINHERIT 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 with access to sensitive logs and only allow certain users to assume this role explicitly when needed, instead of automatically inheriting those privileges.

By carefully designing your role hierarchy and using the INHERIT and NOINHERIT 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.

Best Practices for Secure and Efficient Permission Management

Adopting the principle of least privilege, 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.

Implementing the Principle of Least Privilege

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.

• Use Predefined Roles: Start by leveraging PostgreSQL’s predefined roles to assign common sets of privileges without granting superuser status.
• Regular Audits: Periodically review role memberships and privileges to ensure they are still appropriate.
• Limit Superuser Access: Restrict the number of superusers and avoid using superuser roles for routine tasks.

Integration with External Authentication Systems

In SQL Server environments, it’s common to use external user management systems like Active Directory. While PostgreSQL can integrate with such systems, it’s less common. However, tools like RedHat IdM are increasingly used alongside automation and DevOps practices to manage users and roles effectively.

PostgreSQL’s Predefined Roles

The evolution of PostgreSQL’s built-in roles reflects its growing use in critical production environments. These roles provide operational flexibility and enhanced security, aligning PostgreSQL more closely with traditional RDBMS like Oracle or SQL Server.

Refer to the official documentation for more details:
PostgreSQL: Documentation: Predefined Roles

Useful Predefined Roles

• pg_read_all_settings: Allows reading of all configuration variables without needing to be a superuser.
• pg_read_all_stats: Allows reading of all statistics. Monitoring tools like Prometheus and Percona Monitoring and Management (PMM) utilize this role.
• pg_stat_scan_tables: Allows reading of all statistics from pg_stat_all_tables and pg_statio_all_tables.
• pg_monitor: Combines the above roles, providing comprehensive monitoring capabilities.

Specialized Access Control Techniques

Granting Schema-only Access

Granting schema-only access in PostgreSQL is a strategic approach to provide users with the ability to view and understand database structures without granting them access to the actual data. This is particularly useful in environments where developers or auditors need to understand database layouts, write SQL queries, or perform schema migrations without the risk of accessing sensitive data.

Using USAGE Permissions on Schemas

The USAGE privilege on a schema allows a role to access objects within the schema (to see the objects). However, it doesn’t allow reading or modifying the data in those objects without additional permissions.

Granting Schema-only Access: Step-by-Step

1. Grant USAGE Permission on Schemas

GRANT USAGE ON SCHEMA public TO schema_viewer;

2. Grant SELECT Permission on System Catalogs

To enable access to object definitions, grant SELECT on the system catalogs in the pg_catalog schema:

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO schema_viewer;

3. Restrict Access to Data

Ensure that the role does not have SELECT permission on the tables containing actual data.

Advanced Use: Creating Views for Controlled Access

For more granular control, you can create views that expose the necessary metadata while filtering out sensitive information.

CREATE VIEW custom_table_metadata AS
SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname = 'public';

GRANT SELECT ON custom_table_metadata TO schema_viewer;

Use Cases

• Auditing: Auditors can verify the structure and integrity of the database schema without accessing data.
• Development: Developers can write and test queries, ensuring they are syntactically correct and optimized without requiring access to production data.

Automating the Process

For environments where new schemas or permissions need to be managed dynamically, consider automating these grants through scripts or configuration management tools like Ansible, Puppet, or Chef. Automation ensures consistency, reduces human error, and can be integrated into CI/CD pipelines for agile development practices.

Encapsulating Permissions Through Functions

Creating administrative functions with the SECURITY DEFINER attribute allows for encapsulating permissions, offering a method to manage user actions securely and efficiently.

Example: Dropping a User with a Security Definer Function

Create the Administrative Function

When you create a function with the SECURITY DEFINER attribute, which means the function will execute with the privileges of the user who defines it (typically a superuser or a user with the necessary administrative privileges). Ensure that this function carefully validates its inputs to prevent security issues, such as SQL injection or unauthorized actions.

CREATE OR REPLACE FUNCTION drop_user_function(target_username TEXT)
RETURNS VOID AS $$
BEGIN
    -- Validate that the target user exists
    IF EXISTS (SELECT FROM pg_roles WHERE rolname = target_username) THEN
        EXECUTE format('DROP ROLE %I', target_username);
    ELSE
        RAISE EXCEPTION 'User "%" does not exist.', target_username;
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Set the Function’s Ownership and Permissions

-- Assume the function is created by a superuser or admin role
ALTER FUNCTION drop_user_function(TEXT) OWNER TO admin_user;

-- Revoke default permissions and grant execute to specific roles
REVOKE ALL ON FUNCTION drop_user_function(TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION drop_user_function(TEXT) TO user_manager_role;

Use the Function

A user with execute privileges on this function can drop a user by calling:

SELECT drop_user_function('user_to_drop');

Security Considerations

• Validate Inputs: Always validate inputs within SECURITY DEFINER functions to prevent SQL injection attacks or unauthorized actions.
• Limit Permissions: Restrict execute permissions on the function to specific roles or users.

Conclusion

With PostgreSQL’s role and permission management features, DBAs can create a secure and efficient database environment tailored to their organization’s needs. Understanding and properly implementing role inheritance, predefined roles, and specialized access controls are crucial steps toward this goal. By adopting best practices such as the principle of least privilege, regular audits, and careful use of SECURITY DEFINER functions, you can enhance the security and operational effectiveness of your database environments. Additionally, if you are in a position of having to manage users’ permissions on a PostgreSQL cluster, you should enable a monitor tool of your own or with a third-party application to be sure to manage who has access to what regardless of whether you are going to integrate with IdMs and Active Directory/Entra capabilities… Small hint, if you monitor user access, refreshing your data every 5s is not recommended as it would put unnecessary pressure on the system with no added value since user grants are not that frequent in most cases and enterprise RDBMS, remember that you have an application at the front end…

If you want to read more on PostgreSQL security I recommend those 2 blogs as well :

Percona Blog : PostgreSQL Database Security Best Practices
EDB – Dave Page Blog :How to Secure PostgreSQL: Security Hardening Best Practices & Tips
dbi Blog – Daniel Westermann : Modifying pg_hba.conf from inside PostgreSQL