CODEWITHSUNDEEP
postgresql
JimP
JimP

Reputation: 23

Postgres: Restrict user account/role ability to create database objects

Excuse if a duplicate...I looked for it but didn't find it.

I need my user account (role) to have standard CRUD permissions in tables, execute permissions for functions, and so forth.

But this role should be prevented from creating or deleting objects. Create table/create function (or delete or alter table) should be forbidden.

How to do this does not seem to be obvious from the documentation. Thanks in advance for any help or insight.

Upvotes: 0

Views: 1258

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61506

The parts of the documentation you should focus on is the PUBLIC pseudo-role (which comprises all users), and the public schema that gets created by default.

The purpose of the public schema is to let new users create objects without too much hassle, but if it's not desirable, you may remove the corresponding privileges, by issuing:

REVOKE ALL on schema public FROM public;

ALL in this context is a shortcut for CREATE, USAGE. Be aware that it happens only in the database you are connected to.

Another option is to just remove the public schema, and create another one without any predefined permission for your application objects.

The execution permissions for functions are dealt separately by GRANT EXECUTE.

The permission to drop a table does not come from the schema that contains it, only the owner of a table or a superuser can drop it.

See https://www.postgresql.org/docs/current/static/sql-grant.html and
https://www.postgresql.org/docs/current/static/ddl-schemas.html for more.

Upvotes: 1

Related Questions