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:
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 thedb_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