PostgreSQL

Postgres GRANT ALL PRIVILEGES ON SCHEMA to User

GRANT implies allowing some access of Postgresql to the user. The process of allowing the user to interact with the objects of the database to apply operations on the tables etc., lies under the GRANT mechanism. The “GRANT” command has two variants.

  • Grant privileges on the objects in the database like the commands (select, insert, delete, etc.), function, procedure, and schema as well.
  • Grant on the roles; this feature is used to create a new user and then grant the role membership to the new user.

The keyword “privilege” is an optional word in Postgresql. Whereas for other databases, it is essential. The membership of roles is not allowed to the public, as we do it in the case of privileges. Postgresql allows the owner to revoke all the privileges created on its own. In this instance, the owner can make the whole database read-only by revoking the commands like insert, update and delete.

Open psql shell after successful installation of the Postgresql database system in your computer. Check the user that you have already created in your system. Which is “Postgres”, which is created by default whenever you install and configure the Postgresql in your systems.

Some roles are already given to the user at the start. But if you want to provide all privileges to the user, then you can apply all in one command or in separate commands to elaborate the condition and working of the commands.

Example 1

To connect the database with the user, you are already working on, use the command given below:

>> GRANT CONNECT ON DATABASE Postgres to Postgres;

Using this command, the user will be connected with the database and have all the rights to work on it.

Example 2

After connection with the database, the user wants to have a command on all the schemas of the database. Schema is of two types, one is user_created, and the other one is system_created schema. By applying the query, the privileges are shifted towards both the schemas. The system-defined schemas are listed in the option of the catalog inside the database. Whereas the schemas that the user creates are mentioned in the “schemas” portion of the database description. If you want to provide privileges on only a single schema, you will mention the schema’s name in the command.

>> GRANT USAGE ON SCHEMA public TO Postgres;

Now the user can access that particular schema.

Example 3

Now, if you want all the commands applied on the table to be accessed by the user, then mention each in the “GRANT” command. You can also use a separate query for each command. These commands will be applied to the table in the specified schema. Each schema is accessed separately, one at a time.

Example 4

Similar to all the “data fetching” commands, we can also apply privileges on all the relations in the schema.

After providing all the privileges to a user, you can check the relations. It can be done by fetching schema, table name, and the user’s privilege from the schema.

A select command will be used to select the schema, table name, and the privileges applied for the Postgres user.

The table column includes all the names of the tables in the schema. Whereas the privileges like “insert” and “select” are the commands, we have allowed to the user in the previous query.

Example 5

The sequence is an important feature in any database created in Postgresql. Each sequence for each schema is different. For public schema to be accessed by the user, we will use the command to access the sequences.

Example 6

Earlier in the article, we have created the connection of the user with the database. As there are many features and services in the database, applying the privileges one by one to the user may take a lot of time. So we decided to grant privileges to the whole database collectively.

The Postgres database will be now accessed by the user “Postgres”.

Example 7

Till now, all the privileges were granted to the already created relations. But for the new ones, we will create a table named “sample1”

>> create table sample1(d integer, name varchar(20));

Now, we will alter the user’s privileges to add this table too in the schema of the database.

First, you alter the privileges that already exist and then mention the user. And at the last use the grant command to show the statements to be applied to the user.

Example 8

Here we will use the “GRANT ON ROLES” command. To apply the privilege of creating the database, we will apply this role to the user.

>> ALTER USER Postgres CREATEDB;

Example 9

User is made as a superuser, and similarly, the roles are removed from being super.

>> ALTER USER Postgres with SUPERUSER;

Example 10

To remove all the privileges we have provided, use the “REVOKE” keyword for this purpose.

>> REVOKE ALL ON SAMPLE FROM Postgres;

Example 11

Besides working with the already existing roles, we will create a new user to create a new role.

>> create role user1 login password ‘ubuntu123’;

Now for this user, create a new table.

>> create table new (id int, name varchar(10));

Now use the “select” command to see the columns created in the table. This command will not execute and will display an error. Because the user is created now and doesn’t have any access to the database.

ERROR:  permission Cancelled. You cannot alter the table.

Apply the privileges to the user.

>> grant select on new to user1;

If we check the list of roles, you will see two roles, but user1 has not mentioned any member. As it is understood that ‘Postgres is a member of the Postgres database.

>> \du

If we apply the command to fetch the table name, schema, and privileges on user1, you will see that a single table is mentioned with the only “select” statement. As we have granted only “select” for this. The schema for each table is public. Because the user forms all these tables, so these relations are always stored in the public schema.

 

Now apply privileges to all the commands on all the tables.

All the relevant statements of the table are applied to the user.

When we again apply that command to user1, we will see different results. This is how the “GRANT” command works.

Again check the list of roles; you can see how the “user1” is mentioned as a member of Postgresql.

>> \du

Conclusion

“Postgres GRANT ALL PRIVILEGES ON SCEMA to the user” deals with providing access to the newly created or already existing users. New users are provided with the roles where those who already have roles, just allowed the privileges of using “select, insert, update, etc., commands. Similar to the grant command, we can also remove the rights by using a REVOKE command. With the help of this guide, you will be able to give the users the right to modifications in the database.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.