Role Management for PostgreSQL

Compose deployments come with an 'admin' role with full privileges over your fresh deployment. If you use the data browser to create databases, tables, or other users, it is using the 'admin' role. You may use 'admin' for everything, especially if you are unfamiliar with privilege and access management in PostgreSQL. Using 'admin to provision new databases, tables, and perform all your queries will ensure the permission to access all the data, connections, and queries will be accessible to the 'admin' user.

This may not be appropriate for all situations, so you may provision new users for deployment and make use of PostgreSQL's robust role and privilege functionality.

🚧

Superuser

At this time there is no way for you to provision a superuser account.

Roles

Database roles carry across all databases in a cluster/deployment. Roles are provisioned with attributes that control who can login, create databases, and create other roles.

The ability to provision new roles and attributes is available through the Browser. Since roles carry through all databases, click on any database in your deployment and select Roles from the side-bar. The 'admin' user has the roles login, createdb, createrole across your entire deployment.

Adding a new role to your deployment

To add roles, navigate to the Roles panel through the Browser. In the upper-right is an Add User button. When you click on it, a box appears with the pre-populated CREATE ROLE command. Fill in the name and password fields, and toggle specific privileges under the command. Available privileges are:

• LOGIN - the role will be able to login and access the deployment
• CREATEDB - the role will be allowed to create databases
• CREATEROLE - the role will be allowed to create other roles

You may also add roles through psql using the 'admin' role. The syntax is:
CREATE ROLE new_user WITH PASSWORD 'newpassword_here';

More documentation on role creation can be found in the PostgreSQL documentation.

Please note that for any user created through psql will NOT automatically have privileges set. If you still wish to use 'admin' to control the new user, you will have to set those privileges manually. Full details on setting privileges and Compose-specific privilege management are in the following section, Privileges on Compose PostgreSQL deployments

Privileges on Compose PostgreSQL

In PostgreSQL, the creator of an object, such as a database or table, is the object's owner and has full privileges to that object. For any role that is not the owner, you have to assign privileges for any object that it does not own. If you are using 'admin' exclusively with your deployment, you do not have to worry about privileges, since 'admin' will own all the objects on your deployment.

For any user you create, you will have to make set their privileges to objects appropriately. For example, if you create a table 'names' using 'admin', and would like a user 'jerry' to be able to use this table, 'admin' can issue the command
GRANT ALL PRIVILEGES ON names TO jerry;

Alternatively, if you would like the new user to have all the same privileges as the 'admin' role, you can grant them via:
GRANT admin TO jerry;
Granting the same priviledges as 'admin' includes many other privileges across databases, including access to other tables and the ability to terminate the connections of other users. Use with caution.

Users created via psql

If you use the UI to make a user, privileges are automatically set so that the 'admin' user is able to access all of the ojects created by that user. If you create a user through psql, however these privileges have to be set manually.

To keep access control consistent through all users (UI-created and psql-created) and make every object created by any user on the deployment accessible to 'admin' grant the new user's role to 'admin'. Example:

=> CREATE USER jerry WITH PASSWORD 'new_pass2';
CREATE ROLE

=> GRANT jerry TO admin;
GRANT ROLE

This creates a new user with a password and and then grants 'admin' privileges to everything that 'jerry' creates. This includes databases, tables, and also connections and queries.

This is also optional. You may create users that have their own domain and do not share their objects with 'admin'. If you then wish to grant 'admin' limited access to certain objects you can still grant those privileges to 'admin' or any other user with the GRANT command.

Group Roles

PostgreSQL uses a system of group roles to give similar privileges to individual roles. You can determine group role membership for all roles across your deployment by using the psql command \du.

This lists all roles, their attributes, and then also their group role membership. In the sample output above, 'admin' is a member of the {PaulS,example_application,testUIuser,psqluser2} groups. This means that 'admin' has privileges to all of those role's objects. This is a one-way relationship. Those other roles do not have privileges to 'admin' objects.

The same concept applies to the 'psqluser1' role. It is a member of the {admin} group so have privileges to all of the objects that 'admin' does. This includes all of the groups that 'admin' is a part of so 'psqluser1' has permissions to all of {PaulS,example_application,testUIuser,psqluser2} as well. Again it is one way so 'admin' does not have privilege to 'psqluser1' objects.

Still Need Help?

If this article didn't solve things, summon a human and get some help!