CODEWITHSUNDEEP
databasepostgresqlpgadmin-4alembic
Kamarul Adha
Kamarul Adha

Reputation: 153

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?

Upvotes: 1

Views: 64

Answers (3)

Zegarek
Zegarek

Reputation: 26347

All users are roles in Postgres - these are two names for the same thing. If you just want a new user to have all the same rights as an existing one, you can grant the whole role without having to re-type all its privileges:
demo at db<>fiddle

grant postgres to {USER};

The only thing this doesn't do is transfer actual ownership or role attributes - if the new role has nologin and you grant it the whole postgres role that has login on it, the attribute won't change.

That being said, it's always best to try and set up specific roles with minimum access necessary for the user to do what they need to do.

Upvotes: 1

Siddhant kumar Singh
Siddhant kumar Singh

Reputation: 31

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

Upvotes: 1

Rizky Ismail
Rizky Ismail

Reputation: 9

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};

Upvotes: 1

Related Questions