GRANT USAGE ON SERVICE TO ALL ROLES

The GRANT USAGE ON SERVICE TO ALL ROLES utility function enables a SUPERUSER to grant access to services for other system roles.

You may use it to perform one of the following options:

Granting access to all services for all roles
Granting access to a specific service for all roles

This utility function is particularly beneficial during the upgrade process from SQreamDB version 4.2 or earlier to version 4.3 or later. In previous versions, service access permissions were not required. In this scenario, you can easily grant access to all services for all roles immediately after the upgrade. If you are already using SQreamDB version 4.3 or later, you can grant or revoke access to services by following the access permission guide.

Note

When you create a new role, it automatically inherits access permissions to all services from the PUBLIC role. If you prefer to create new roles without automatically granting them access permissions to all services, you will need to follow the ALTER DEFAULT PERMISSIONS guide to revoke the access permissions of the PUBLIC role.

Syntax

SELECT grant_usage_on_service_to_all_roles (<'single service'>)

Examples

Granting access to all services for all roles:

SELECT grant_usage_on_service_to_all_roles();

Output:

role_name |  service_name |  status
----------+---------------+--------------------
role1     |  service1     |  Permission Granted
role1     |  service2     |  Permission Granted
role1     |  service3     |  Permission Granted
role2     |  service1     |  Permission Granted
role2     |  service2     |  Permission Granted
role2     |  service3     |  Permission Granted
role3     |  service1     |  Permission Exists
role3     |  service2     |  Permission Exists
role3     |  service3     |  Permission Exists

Granting access to one specific service for all roles:

SELECT grant_usage_on_service_to_all_roles('service1');

Output:

role_name |  service_name |  status
----------+---------------+--------------------
role1     |  service1     |  Permission Granted
role2     |  service1     |  Permission Granted
role3     |  service1     |  Permission Exists

Permissions

Using the grant_usage_on_service_to_all_roles requires SUPERUSER permission.