Why Did I Fail to Create an Object on the postgres Database as a Common User?

Symptom

• Creating a Schema as a Common User

  Command: CREATE SCHEMA my_schema;

  Error: ERROR: permission denied for database postgres

  

• Creating a Table as a Common User

  Command: CREATE TABLE my_table(id int PRIMARY KEY,name VARCHAR(30));

  ERROR: permission denied for schema public

  

Solution

A common user cannot create objects on the postgres database, but a root user can.

Switch to user root and ensure that your database kernel allows for root privilege escalation. For details, see Privileges of the root User.

Privileges of the root User

RDS for PostgreSQL provides permissions for the root user. To create objects on an RDS for PostgreSQL database without operation risks, escalate your account to root privileges when necessary.

The following table describes root privilege escalation in different versions.

Escalate to root privileges when you need to:

• Create an event trigger.
• Create a wrapper.
• Create a logical replication publication.
• Create a logical replication subscription.
• Query and maintain replication sources.
• Create a replication user.
• Create a full-text index template and parser.
• Run the vacuum command on a system table.
• Run the analyze command on a system table.
• Create an extension.
• Grant an object permission to a user.