Privileges

Privileges

The ai.grant_ai_usage function is an important security and access control tool in the pgai extension. Its primary purpose is to grant the necessary permissions for a specified user or role to use the pgai functionality effectively and safely. This function simplifies the process of setting up appropriate access rights, ensuring that users can interact with the AI features without compromising database security.

Purpose:

1. Grant appropriate permissions to a specified user or role for using pgai features.
2. Provide a streamlined way to manage access control for the AI functionality.
3. Allow for different levels of access (regular usage vs. administrative access).

Usage:

SELECT ai.grant_ai_usage(to_user name, admin bool DEFAULT false)

Parameters:

1. to_user: The name of the user or role to whom permissions will be granted.
2. admin: A boolean flag indicating whether to grant administrative privileges (default is false).

The function doesn’t return a value, but it performs several grant operations.

Key actions performed by ai.grant_ai_usage:

1. Grants permissions on the ‘ai’ schema.
2. Grants permissions on tables, sequences, and views within the ‘ai’ schema.
3. Grants execute permissions on functions and procedures in the ‘ai’ schema.
4. If admin is true, grants more extensive permissions, including the ability to grant permissions to others.

Examples:

1. Granting regular usage permissions:

SELECT ai.grant_ai_usage('analyst_role');

This grants basic usage permissions to the ‘analyst_role’.

2. Granting administrative permissions:

SELECT ai.grant_ai_usage('ai_admin_role', admin => true);

This grants administrative permissions to the ‘ai_admin_role’.

Key points about ai.grant_ai_usage:

1. Regular usage (admin = false):

   • Grants USAGE and CREATE on the ‘ai’ schema.
   • Grants SELECT, INSERT, UPDATE, DELETE on tables.
   • Grants USAGE, SELECT, UPDATE on sequences.
   • Grants SELECT on views.
   • Grants EXECUTE on functions and procedures.

2. Administrative usage (admin = true):

   • Grants ALL PRIVILEGES on the ‘ai’ schema, tables, sequences, views, functions, and procedures.
   • Includes WITH GRANT OPTION, allowing the admin to grant permissions to others.

3. The function is designed to be idempotent, meaning it can be run multiple times without causing issues.

4. It automatically handles the different types of database objects (tables, views, functions, etc.) without requiring separate grant statements for each.

5. The function is security definer, meaning it runs with the privileges of its owner (typically a superuser), allowing it to grant permissions that the calling user might not directly have.

Use cases:

1. Setting up a new analyst:

CREATE ROLE new_analyst;
SELECT ai.grant_ai_usage('new_analyst');

2. Promoting a user to an AI administrator:

SELECT ai.grant_ai_usage('experienced_user', admin => true);

3. Ensuring all members of a role have appropriate access:

SELECT ai.grant_ai_usage('data_science_team');

4. Granting temporary admin access for maintenance:

CREATE ROLE temp_admin;
SELECT ai.grant_ai_usage('temp_admin', admin => true);
-- After maintenance
DROP ROLE temp_admin;

Best practices:

1. Use this function instead of manually granting permissions to ensure consistency and completeness of access rights.
2. Be cautious with granting admin privileges, as this gives extensive control over the pgai functionality.
3. Regularly review who has been granted access, especially admin access, as part of security audits.
4. Consider creating roles for different levels of pgai usage and granting permissions to these roles rather than individual users.

The ai.grant_ai_usage function is a crucial tool for managing access to pgai features. It ensures that users have the permissions they need to work with AI functionality in the database while maintaining security and control over these powerful features. By providing a simple interface for granting permissions, it helps database administrators manage access effectively and reduce the risk of misconfiguration.