Restrict access to creating users on PostgreSQL

Question

Using PostgreSQL and would like to have only admins have the ability to create new users. While allowing some non-admin users read-write access to our tables.

I cannot find a way to get this done. I have a role called webuser to which I gave:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO webuser;

But now webuser has access to call CREATE USER and GRANT ROLE also. How can I remove access from webuser to do user-management operations while allowing SELECT, INSERT, UPDATE, DELETE on our database tables?

That grant does most definitely not grant the createuser privilege. — user330315
– user330315, Commented Jul 2, 2020 at 6:02

pifor · Accepted Answer · 2020-07-02 06:03:37Z

2

A user can only create other users if it has the createrole property: this property is not assigned by default when creating a user.

Doc. says:

CREATEROLE NOCREATEROLE

These clauses determine whether a role will be permitted to create new roles (that is, execute CREATE ROLE). A role with CREATEROLE privilege can also alter and drop other roles. If not specified, NOCREATEROLE is the default.

Either the user has been created with createrole or a superuser has run: alter user webuser createrole.

To revoke that privilege run alter user webuser nocreaterole

NB:

CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.

edited Jul 2, 2020 at 6:03

user330315

answered Jul 2, 2020 at 5:30

pifor

7,9722 gold badges12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Restrict access to creating users on PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related