PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Allowing new users to create new tables, columns, as well as deleting them in postgres DB

Currently running a self-managed postgresDB. I’m accessing via PGAdmin. I’m using the DB credentials for my FastAPI backend that runs alembic migrations.

I’m seeing that the root user of the DB can create, delete, update tables and columns. But when I created a new user just for a specified database, it can read and write records, but it cannot create new tables or edit any of the columns.

Stuff I’ve tried:

GRANT ALTER, DROP ON ALL TABLES IN SCHEMA public TO {USER};

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO {USER};

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO {USER};

GRANT USAGE ON SCHEMA public TO {USER};

GRANT CREATE ON SCHEMA public TO {USER};

And still, I wouldn’t be able to create or edit tables and columns.

Any other permission I need to set?

Tags:

2

Answers


  1. 0

    have you tried assigning roles at the database level?

    GRANT CONNECT ON DATABASE {database_name} TO {USER};

    GRANT TEMPORARY ON DATABASE {database_name} TO {USER};

    GRANT CREATE ON DATABASE {database_name} TO {USER};

    Login or Signup to reply.
  2. 0

    You’ll need to grant privileges at the database level since table/column modifications require database-level permissions:

    GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_user;

    Also ensure the user has proper role attributes:

    ALTER ROLE your_user CREATEDB;

    For alembic migrations specifically, the user needs:

    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO your_user;

    Check current permissions:

    du your_user
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search