Databases are crucial, and it’s the work of the administrator to control what different roles the users can do within a given database. As part of authorization, the administrator can define user entities within the database and grant or revoke various privileges to the roles.
That way, you attain control over who can access a database and what privileges they have if they can access the database. For instance, you can revoke the database modification or grant all privileges on the schema to a user or users in a given table. This guide details how to use PostgreSQL to grant a user all privileges on the schema to a user.
How To Grant Privileges to Users on PostgreSQL
When you create a new role, they get some privileges by default. However, most privileges require the administrator to grant them to different users to regulate the access and control of a schema. You can grant all the privileges at once or separately.
For this tutorial, let’s create a new role named linuxhint1.
# create role linuxhint1 login password ‘linuxhint’;
Note that we are logged in as postgres, the default role created once you install PostgreSQL.
With the role (user) created, we can list the available roles using the following command:
The available roles will be displayed in a table format.
Postgres is the default role and has most privileges already granted. However, the new role, linuxhint1, has no privileges until we grant it.
1. Granting a Specific Privilege to a User
The created role can’t modify the schema unless you permit it. Let’s verify this by first creating a table as the default role, postgres.
You can list the available relations, as shown below:
Next, open a new shell and log in to PostgreSQL using the other role, linuxhint1, that we created earlier using the following command:
The -d specifies that the role is to use the Postgres database.
Try to read the contents of the table that we created using the SELECT command.
It returns a permission denied error hindering the user from accessing the table.
In such a case, we need to grant the role privileges to select/view data from the given table using the following syntax:
To grant the privileges, use the Postgres session.
Once granted, try to rerun the previous command.
That’s it. You managed to grant the select privilege to the specific user.
2. Granting All Privileges on Schema to a User
So far, we’ve managed to grant only one privilege on schema to a user. Well, that’s not enough. The user can’t modify the schema unless you grant it all privileges, such as inserting and updating.
There are different ways of achieving this. First, let’s grant the user all the privileges to a given table using the following syntax:
The user can interact with and modify the particular table. However, they can’t work with other tables in the schema.
To grant all privileges on all the tables in a particular schema, use the following syntax:
Lastly, you can grant specific privileges, such as inserting or selecting all tables on the schema to a given role.
The role can select data in all tables in the specified schema. That’s how you define what privileges to grant on the schema to different users.
Conclusion
PostgreSQL is a powerful DBMS. It has different features, including allowing the administrator to grant various privileges to users. Granting all privileges on the schema to users means allowing the user to modify and interact with all tables in the specified schema. We’ve seen how to apply that and other ways of granting privileges on the schema to roles using the GRANT statement in PostgreSQL.
