How to Perform the Postgres Grant on Database Objects

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In Postgres, the owner of an object or database can grant privileges to modify or view the database or the objects contained in it. The Postgres grant statement has two rudimentary variations, to provide privileges on a database object itself or to provide specified user roles. More precisely, the PostgreSQL GRANT command is used to provide users with specific database privileges on the database as a whole, on just the individual objects contained in the database or to define user roles. This tutorial will provide explanations and examples on the ways of executing the Postgres GRANT statement on database objects.

Prerequisites

  • PostgreSQL must be properly installed and configured on the local operating system. Execute the psql -V command in a terminal to view the currently installed version of the PostgreSQL interactive shell.

The Postgres grant

The Postgres GRANT statement can be used in two different ways. The first is to provide access to database objects such as tables and columns, sequence and procedural language, schemas, tablespace and view functions. The second way the Postgres GRANT command can be used is with user roles to grant permission to perform certain specified tasks.

Following is the primary syntax of the Postgres grant statement used to describe access privileges:

1
GRANT privilege ON object TO GROUP;

To better understand how the privileges option works, execute the following command to create a new user in PostgreSQL:

1
2
CREATE USER new_user WITH PASSWORD '1234';
CREATE ROLE

NOTE: The \du command can be used to show the list of current users in the PostgreSQL database cluster.

Next, access must be obtained to the database and a new table created for granting privileges to specified user.

Execute the \l command to display the list of databases in Postgres. Then execute the \c command followed by the database name to connect.

Once connected to the database, create the following table in PostgreSQL:

1
CREATE TABLE employee(emp_id SERIAL PRIMARY KEY, fullname TEXT NOT NULL, age INT NOT NULL);

Now insert the following records into the table:

1
2
3
4
5
6
INSERT INTO employee(fullname, age) VALUES
    ('Michael Swatsckiz', 22),
    ('Brenica Grab', 25),
    ('Anthony Martis', 27),
    ('Rebeca Nays', 24),
    ('Edison Great', 26);

These are some of the privileges that can be granted for database objects. For this example, provide the new_user with a privilege to the above “employee” table.

  • Note that the SELECT statement can be used to select any column of a specified table or used for view and sequence functions. As shown in the following example, privileges can also be granted to make an object readable:
1
GRANT SELECT ON employee TO new_user;

Note that this will create a read only role in PostgreSQL.

  • The INSERT command can be used to grant a user specific permission to insert rows into specified columns in a specific table. This is demonstrated in the following example:
1
GRANT INSERT ON employee TO new_user;
  • The UPDATE statement can be used to grant a user permission for specific columns listed in the specified table as shown in the following example:
1
GRANT UPDATE ON employee TO new_user;

Note that this privilege has the ability to grant truncating privileges on the object.

The PostgreSQL grant all privilege example

The GRANT statement can also be used to set up all-access privileges to all database objects. This privilege only needs to be set up once and will remain in force until revoked. Execute the following ALL option to grant all privileges to the new_user:

1
GRANT ALL ON employee TO new_user;

Conclusion

This tutorial provide explanations and examples for performing the Postgres GRANT statement on database objects. The tutorial gave an overview of the GRANT statement functions and provided the basic syntax of the command as it is used to provide access privileges. The article covered how to use the \du command to show the list of current users in the PostgreSQL database cluster, how to grant privileges to specified user, how to connect to the database, how to create a new table and then provide the user with privileges. The tutorial explained creating a read only role in PostgreSQL and how to use the UPDATE statement to grant a user permission for specific columns in a specified table and gave working examples. The tutorial also explained how to use the GRANT statement to set up all-access privileges. Keep in mind that the Postgres GRANT command can be used to provide access to database objects or used used to define user roles to perform tasks. Also remember, that when used in conjunction with the Postgres GRANT command, the SELECT statement can be used to select any column of a specified table or used for view and sequence functions.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.