Postgres access privileges

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

DBAs and other database management professionals work with Postgres on a daily basis. Based on their job responsibilities, when new workers arrive, some may require database access. You can assign access by creating user roles. But when a worker leaves a department, say for a promoted position, that worker may no longer need to view the data. In that case, it’s imperative to revoke access and limit it to only those that need them to complete their job requirements. Whatever the situation, Postgres privileges may vary from person to person, group to group, role to role. Managing roles is simple with this tutorial that explains how to assign Postgres privileges that enable you to grant or revoke database access to users.

Prerequisite

What is PostgreSQL access privileges

DBAs assign roles to groups of users who require access to Postgres databases. Roles indicate if users in a specific group can view, edit, or otherwise manipulate certain data. For example, one role can be assigned to a group that can just view data, while another role can be set for a group that can view and print data. Yet, another role can be given to a group that can view, edit, and print information in the same database.

The combinations are limitless. Assigning roles to groups saves time in giving access to database privileges, because otherwise, DBAs would manually give privileges to each user on a one-by-one basis.

PostgreSQL privileges allow database professionals to quickly grant and revoke database access in an efficient way.

Commonly used Attributes

Use these commands in a new role setup process.

  • LOGIN – This role gives a user the ability to login to a Postgres database.

  • SUPERUSER – This role allows a user to have all-access, all database privileges.

NOTE Superusers have no boundaries on manipulating data; therefore, verify that this is what you had in mind when assigning a superuser role to a user. Typically, the DBA’s role or the person who manages the entire Postgres database has a superuser role, which is one with elevated privileges. For security measures, that role is not commonly granted to several users or a group of users; therefore, proceed cautiously when assigning a superuser role.

  • CREATEDB – The role that gives a user permission to create databases.

  • CREATEROLE – The role that allows a user to create roles.

Create and Drop Roles

  • Begin to create a role using the CREATE ROLE command like this:
1
CREATE ROLE <name_of_role>;
  • Next, specify that the role is for the database user like this:
1
CREATE ROLE db_user;

If successful, you should see the CREATE ROLE response which indicates you are ready to begin creating a user role.

  • Confirm that it was completed correctly with the \du command like this:
1
2
3
4
5
6
7
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 db_user   | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rommel    | Superuser                                                  | {}

NOTE: When setting up Postgres privileges, it won’t accept characters that are reserved and invalid.

  • Now you’re ready to drop roles with this command:
1
DROP ROLE <name_of_role>;
  • Earlier, you created the db_user role. Drop it with this command like this:
1
DROP ROLE db_user;

When finished, you should receive a “DROP ROLE” response from Postgres.

  • Just as you did before, go ahead and confirm that you created and dropped the role successfully with the \du command. When you see the role list, you should no longer see the db_user there.

Postgres LOGIN privilege

Give the privilege LOGIN to the role you just created and dropped with this command:

1
CREATE ROLE <name> WITH <permission>;

NOTE: As soon as you assign the LOGIN privilege to the role, it’s granted immediately to any user who is assigned to the role.

Enter this command to test out your work:

CREATE ROLE sample_user WITH LOGIN PASSWORD '1234';

The above command shows that the user has the ability to change the password to a more secure one.

Altering user ROLE

  • Modify the user role with the ALTER ROLE command like this:
1
ALTER ROLE <role_name> WITH <optional_attributes>
  • Alter the sample_user role by giving it an attribute that reflects its prior state like this:
1
ALTER ROLE sample_user WITH NOLOGIN;

When finished, you should receive a response from Postgres that states “ALTER ROLE.”

  • Use the du command to confirm the success of altering the role.

  • The result should look similar to this:

1
2
3
4
5
6
                                    List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rommel      | Superuser                                                  | {}
 sample_user | Cannot login

You successfully revoked Postgres privileges of the sample_user role by assigning the “Cannot login” attribute to it.

Conclusion

Postgres privileges give DBAs the ability to assign roles for controlling database access. The process starts with using the CREATE ROLE command. With it, you can create, drop, and alter roles. These executions are necessary to secure databases in an efficient manner on an ongoing basis.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.