[HOW TO] PostgresSQL add/create/delete user account and grant permission

Hello,

In the afternoon I have configured the Postgres database for one developer. After finished, I think I should write a little article about Postgres installtion.

Let's start!

1. Install the postgres
 t13n@ho-pc-pttien:~$ sudo apt-get install postgresql postgresql-contrib

With that, postgres will be installed on your PC

2. Check the authentication way
 t13n@ho-pc-pttien:~$ sudo vi /etc/postgresql/9.1/main/pg_hba.conf

This is  PostgreSQL Client Authentication Configuration File.

METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.

Come to line below and change from peer to trust
# Database administrative login by Unix domain socket
local   all             postgres                                trust

Restart the Postgres
 t13n@ho-pc-pttien:~$ sudo /etc/init.d/postgresql restart

When you do it, you allow postgres user can login without password. It's risk :) but you can improve laster. I think the first you must understand it before attack it.


 3. Login with postgres user
 t13n@ho-pc-pttien:~$psql -U postgres

4. Create a new user
postgres=# CREATE USER thminh_1 with password '0********';
CREATE ROLE

5. Update the password for user
postgres=# ALTER USER postgres with password '0123456789';
ALTER ROLE

6. Delete the user
postgres=# DROP USER thminh_1

7. Set the user to be a superuser
postgres=# ALTER USER thminh_1 WITH SUPERUSER;
ALTER ROLE

8. Connect to database server
Type the following command

$psql temp01

or

$psql -d temp01 -U postgres

9. Create database
temp01#CREATE DATABASE hello;

10. Grant the privileges on database
temp01#GRANT ALL PRIVILEGES ON DATABASE hello to your_user

11. \q to quit
temp01#\q

12. Test your_user login

$su - your_user
$psql -d hello -U your_user