Introduction to PostgreSQL GRANT
In PostgreSQL, whenever you want to assign privileges for certain database object then you can use the GRANT query statement. GRANT query also provides us with one more facility to grant membership to a particular role. Whenever a new user is created, it has the default privileges on the database object. These access privileges are overridden by the GRANT command. The database objects include the tables, columns on tables, stored procedures, functions, sequences, database servers, foreign-data wrapper, views, schemas, foreign server, tablespace, and procedural languages. Any of this database object can be allowed for access to a particular role by using a PostgreSQL grant.
Examples of PostgreSQL GRANT
Given below are the examples:
Example #1
We can create a new user by using the CREATE USER command and check the privileges assigned to the user from the table table_privileges in information_schema by firing the select query for a particular grantee.
Let us check the list of users in my database server by firing \du metacommand.
Code:
\du
Output:
We have two users named payal and Postgres. Postgres is the default user present in the PostgreSQL database that is the superuser and has all privileges while payal user is created by me for demonstration purpose that does not has any privileges.
We can check that by firing the following query.
Code:
SELECT table_schema as schema, table_name as table, privilege_type as privilege
FROM information_schema.table_privileges
WHERE grantee = 'postgres';
Output:
Firing the above command for payal user.
Code:
SELECT table_schema as schema, table_name as table, privilege_type as privilege
FROM information_schema.table_privileges
WHERE grantee = 'payal';
Output:
Now we will see the grant query syntax and example one by one by assigning different database object privileges to our user named payal.
Example #2
Granting table privileges.
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] name_of_table [, ...]
| ALL TABLES IN SCHEMA name_of_schema [, ...] }
TO { [ GROUP ] name_of_role | PUBLIC } [, ...] [ WITH GRANT OPTION ]
The above syntax for granting privilege on the certain table name to the particular user has name_of_table as the table name that you want to grant the privilege of, name_of_schema is schema name to which that table belongs and name_of_role is the user name that in our case is payal.
Let us check the list of tables present in Postgres database by firing \dt command.
Now, we have to give insert privilege to payal user on the teams table.
Code:
GRANT INSERT ON TABLE teams TO payal;
Output:
Now, after firing select command for privilege checking.
Code:
SELECT table_schema as schema, table_name as table, privilege_type as privilege
FROM information_schema.table_privileges
WHERE grantee = 'payal';
Output:
Hence, insert privilege is granted to the payal user on table teams.
Example #3
Granting table column privileges.
Syntax:
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
[,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
ON [ TABLE ] name_of_table [, ...]
TO { [ GROUP ] name_of_role | PUBLIC } [, ...] [ WITH GRANT OPTION ]
Now, we will grant the update privilege on certain columns of the educba table. Let us describe educba table by using \d educba; metacommand.
Code:
\d educba;
Output:
Now we will use format 2 of grant query statement to assign update privilege on certain columns of educba table using the following query.
Code:
GRANT UPDATE(technologies, workforce, address) ON TABLE educba TO payal;
Output:
Example #4
Granting sequence privileges.
Syntax:
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE name_of_sequence [, ...]
| ALL SEQUENCES IN SCHEMA name_of_schema [, ...] }
TO { [ GROUP ] name_of_role | PUBLIC } [, ...] [ WITH GRANT OPTION ]
For granting all privileges on all sequences to payal user we will use format 3 of grant query.
Code:
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO payal;
Output:
Example #5
Granting database privileges.
Syntax:
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE name_of_database [, ...]
TO { [ GROUP ] name_of_role | PUBLIC } [, ...] [ WITH GRANT OPTION ]
Let us check all databases using \l command.
Code:
\l
Output:
To grant all privileges on educba database to payal user, we will use the following query statement.
Code:
GRANT ALL ON DATABASE educba TO payal;
Output:
Example #6
Granting function privileges.
Syntax:
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTION name_of_function ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
| ALL FUNCTIONS IN SCHEMA name_of_schema [, ...] }
TO { [ GROUP ] name_of_role | PUBLIC } [, ...] [ WITH GRANT OPTION ]
Let us create one function.
Code:
CREATE OR REPLACE FUNCTION iseligible(int) RETURNS void AS $$
DECLARE
age int:=$1;
BEGIN
IF age > 18 THEN RAISE NOTICE 'You are eligible to vote as your age is %!', age;
END IF;
END;
$$ LANGUAGE plpgsql;
Now, for granting all privileges to payal user on is eligible() function, we can use the following query statement.
Code:
GRANT ALL ON FUNCTION iseligible TO payal;
Output:
Example #7
Granting schema privileges.
Syntax:
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA name_of_schema [, ...]
TO { [ GROUP ] name_of_role | PUBLIC } [, ...] [ WITH GRANT OPTION ]
To grant all permissions on the public schema to payal user, we can use the following query statement.
Code:
GRANT ALL ON SCHEMA public TO payal;
Output:
Example #8
Granting membership privileges.
We can grant membership of a certain role, user to other role or user using the following format of the grant.
Syntax:
GRANT name_of_role [, ...] TO name_of_role [, ...] [ WITH ADMIN OPTION ]
To grant Postgres role to payal, we can fire the following query statement.
Code:
GRANT postgres TO payal;
Output:
Let us check the output of \du command now.
Code:
\du
Output:
payal user is now a member of Postgres.
Recommended Articles
This is a guide to PostgreSQL GRANT. Here we discuss the introduction of PostgreSQL GRANT along with examples. We can grant privileges and make database objects accessible by using the grant command in Postgres and also assign membership to the users. You may also have a look at the following articles to learn more –
