How to add a user to PostgreSQL database

How to add a user to PostgreSQL database

Following are the steps to add a user ‘testuser ‘ to the PostgreSQL database ‘testdb’ and grant access to that database;

First you need to make sure that the user is already existing in the server.

# grep testuser /etc/passwd

Else you need to create a user by type the following commands:

# adduser testuser
# passwd testuser
su - postgres
/usr/local/pgsql/bin/psql template1
/usr/local/pgsql/bin/psql -d template1 -U postgres (This command will result into the PostgreSQL command prompt)

Welcome to psql , the PostgreSQL interactive terminal.

template1=#

Now you need to add the user ‘testuser’ and give access to the database named ‘testdb’.

Follow the steps given below:

template1=# CREATE USER tom WITH PASSWORD ‘testPassword’;
template1=# CREATE DATABASE testdb;
template1=# GRANT ALL PRIVILEGES ON DATABASE testdb to testuser;

then quit from the template1 prompt:

Now you need to login as user ‘testuser’;

@ ~]$ su - testuser
Password:
[testuser@ ~]$ /usr/local/pgsql/bin/psql -d testdb -U testuser
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

testdb=>

Tags: ,

Trackback URI | Comments RSS

Leave a Reply

You must be logged in to post a comment.