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>>';"