In PostgreSQL, a user must have the LOGIN privilege to log into the database. However, explicit permissions must be assigned to perform database operations, including selecting tables, columns, and records.
The PostgreSQL GRANT query allows you to modify privileges for users on database objects such as tables, columns, views, functions, schemas, and more.
This tutorial will walk you through using the PostgreSQL GRANT command to modify privileges for various database objects.
The basic syntax for the GRANT command is:
In the above syntax, you can specify one or more privileges to grant on the specified object to the user.
To set all the privileges on an object, you can use the ALL keyword.
Some of the privileges you can specify to grant on an Object include:
Next, you specify the database object and the user which to grant the set privileges.
Grant Statement Examples
Let us look at a few examples of how to use the GRANT query on the PostgreSQL database.
First, create a local user with no privileges. To do this, ensure you log in as the admin user.
In the next step, create a sample database and table that we will use to illustrate PostgreSQL privileges.
A sample query is below:
Finally, exit the Postgres user and log in as the user we just created above. In the above example, use the command:
If you try to select values from the modify table of the privs database, you will get a permission denied error.
You can solve this error by granting all permissions to the mods user or by adding select privileges.
To do this, log in as the Postgres user.
This allows the mods users to select items from the modify table of the privs database.
Once you run the select statement, you should get results as:
id | username | email | amount
You can also assign multiple privileges at once. For example, to specify SELECT, INSERT, DELETE, and TRUNCATE privileges, use the command.
To grant all permissions to a user, set the privileges to ALL, as shown in the example command below:
In all the examples above, we only assign privileges to a user on a specific table. To grant the privileges to all tables of a specified schema, we can use the command:
You can also GRANT a connect privilege to a specific user to a specific database. The query is:
To revoke all the permissions granted to a user, use the REVOKE query as:
As this guide has shown you, PostgreSQL allows you to modify privileges to a user on database objects using the GRANT and REVOKE commands.