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:
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:
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:
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:
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:
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:
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:
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.
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.