How to Perform the Postgres Grant on Database Objects
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.
- PostgreSQL must be properly installed and configured on the local operating system. Execute the
psql -Vcommand in a terminal to view the currently installed version of the PostgreSQL interactive shell.
The Postgres grant
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:
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:
CREATE USER new_user WITH PASSWORD '1234';
\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.
\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:
CREATE TABLE employee(emp_id SERIAL PRIMARY KEY, fullname TEXT NOT NULL, age INT NOT NULL);
Now insert the following records into the table:
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
SELECTstatement 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:
GRANT SELECT ON employee TO new_user;
Note that this will create a read only role in PostgreSQL.
INSERTcommand 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:
GRANT INSERT ON employee TO new_user;
UPDATEstatement can be used to grant a user permission for specific columns listed in the specified table as shown in the following example:
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
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:
GRANT ALL ON employee TO new_user;
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