How to Grant Privileges to the User in PostgreSQL

1. Brief Introduction of Privileges in PostgreSQL
2. Use of the GRANT Keyword in PostgreSQL

In PostgreSQL, all database objects, such as a table or a view, have an owner. The owner is usually the user who created the object.

Consequently, only the owner has the right to modify or delete objects created by them, and no other user can do so by default. If the owner wants to allow some other user to perform specific actions on their object, they need to grant them the privileges.

In this tutorial, we demonstrate how an owner can grant all privileges to another user in PostgreSQL. Let’s begin with the basics and learn what privileges are.

Brief Introduction of Privileges in PostgreSQL

Privileges are permissions related to particular database objects. In PostgreSQL, we have the following types of privileges:

Note: It is impossible to grant a user the privilege to DROP an object or change its definition.

All these privileges represent different actions that a user can perform on a database. For example, a user can not CREATE a table in a particular database if the CREATE privilege has not been granted.

So how does one grant all privileges to a user? For example, let us create a temporary user by running the following command:

CREATE USER myuser WITH PASSWORD 'password';

Next, we create a sample table and fill it with some data:

create table person(
    name varchar(30) not null,
    age int not null
);

insert into person values ('Ali', 20), ('Fatima', 19), ('Hassan', 22);

All privileges are given to a user using the GRANT keyword, which is explained below.

Use of the GRANT Keyword in PostgreSQL

The basic syntax of the GRANT keyword is as follows:

GRANT privilege
ON object
TO {PUBLIC | GROUP group | username}

Let us look at each component of the syntax one by one:

• privilege - Here, we write the privilege(s) we want to grant to the user. In our case, we will write ALL PRIVILEGES since we are learning how to grant all privileges.
• object - A database object includes, but is not limited to, a table, view, sequence, database, function, procedure, or schema.
• PUBLIC - If we write this keyword, we will grant the privilege(s) to all users in all roles and groups.
• GROUP - A group is a type of role in PostgreSQL. A role can be a group or individual user that owns a database object. We can grant all privileges to a group in PostgreSQL by specifying its name.
• username - If we want to grant all privileges to an individual user, we write their username here.

We will now demonstrate the usage of this syntax with an example. Suppose we want to grant all privileges to myuser created above on our defined table person.

For this, we can execute the following query:

GRANT ALL PRIVILEGES ON person TO myuser;

The output shows the following text:

It means that the specified privilege(s) have been granted successfully. In PostgreSQL, it is optional to write PRIVILEGES as written in the query above. Therefore an alternative is:

GRANT ALL ON person TO myuser;

It also works in the same way. Similarly, we can also create other database objects and grant all privileges on them to a user. Let us create a sample procedure as an example:

create procedure just_an_example()
language plpgsql
as $$
begin
select * from person;
end; $$

Now, we can execute the query given below to grant all privileges on it to myuser:

GRANT ALL PRIVILEGES ON PROCEDURE just_an_example() TO myuser;

It will be successfully granted:

We can also add the WITH GRANT OPTION while granting all privileges. It will mean that now the user also has permission to grant privileges to other users. For example:

GRANT ALL PRIVILEGES ON PROCEDURE just_an_example() TO myuser WITH GRANT OPTION;

After this query is executed, myuser is allowed to further grant privileges to any user they want, initially only entitled to the owner of just_an_example().

The method we have explained above includes granting all privileges to the user directly. However, a better approach is to grant all privileges to a group and make the user a member.

This technique is better because it helps organize privileges or even revoke them when dealing with several users. Let us see how this is done.

Firstly, we will create a group in the following way:

CREATE GROUP mygroup;

It will be successfully created as shown in the output below:

Now, we will grant all privileges on our table person to the group mygroup using the following query:

GRANT ALL PRIVILEGES ON person TO mygroup;

The output shows that we have successfully granted the privileges:

Next, we will add myuser as a member of mygroup using the following syntax:

GRANT mygroup TO myuser;

It gives the following output, confirming the membership:

In this way, we have granted all privileges on the table person to the group mygroup, and since myuser is a part of mygroup, they also have all privileges on person.

It sums up our discussion of how to grant all privileges on a database object to a user in PostgreSQL. We hope you have learned the usage and syntax of the GRANT keyword and the different methods we can use to grant all privileges to a user.