Postgresql – How to create a role that cannot create or alter a table

permissionspostgresqlrole

I want to create a role named manager in postgresql . This role can update, select, delete and insert data in all tables but users that belong to this role cannot create a table nor modify the schema.

I tried the following sql statement:

CREATE ROLE manager
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

How can I achieve this condition?.

Best Answer

The privilege to create tables is granted to new roles automatically. You need to REVOKE the role's CREATE privilege on the schema (not the database):

REVOKE CREATE ON SCHEMA myschema FROM manager;

Per documentation on GRANT:

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.)

And REVOKE all direct privileges any role might have in respective schemas. By default, plain roles only have privileges for the schema public and schemas they create themselves.

Related Question