how to permit DROP table Azure Database for PostgreSQL flexible server

Yusif Nasirov 5

I want to prevent a user from dropping a table

But at the same time he can create it, but not drop it.

Mahesh Kurva 5 Reputation points Microsoft Vendor

2024-09-18T11:44:03.6366667+00:00
Hi @Yusif Nasirov,

Thank you for reaching out.

Regarding the question:

I understand your question. You want to give a user permission to create a table, but not allow them to delete the table.

Here are the steps:
Create a new role using the CREATE ROLE command.

CREATE ROLE table_creator;

Grant the CREATE permission to the role using the GRANT command.

GRANT CREATE ON DATABASE your_database TO table_creator;

Deny the DROP permission to the role using the REVOKE command.

REVOKE DROP ON TABLE your_table FROM table_creator;

Grant the role to the user using the GRANT command.

GRANT table_creator TO your_user;

After completing these steps, the user will be able to create tables, but not drop them.

For more information, please refer the document: Grant.

For more information, please refer the document: Revoke.

I hope this information helps. Please do let us know if you have any further queries.
Mahesh Kurva 5 Reputation points Microsoft Vendor

2024-09-19T09:55:11.2066667+00:00

Hi @Yusif Nasirov,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Dillon Silzer 57,311 Reputation points

2024-09-15T21:17:35.48+00:00
Hi Yusif,

Try revoking the user's roles and create a new role with limited abilities:

For example, create a role with specific privileges:

CREATE ROLE limited_role; GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO limited_role;

Then, assign this role to the user:

GRANT limited_role TO my_user;

If this is helpful please accept as answer or upvote.

Best regards,

Dillon Silzer | Cloudaen.com | Cloudaen Computing Solutions
Please sign in to rate this answer.
Yusif Nasirov 5 Reputation points

2024-09-17T20:01:54.1766667+00:00

I have done this method, but I need the user to be able to create a table, but not be able to delete it.

I don't know if it's possible and if it's even right, but I've just been given this task.

there's DELETE and there's DROP.

The user can delete something from a table, but should not be able to DROP the table.

I created a role for him and gave him permission to create. I need the user to not be able to DROP a table.
I hope I can explain and get the point across
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Your answer