RDS for PostgreSQL does not provide superusers, which brings difficulties to many cloud users. This is because of the following reasons:
  • The superuser of PostgreSQL has almost all Database permissions, and can even directly modify system tables, which is potentially risky.
  • The superuser of RDS for PostgreSQL uses O&M instances to manage operations such as stream replication and backups. In other words, it should be completely left to cloud services.
  • The common user permissions of apsaradb RDS for PostgreSQL applications are sufficient to allow users to manage their data in the cloud.

Basic principles

  • A common user can only create schemas in a database where it is the owner.
  • Objects in a database have a schema. Common user can create objects such as tables in public mode. Objects in other schemas must be the schema owner or be authorized.
  • You must be the owner of an object (in addition to a user group) to manage a database object.

Exceptions and solutions

In a multi-user environment, apsaradb RDS for PostgreSQL users cannot use common user to manage objects created by other common user, and have no permissions on the operation objects.

You can use the Premier account created in the console to create an administrator account and then grant the sub-account permissions to the administrator account. Such a user group can be used to manage all other common user and their objects in the group using a single administrator account, it can manage the objects of multiple users and achieve a certain degree of permission isolation, you can also use this method to manage your instances in the cloud.

Note By default, the administrator account has the INHERIT permission, which determines whether a role inherits the permissions of the role to which it belongs. A role with an INHERIT attribute can automatically use any permissions that have been granted to the group to which it belongs, directly or indirectly.