PostgreSQL - Creating and Dropping Roles

For testing and production use of our database server, we'll want to create additional roles, as it's not recommended to work regularly in our databases as the default superuser role.


However, to create additional roles we do need to run some commands as the postgres superuser role. This will require a login as the Linux user named "postgres", as was explained in the previous article.

First, we need to login to our slice as a normal Linux user, then:

# sudo su - postgres

Connect with psql

Now connect to the database server using the psql client, as the postgres role:

postgres@demo:~$ psql -U postgres
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

Creating a Role

Connected with the psql client, we'll create a role that has the LOGIN attribute and a non-empty MD5-encrypted password:

postgres=#CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1';

Note the required trailing semicolon ( ; ) at the end of the SQL statement. The single-quotes ( ' ' ) are not part of the password, but must enclose it.

Did it work? We can check using '\du' command:

postgres=# \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of 
 demorole1 | no        | no          | no        | no limit    | {}
 postgres  | yes       | yes         | yes       | no limit    | {}
(2 rows)

Dropping a Role

What if we want to drop (delete, remove) a role? Easy:

postgres=#DROP ROLE demorole1;

If we check with the '\du' command we'll see that 'demorole1' is no longer listed.

Alternative: createuser and dropuser

Alternatively, we can create and drop database roles using the createuser and dropuser shell commands, which are basically "wrappers" for the CREATE and DROP SQL statements. They are included in a standard postgres installation.

With our present setup, we can only run these commands (successfully) as the postgres Linux user. We're still connected with the psql client, so let's exit with Ctrl-D or the '\q' command:

postgres=# \q

Good, we have a shell prompt as the postgres Linux user.


With createuser we'll create a non-superuser role that has the LOGIN attribute.

postgres@demo:~$ createuser -PE demorole2

With the '-P' flag we're prompted to set a password for the new role, and the '-E' flag indicates the password should be stored as an MD5-encrypted string.

Enter password for new role: 
Enter it again: 

Having supplied and confirmed the password, we're returned to a shell prompt. If we reconnect with psql and run the '\du' command, we'll get this:

postgres=# \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of 
 demorole2 | no        | no          | no        | no limit    | {}
 postgres  | yes       | yes         | yes       | no limit    | {}
(2 rows)


We can drop (delete, remove) a role with the dropuser shell command:

postgres@demo:~$ dropuser -i demorole2
Role "demorole2" will be permanently removed.
Are you sure? (y/n) y

The '-i' flag provides a confirmation prompt, which is a good safety measure when running a potentially destructive command.

Creating a superuser

On occasion, we'll want to create additional superuser roles, e.g. when we have a database programmer whom we trust to administer our postgres server.

We can do this with the 'createuser' shell command and the '-s' flag:

postgres@demo:~$ createuser -sPE mysuperuser

Alternatively, we can do the same thing from within a psql session, when we're connected as the postgres role (or another existing superuser):


We've set the LOGIN attribute and a non-empty password — important if this superuser role will be specified for local and remote connections to the database. We've also set the CREATEDB and CREATEROLE attributes. With those attributes specified, our SQL statement will match the action of the 'createuser -sPE' command.

© 2015 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License

See license specifics and DISCLAIMER