How to Grant All Privileges to Users in PostgreSQL

PostgreSQL offers a GRANT statement that is used to assign privileges to the database objects. The database object can be a schema, a table, a function, and so on. In Postgres, the GRANT statement assists the users in accessing and overriding the specific role. In Postgres, you can grant all privileges to a user via the "GRANT ALL" statement.

This blog post will present a comprehensive guide on granting all privileges to the users in Postgres via practical demonstration. So, let’s start.

How to Grant All Privileges to Users in Postgres?

When a role with the LOGIN attribute is created, it can log into the PostgreSQL database server. However, it cannot interact with the database objects until privileges are granted to that role. To grant all privileges to a user, follow the below syntax:

GRANT ALL 
ON tbl_name
TO rol_name;

In the above syntax:

  • GRANT is a statement that assigns privileges to the users.
  • ALL is an option used with the GRANT statement to give all the privileges to the users.
  • tbl_name represents a table.
  • rol_name specifies which role should be granted privileges.

Let’s understand it via practical examples.

Example: Grant All Privileges to User

This example presents a step-by-step procedure to grant all the privileges to the users in Postgres:

Step 1: Create a Role

Let’s create a role named “admin” with LOGIN privileges:

CREATE ROLE admin LOGIN PASSWORD 'cp12345';
img

From the output snippet, you can observe that a role has been created.

Step 2: Verify Roles

To verify the role’s creation, run the “\du” command as follows:

\du;
img

A role named “admin” has been created.

Step 3: Create Table

Now, let’s create a new table named “shortlisted_students” with three columns:

CREATE TABLE shortlisted_students(
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
student_email VARCHAR(100)
);
img

The table named “shortlisted_students” has been created successfully.

Step 4: Login as New User

Log in as an “admin” user from a new separate session:

img

Now, we are logged in as an “admin” user.

Step 5: Insert Data

Now try to insert data into the “shortlisted_students” table from the “admin” session:

INSERT INTO shortlisted_students(student_id, student_name, student_email) 
VALUES (1, 'Joseph', 'joseph@12345');
img

The above snippet shows that the user “admin” doesn’t have privileges to edit the shortlisted_students.

Step 6: Grant All Privileges

Now, log in as a superuser and grant all the privileges on the shortlisted_students table to the “admin” role. To do so, run the GRANT statement with the ALL option:

GRANT ALL ON shortlisted_students TO admin;
img

The output snippet shows that all the privileges have been granted to the admin role.

Step 7: Insert Data

Now run the insert query one more time:

INSERT INTO shortlisted_students(student_id, student_name, student_email) 
VALUES (1, 'Joseph', 'joseph@12345');
img

The output clarifies that one record has been inserted into the shortlisted_students table.

Step 8: Fetch Data

To get the data from the shortlisted_students table, we will execute the SELECT command as follows:

SELECT * FROM shortlisted_students;
img

The output proves that all the privileges have been granted to the user.

Conclusion

PostgreSQL offers a GRANT statement that is used to assign privileges to the database objects. In Postgres, you can grant all privileges to a user via the "GRANT ALL" statement. When a role with the LOGIN attribute is created, it can log into the PostgreSQL database server. However, it cannot interact with the database objects until privileges are granted to that role. This blog post demonstrated the working of the GRANT statement via practical examples.