All Products
Search

This Product

    PolarDB:Account permissions

    Document Center

    PolarDB:Account permissions

    Last Updated:Apr 18, 2024

    This topic describes the permissions of privileged accounts and standard accounts in PolarDB.

    Background information

    For the sake of security, you must manage and control account permissions in PolarDB. An account can only perform operations on which it is granted permissions. PolarDB adopted a role-based RAM model to manage account permissions. In this model, accounts are assigned different roles, each of which has specified permissions. Each account can perform operations on which it is granted permissions. This method can control account permissions and protect database security.

    PolarDB provides two roles: privileged accounts and standard accounts.

    • Privileged accounts are used for management tasks, such as creating databases, granting basic permissions, and creating publications and subscriptions.

    • Standard accounts perform business logic operations.

    PolarDB uses this permission system to build database operation types, implement the RAM model, and ensure permission security.

    Permissions granted to a privileged account

    Account

    Permission

    Privileged account

    Create a database. For more information, see Create a database.

    Create an extension. For more information, see Extensions of PolarDB for PostgreSQL.

    Create an account which has lower or equal permissions. For more information, see Create a user.

    Create and use an event trigger. For more information, see Create a trigger.

    Create and modify a type. For more information, see Create an object type.

    Invoke a garbage collection instruction. For more information, see Garbage collection mechanism.

    Create and modify an external FDW. For more information, see Read and write external data files by using oss_fdw.

    Create publications and subscriptions. Example:

    -- Create a publication.
create publication pub for table test_t;

-- Create a subscription.
create subscription sub connection 
'hostaddr=xxx.xxx.xxx.xxx port=xxxx user=PolarDB dbname=PolarDB_PG' 
publication pub;

    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 rollback.
rollback prepared 'test_1';

    Send signals. Example:

    -- 4300 is a non-superuser process
select pg_cancel_backend(4300);
select pg_terminate_backend(4300);

    View the status of background processes. Example:

    select * from pg_stat_activity;

    Permissions not granted to privileged accounts or standard accounts

    The following permissions are prohibited for the sake of database security.

    Account

    Permission

    Privileged account and standard account

    Modify system parameters.

    Create a tablespace.

    Kill a superuser process.

    Execute an untrusted language.

    Note

    Only PL/pgSQL is a trusted language. Other languages are untrusted and cannot be executed.

    User group permissions

    PolarDB for PostgreSQL is built on the PostgreSQL protocol and uses certain group permissions from PostgreSQL. The inheritance mechanism of these group permissions is detailed in the following table. These permissions have a union relationship with the permissions owned by privileged users. Specifically, the set of permissions is the sum of these two types of permissions.

    Permission

    Ownership

    Description

    pg_read_all_stats & pg_stat_scan_tables

    Full

    Reads related statistics.

    pg_signal_backend

    Full

    Signals another backend to cancel a query or terminate its session.

    pg_polar_superuser

    Full

    Contains a series of permissions of privileged users.

    pg_polar_replication

    Full

    Provides the streaming replication permission.

    pg_monitor

    Full

    Reads/executes various monitoring views and functions. The read and write permissions on local files are denied.

    pg_read_all_data & pg_write_all_data

    Limited

    Reads all user tables and most system tables and writes data to all user tables. Some system tables are set invisible for security reasons.

    pg_read_all_settings

    Limited

    Reads all configuration variables within granted permissions. Some variables are set invisible for security reasons.

    pg_read_server_files & pg_write_server_files

    Prohibited

    Not allowed to read or write local files.

    pg_execute_server_program

    Prohibited

    Not allowed to execute binary commands.