How to create user and give access to database in postgresql

PostgreSQL is a powerful and popular open-source relational database management system that provides numerous features and options for managing databases. If you’re new to PostgreSQL, one of the first things you’ll need to do is create a user and give them access to your database. In this article, we’ll go over the steps for creating a user and granting them access to a PostgreSQL database.

Step 1: Connect to PostgreSQL

Before you can create a user and grant them access to a database, you need to connect to your PostgreSQL server. You can do this using the psql command-line tool. Open a terminal or command prompt and enter the following command

$ sudo -u postgres 
$ psql -U postgres

This command will connect you to the default PostgreSQL database with the superuser account named “postgres.”

Step 2: Create a user

To create a new user, use the CREATE USER command followed by the username and password. Here is an example:

CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

Replace "myuser" and "mypassword" with the username and password you want to use for your new user. The semicolon at the end of the line indicates the end of the command.

Step 3: Create a database

If you haven’t already created a database, you can use the CREATE DATABASE command to create one. Here is an example:

CREATE DATABASE mydatabase;

Replace "mydatabase" with the name you want to use for your new database.

Step 4: Grant access to the database

To give your new user access to the database, you need to grant them permission. Use the GRANT command followed by the privileges you want to give and the name of the user and database. Here is an example:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

This command grants all privileges to the user "myuser" on the database "mydatabase." You can replace "ALL PRIVILEGES" with specific privileges such as SELECT, INSERT, UPDATE, DELETE, etc. depending on your requirements.

Step 5: Verify access

To verify that your user has access to the database, you can try connecting to the database using the new user’s credentials. You can do this using the following command:

$ psql -U myuser -d mydatabase

This command will connect you to the "mydatabase" database using the "myuser" user account.

Conclusion

Creating a user and giving them access to a PostgreSQL database is a simple process that requires just a few commands. With the steps outlined in this article, you should be able to create a new user, create a new database, and grant your new user access to the database. By doing so, you can better manage your PostgreSQL databases and ensure that your users have the necessary permissions to perform their tasks.

Bonus

if you have an error like Insufficient privilege: 7 ERROR: permission denied for schema public or something like that, then you need to do this :

$ sudo -u postgres psql

postgres=> \c mydb postgres
mydb=# GRANT ALL ON SCHEMA public TO myuser;
GRANT

Create Superuser in Postgresql from command line

$ sudo -u postgres createuser -s -i -d -r -l -w <<username>>
$ sudo -u postgres psql -c "ALTER ROLE <<username>> WITH PASSWORD '<<password>>';"