After successfully installing the PostgreSQL database, the next step is to manage users’ permissions for the stored databases, which involves creating new users and granting them access to specific databases while restricting their access to others.
In this post, we will discuss how to enter the Postgres shell and create a new user account with password protection.
Connecting to PostgreSQL Server
To enter the Postgres shell, we need to first move from the standard user account to the Postgres account. We can do this by running the following command:
$ sudo -i -u postgres
To determine which user we are working with, use the whoami command, and we may see output similar to the following:
$ whoami
After checking the username using the whoami command, we can run the psql command to get access to the PostgreSQL server. Alternatively, we can use the command “psql -U postgres -W” to enter the Postgres shell.
$ psql -U postgres -W OR psql -V
Once inside, we will see a prompt that looks as follows:
Create a Postgres User
Now that we have entered the postgres shell, we can create a new user account called geeksveda by executing the following command:
CREATE USER geeksveda WITH PASSWORD 'geeks123';
The above command creates a new user account named geeksveda with the password ‘geeks123‘.
Next, we will create a database and grant geeksveda access to it.
Grant Permissions to Postgres User
To grant privileges to the user, we can use the GRANT command followed by the privileges we want to grant and the user we want to grant them to.
Create PostgreSQL Database
First, let’s create a database called geeksveda_db with the following command:
CREATE DATABASE geeksveda_db;
After creating a database named geeksveda_db, we can grant access to the user geeksveda by executing the following command:
GRANT ALL PRIVILEGES ON DATABASE geeksveda_db TO geeksveda;
This command grants geeksveda all permissions in the database, including SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, and USAGE.
If we want to grant only one permission, such as SELECT, we can use the command:
GRANT SELECT ON DATABASE geeksveda_db TO geeksveda;
In PostgreSQL, privileges are used to determine which users or roles have access to specific objects such as tables, views, sequences, functions, and databases.
Various types of privileges can be granted to users or roles in PostgreSQL, including:
- SELECT – allows the user to read data from a table or view.
- INSERT – allows the user to insert new rows into a table.
- UPDATE – allows the user to modify existing rows in a table.
- DELETE – allows the user to delete rows from a table.
- TRUNCATE – allows the user to delete all rows from a table.
- REFERENCES – allows the user to create a foreign key constraint that references a column in another table.
- TRIGGER – allows the user to create triggers on a table.
- CREATE – allows the user to create new objects, such as tables, views, or functions.
- CONNECT – allows the user to connect to a database.
- TEMPORARY – allows the user to create temporary objects that are automatically dropped at the end of a session.
- EXECUTE – allows the user to execute functions and stored procedures.
- USAGE – allows the user to use a schema or a sequence.
It is important to be careful when granting privileges, as giving too many privileges to a user or role can lead to security issues. It is recommended to only grant the minimum necessary privileges for a user to perform their required tasks.
Update User Password in PostgreSQL
To update a user in PostgreSQL, you can use the ALTER USER command as shown.
ALTER USER geeksveda WITH PASSWORD 'new_password';
This will update the password for the user with the specified username (geeksveda).
Update Username in PostgreSQL
To change a user’s name in PostgreSQL, you can use the ALTER USER with RENAME TO statement. Here’s an example of how to change a user’s name from ‘oldname‘ to ‘newname‘:
ALTER USER geeksveda RENAME TO geeksveda1;
Delete User in PostgreSQL
To delete a user in PostgreSQL, you can use the DROP USER command as shown.
DROP USER geeksveda1;
This will delete the user with the specified username (geeksveda1) from the database.
Note that this action is permanent and cannot be undone, so use it with caution.
Overall, these commands are simple and straightforward to use. However, it’s essential to take care when performing any user management tasks, as incorrect changes or deletions can have a significant impact on your database and applications.
Use Comments in SQL Statements in PostgreSQL
We use comments to put notes or explanations about the large complex queries or functions that we have created so that we do not forget the reason for writing the SQL commands that we put in that specific form.
Therefore, if we want to review or modify them after a while, that will be very easy because we will read the comments until we understand how to install commands quickly and then modify what we want.
Putting comments does not affect the orders placed in the query, and an unlimited number of comments can be placed.
- Comments, in general, can be placed in most types of databases, but not in all of them. For example, Access databases do not allow comments.
- Another note is that comments are not placed in the same way in all types of databases. For example, there are three options for placing comments in MySQL databases, while in PostgreSQL databases there are only two options.
Add Comments in PostgreSQL
There are two ways to add comments in PostgreSQL:
Single-line comments, which start with two consecutive dashes (--). Everything that follows on that line will be considered a comment and ignored by the system.
For example:
CREATE DATABASE geeksveda_db; -- This query creates a database with name geeksveda_db
Multi-line comments can be used for longer comments that span across several lines. They start with the /* symbol and end with the */ symbol.
For example:
/* Create a user with the name geeksveda create a database with name geeksveda_db Assigning permissions to the user geeksveda */
Comments can be added to most objects in PostgreSQL, including tables, columns, views, functions, and triggers.
Conclusion
In this post, we discussed how to create a new user, assign permissions to the user, and use comments to add notes and explanations in PostgreSQL databases. By following these steps, you can easily manage users’ permissions and optimize your PostgreSQL database for better performance.
