Learn about the operations the pg_ba_admin role can perform and its Postgres built-in roles.
A user with the pg_ba_admin role can perform many operations typically reserved for the Postgres superuser. For example, they can:
- Create roles.
- Create databases.
- Create, modify, or delete any non-superuser role, including setting passwords.
- SET ROLE to any user except a superuser without requiring a password.
- Grant pg_ba_admin membership and the corresponding powers to other roles.
- Read contents of all Postgres system catalogs in pg_catalog.
- Read and write to all user objects, that is, tables, views, and so on.
- Perform all monitoring functions on the Postgres instance. This role has the pg_monitor role.
- Install supported EDB extensions. See Supported Postgres extensions and tools.
- Bypass row-level security policies.
- Create foreign servers for foreign data wrappers. Use might be confined by network access control rules.
- Grant the pg_checkpoint role to itself and to other users
A user with the pg_ba_admin role can't perform the following operations that a Postgres superuser can:
- Execute programs on the server. pg_ba_admin doesn't have the pg_execute_server_program role.
- Create tablespaces, casts, operator classes, access methods, and text search templates.
- Read and write files on the server. pg_ba_admin doesn't have the permissions pg_read_server_files and pg_write_server_files.
- Define LEAKPROOF functions.
- Set parameters related to pg_audit (PostgreSQL only) or edb_audit (EDB Postgres Advanced Server only).
- Create functions in non-trusted languages such as Python and C.
- Install unsupported extensions. See Supported Postgres extensions and tools.
- Use SQL to set any superuser-only configuration parameters not explicitly listed as allowed. Administrators can modify some parameters using the BigAnimal portal. See Modifying database configuration parameters.
- Execute ALTER SYSTEM ... SET to modify the PostgreSQL configuration. Administrators can use the BigAnimal portal instead. See Modifying database configuration parameters.
- Execute CREATE LANGUAGE with custom handlers. Administrators can use CREATE EXTENSION for supported languages.
- Execute CREATE FOREIGN DATA WRAPPER with custom handlers. Use CREATE EXTENSION for supported foreign data wrappers.
- Execute CREATE TYPE for basic types. Composite types and domains are allowed.
- Execute REINDEX system catalogs.
- Execute index maintenance operations of catalog tables.
- Execute SET SESSION AUTHORIZATION
A user with the pg_ba_admin role can't perform the following additional operations using an EDB Postgres Advanced Server distribution:
- Use portions of the DBMS package that require writing the network or the file system
- Execute CREATE USER IDENTIFIED BY
- Create resource groups
- Access the network or make use of the EDB Postgres Advanced Server http packages that access the network
- Change the encoding using DBMS_SESSION
- Make use of the EDB Postgres Advanced Server UTIL_FILE packages that access the file system
- Change qreplace_function settings
- Use the DBMS_AQ package
- Use the UTL_HTTP package
- Use the UTL_TCP package
- Use the UTL_SMTP package
A user with the pg_ba_admin role has the following roles that are predefined in Postgres.
|Read all data (tables, views, sequences) as if having SELECT rights on those objects and USAGE rights on all schemas, even without having it explicitly. This role doesn't have the role attribute BYPASSRLS set. If RLS is being used, an administrator might want to set BYPASSRLS on roles that this role is granted to.
|Write all data (tables, views, sequences) as if having INSERT, UPDATE, and DELETE rights on those objects and USAGE rights on all schemas, even without having it explicitly. This role doesn't have the role attribute BYPASSRLS set. If RLS is being used, an administrator might want to set BYPASSRLS on roles that this role is granted to.
|Read all configuration variables, even those normally visible only to superusers.
|Read all pgstat* views and use various statistics-related extensions, even those normally visible only to superusers.
|Execute monitoring functions that might take ACCESS SHARE locks on tables, potentially for a long time.
|Read/execute various monitoring views and functions. This role is a member of pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables.
A user with the pg_ba_admin role doesn't have, and can't be granted, the following roles that are predefined in Postgres.
|Postgres predefined role
|Allow reading files from any location the database can access on the server with COPY and other file-access functions.
|Allow writing to files in any location the database can access on the server with COPY and other file-access functions.
|Allow executing programs on the database server as the user the database runs as with COPY and other functions that allow executing a server-side program.