CODEWITHSUNDEEP
sqlpostgresql
kos
kos

Reputation: 1791

Restrict access to creating users on PostgreSQL

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?

Upvotes: 0

Views: 368

Answers (1)

pifor
pifor

Reputation: 7882

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.

Upvotes: 2

Related Questions