EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL GRANT

PostgreSQL GRANT

Payal Udhani
Article byPayal Udhani
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 12, 2023

PostgreSQL GRANT

Introduction to PostgreSQL GRANT

In PostgreSQL, you can use the GRANT query statement whenever you want to assign privileges for a certain database object. 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. The GRANT command overrides these access privileges. The database objects include the tables, columns on tables, stored procedures, functions, sequences, database servers, foreign-data wrapper, views, schemas, foreign servers, tablespace, and procedural languages. Any of these database objects can be allowed to access a particular role by using a PostgreSQL grant.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Examples of PostgreSQL GRANT

Given below are the examples:

Example #1

We can create a new user using the CREATE USER command and check the user’s privileges 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:

postgreSQL GRANT 1

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 I create a Payal user 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:

postgreSQL GRANT 2

postgreSQL GRANT 3

Firing the above command for Payal users.

Code:

SELECT  table_schema as schema, table_name as table, privilege_type as privilege
FROM   information_schema.table_privileges
WHERE  grantee = 'payal';

Output:

postgreSQL GRANT 4

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

We are 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 a 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 as the schema name to which that table belongs, and name_of_role as the user name in our case is Payal.

Let us check the list of tables present in the Postgres database by firing the \dt command.

Now, we have to give insert privilege to the Payal user on the team’s table.

Code:

GRANT INSERT ON TABLE teams TO payal;

Output:

table privileges.

Now, after firing the 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:

postgreSQL GRANT 6

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 the educba table by using \d educba; metacommand.

Code:

\d educba;

Output:

column privileges.

Now we will use format 2 of the grant query statement to assign update privilege on certain columns of the educba table using the following query.

Code:

GRANT UPDATE(technologies, workforce, address) ON TABLE educba TO payal;

Output:

postgreSQL GRANT 8

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 the Payal user, we will use format 3 of the grant query.

Code:

GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO payal;

Output:

sequence privileges

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 the \l command.

Code:

\l

Output:

database privileges.

We will use the following query statement to grant the Payal user all privileges on the educba database.

Code:

GRANT ALL ON DATABASE educba TO payal;

Output:

postgreSQL GRANT 11JPG

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, we can use the following query statement to grant all privileges to the Payal user on an eligible() function.

Code:

GRANT ALL ON FUNCTION iseligible TO payal;

Output:

function privileges.

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 ]

We can use the following query statement to grant the Payal user all permissions on the public schema.

Code:

GRANT ALL ON SCHEMA public TO payal;

Output:

schema privileges.

Example #8

Granting membership privileges.

We can grant membership of a certain role, user to other role, or user using the grant’s following format.

Syntax:

GRANT name_of_role [, ...] TO name_of_role [, ...] [ WITH ADMIN OPTION ]

We can fire the following query statement to grant the Postgres role to payal.

Code:

GRANT postgres TO payal;

Output:

membership privileges.

Let us check the output of the \du command now.

Code:

\du

Output:

postgreSQL GRANT 15JPG

The payal user is now a member of Postgres.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL GRANT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL RANK()
  2. CAST in PostgreSQL
  3. PostgreSQL JSON
  4. PostgreSQL Notify
ADVERTISEMENT
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW