User Account Permission Requirements for Google Cloud SQL for PostgreSQL and AlloyDB

To back up and restore PostgreSQL data on a Google application, verify that your environment has the required user account permission requirements.

User Account Requirements

• For DumpBasedBackupSet, verify that the PostgreSQL instance has a user account with sufficient privileges to perform the following:

  • Perform backup and restore operations of the database objects as part of the subclient content

    The following table illustrates the necessary privileges the user account should have to perform backup and restore operations:

    Operations	Privileges PostgreSQL User Should Have
    Backup	CREATEDB, CREATEROLE
    Restore	CREATEDB, CREATEROLE, INHERIT, LOGIN

  CONNECTION LIMIT, if set, should at least have a value of 2.

  Initially, the user account credentials are provided during the instance configuration after the installation of the agent. You can change the user account at the instance level.

  Note

  You may need to grant other privileges to the postgreSQL backup user to secure the objects in the database. To know more about database roles and privileges, see PostgreSQL documentation.

• If the database instances are configured across Google Cloud projects, create a service account in one project. In other projects that have PostgreSQL database instances, add an IAM principal with Cloud SQL Viewer permission.

  For creating a cloud account, use the service account for authentication that will discover PostgreSQL instances in all projects. You must use the same service account that is added as an IAM principal to create a cloud account. Verify that the Google Cloud VM instance has access to the PostgreSQL database instance.

• You do not require a root user account to install or operate the export-based backup and recovery solution. Use a non-root user account with sudo privileges for the installation and operations. For more information, see installation of UNIX agents by a non-root user with sudo privileges.

• You require SELECT privileges on all tables and objects within the schema or databases being backed up.

  The following statements are valid for PostgreSQL:

  • Version 14 or above

    • GRANT pg_read_all_data to user_name;

  • Version 13 or below

    • GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO user_name;