Creating new Role in Postgres

Question

I want to write an IF Condition in PostgresQL which will CREATE a new ROLE in the DB, but should work in such a way that.

I first check in an IF statement if the existing user has the privilege to CREATE a new ROLE and if the user has the privilege only then execute the CREATE ROLE statement.

Something like

IF ( Current user has privilege to create new ROLE ) THEN
   CREATE ROLE rolename;
end if;

Can this be done?

user330315 · Accepted Answer · 2020-06-24 13:27:39Z

2

I would simply try an run the create role statement and let it fail, notifying whoever runs the script that something went wrong.

However, you can do what you want by checking if the current user has the createrole privilege:

do
$$
begin
  if (select rolcreaterole 
      from pg_roles
      where rolname = current_user) then 

     create role rolename;
  end if;
end;
$$
;

answered Jun 24, 2020 at 13:27

user330315

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

1 Comment

ptilloo Over a year ago

Thanks you @a_horse_with_no_name

Collectives™ on Stack Overflow

Creating new Role in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related