CODEWITHSUNDEEP
databasepostgresqlauthorizationrolesprivileges
user3650102
user3650102

Reputation: 57

How to prevent schema modification by the user in PostgreSQL

How to prevent the user from performing schema modification such as adding new tables, deleting table columns etc. while enabling the user to perform the create, read, update and delete operations?

Upvotes: 1

Views: 1379

Answers (2)

Volodymyr Vintonyak
Volodymyr Vintonyak

Reputation: 105

According to documentation there is two possible privileges for schema CREATE | USAGE, where CREATE allows new objects to be created within the schema (what you need to prevent) and USAGE allows access to objects contained in the specified schema (what you want to keep).

So you need REVOKE CREATE ON SCHEMA ... FROM ... and GRANT USAGE ON SCHEMA ... TO ...

But, before that, be careful with privileges granted on PUBLIC, probably you have to REVOKE all that permissions on PUBLIC and GRANT them to necessary users.

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 248295

The best solution for that is to use two users:

  • one that owns the schema and all objects in it
  • another one that is granted the necessary permissions on the objects (USAGE on the schema and sequences, SELECT, INSERT, UPDATE and DELETE on the tables, etc.)

You can use ALTER DEFAULT PRIVILEGES to get the permissions right for all objects created in the future.

Upvotes: 0

Related Questions