How to Create a New User With Full Privileges in PostgreSQL?

PostgreSQL provides a way to give a user full privileges to do anything with the database. The database objects like schema, table, function, and so on. The ‘GRANT’ command is used in PostgreSQL to provide a user with any specific privileges or to override the role of the user. In this article, we are going to learn how we can create a new user assign that user with full privileges, and so on. So, be prepared with a PostgreSQL terminal because it is far better to run Postgresql in the terminal as it provides more power to the user.

We are going to learn how to create a user with full privileges not a user to superuser. You can check out our article How to Change a User to Superuser in PostgreSQL. Changing a user to a superuser and providing user privileges to modify content in the specific table are whole different concepts.

Creating a New User with Full Privileges in PostgreSQL

Initially, we are going to create a ROLE with LOGIN and then we will connect with our database using that USER that we have created. And, then providing it with all the privileges to create tables, insert values, and delete records. For that purpose, we have to provide our created user with full privileges. So, without any further delay let’s dive into the tutorial.

Step 1: Create a ROLE

We are creating a role with the name “pirate” with LOGIN privilege.

Command:

CREATE ROLE pirate LOGIN PASSWORD '12345';

Output:

Role Created

Step 2: Create a Table

The basic idea of creating a role is to provide it with full privileges so that the role or the user can create, delete, and update records. That’s why we are going to create a table.

Query:

CREATE TABLE test (id INT PRIMARY KEY, name VARCHAR(13));

Output:

Table created

Step 3: Logging as a New User

Now, we will log in as a new user, the user we created earlier remember? “pirate” We will log in using this user and then try to insert values in the created table. But, as our user is new and doesn’t have any privileges yet, Postgres will deny our user to insert values in the table.

Now, let’s see what will happen and why.

Command:

psql -h localhost -U <username> <database_name>

Output:

Logging as a new user

Step 4: Insert Values in Table

Now as we successfully logged in as the new user with which we created earlier, and now we will try to insert values in the table. For your information, our user “pirate” doesn’t have any specific privileges and isn’t a superuser so that means it won’t be able to insert values in the table.

For a user to insert values to modify anything in the database, it must have superuser and other privileges. Now let’s move on with the demonstration and let’s see what happens.

Command:

INSERT INTO test (id,name) VALUES (1,'ujjwal');

Output:

Permission denied

As we can see in the result, permission is denied by Postgres to insert values in the database. So, to overcome this problem we have to provide our user “pirate” with privileges so that it will be able to insert values and modify content in the table in a particular database.

Step 5: Provide Privileges to the User

Now, we have to provide our user “pirate” with privileges so that we can add values and update records in the table in a specific database. So, assigning privileges to the user is pretty simple. Now let’s take a look at how we can achieve this.

But, remember we are providing privileges to “pirate” but to only work with table “test”. It means our user “pirate” can modify and update the contents of table “test” only. That’s an important point to remember.

NOTE: To provide privileges to “pirate” we have to log in as a superuser or as “Postgres” that we usually log in with. And, then we use this command to provide privileges to our new user.

Command:

GRANT ALL ON test TO pirate;

Output:

Privileges granted

Step 6: Insert Data

Now we have provided all the privileges to the user “pirate” It is now able to modify and update the contents of table “test”. Before our user wasn’t able to insert data but now with all the provided permissions it will be able to insert, delete, and modify data.

Now let’s see the demonstration of this step:

INSERT INTO test (id, name) VALUES (1,'ujjwaL');

Output:

value insertion

We have successfully created a new user and learned how to provide the newly created user privileges to insert, delete, and modify data in the specific table. Now let’s see if can we perform other operations like fetch data or not.

Step 7: Fetch Data

We have successfully inserted values in our table “test” with our new user “pirate”. Now let’s try to fetch some data with our current user and see if it can fetch data or not.

Our user is not a superuser which is why it wasn’t able to provide itself privileges, and thus we have to log in as a superuser and then provide privileges to “pirate”.

Now we are going to fetch data as user “pirate”

Command:

SELECT * FROM test;

Output:

Fetched successfully

Our user is now capable of performing all kinds of operations in the table. You have learned many things in this article from creating a new user, providing it privileges, and how to log in as a new user all these steps are necessary in the journey of learning databases, especially the Relational Database Management System.

Conclusion

PostgreSQL is becoming very popular among developers, and learning PostgreSQL will add a new powerful skill to your career which will in the end provide you with the best results. Thus, learning PostgreSQL is crucial and useful to any developers whether they are experienced new upcoming developers or just freshers.

PostgreSQL provides many ways to handle different users and limit their privileges so that a normal user will only be able to fetch data but can not modify the contents of the database, whereas another user can modify the contents and fetch data too. It depends on the superuser to decide what privileges to which user.