Postgres access privileges

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

• Install PostgresSQL and then configure it.

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:

• Next, specify that the role is for the database user like this:

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:

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:

• Earlier, you created the db_user role. Drop it with this command like this:

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:

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:

• Alter the sample_user role by giving it an attribute that reflects its prior state like this:

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:

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.