what are privileged accounts and standard accounts, and what are their respective permissions - PolarDB

This topic describes the permissions for privileged accounts and standard accounts.

Introduction

To ensure database security, you must manage and limit user permissions by granting users only the permissions required for their tasks. ApsaraDB uses a role-based access control (RBAC) model to manage user permissions. In this model, you assign users to different roles, and each role has a specific set of permissions. This model provides effective access control and enhances database security.

The database provides two roles for users: privileged users and regular users.

Privileged users perform management tasks, such as creating databases, granting basic permissions, and creating publications and subscriptions.
Regular users perform various business logic operations.

PolarDB uses this permission system to define database operation types and implement an access control model to enhance the security of ApsaraDB.

Privileged user permissions

Account type	Permissions
Privileged account	Create, update, access, and delete all non-system objects, such as tables, indexes, types, views, functions, and procedures. Permissions on these objects are shared among all privileged users, which allows any privileged user to access objects owned by other privileged users.
	Create databases. For more information, see Create a database.
	Create extensions. For more information, see Extensions.
	Create users with permissions that are not greater than their own. For more information, see Create a user.
	Create and use event triggers. For more information, see Create a trigger.
	Create and modify types. For more information, see Create an object type.
	Invoke garbage collection instructions. For more information, see Garbage collection mechanism.
	Create and modify foreign data wrappers (FDWs). For more information, see Use oss_fdw to read and write external text files.
	Create publications and subscriptions. Example: `-- Create a publication CREATE PUBLICATION my_publication FOR TABLE test_t; -- Create a subscription CREATE SUBSCRIPTION my_subscription CONNECTION 'channel_name=XXXX dbname=XXXX user=XXXX password=XXXX' PUBLICATION my_publication;` Note You must use a `channel` to create a subscription. To create a channel, see Network channels. If an error occurs when you create the subscription, contact us.
	Use two-phase transactions. Example: `-- session_1 begin; insert into t values (1,'a'); prepare transaction 'test_1'; -- session_2 commit prepared 'test_1'; -- Or roll back rollback prepared 'test_1';`
	Send signals. Example: `-- 4300 is a non-superuser process select pg_cancel_backend(4300); select pg_terminate_backend(4300);`
	View background process statuses. Example: `select * from pg_stat_activity;`

Standard user permissions

Account type	Permissions
Standard account	Permission to connect to all databases.
Standard account	Permission to query objects for which they have PUBLIC permissions.

Permissions unavailable to privileged and standard users

The following permissions are prohibited for all users because they pose a security risk to the database.

Account type	Permission
Privileged account/Standard account	Modify system parameters.
	Create tablespaces.
	Kill superuser processes.
	Execute untrusted languages. Note plpgsql is a trusted language. All other languages are untrusted and cannot be executed.

User group permissions

The cloud-native database PolarDB for PostgreSQL is built on PostgreSQL and inherits some group permissions from it. This section describes the inheritance mechanism for these group permissions. These permissions are combined with the previously described privileged user permissions. The final permission set is the union of both.

Permission name	Permission status	Description
pg_read_all_stats & pg_stat_scan_tables	Full	Full permissions related to statistics information.
pg_signal_backend	Full	Full permissions to send signals to other processes.
pg_polar_superuser	Full	Privileged user group that includes a series of permissions for privileged users. Fully owned.
pg_polar_replication	Full	Provides permissions to use streaming replication. Fully owned.
pg_monitor	Full	Provides monitoring permissions, but blocks read and write permissions for local files.
pg_read_all_data & pg_write_all_data	Restricted	Permissions to read and write any table. You can read and write any user table and read most system tables. Access to some system tables is blocked for security reasons.
pg_read_all_settings	Restricted	Permissions to read any parameter. You can read parameters within your permission scope. Access to some parameters is blocked for security reasons.
pg_read_server_files & pg_write_server_files	Blocked	Permissions to read and write local files are not supported.
pg_execute_server_program	Blocked	Permission to execute binary commands is not supported.