Managing PostgreSQL with pgsql and pgAdmin3

The command line interface (CLI) tool that comes with Postgres server is known as pgsql. Alternatively, pgAdmin3 and phpPgAdmin can be installed.

The pgsql

Among some of the commands for pgsql can be shown as below;

Example of common commands

Login to the Postgres prompt

$ psql -U postgres -h 127.0.0.1

Create a role to create database

postgres=# CREATE ROLE dbadmin CREATEDB;

Create a role as administrator

postgres=# CREATE ROLE admin SUPERUSER;

Create a role to create group roles

postgres=# CREATE ROLE dbadmin CREATEROLE;

Add a user to any of above role groups

postgres=# GRANT dbadmin to userone;

Assign role to a database and allow to login

GRANT all privileges on database dbname TO dbadmin;

ALTER ROLE "dbadmin" WITH LOGIN; 

Remove a user from a role group

postgres=# REVOKE dbadmin from userone;

A user can change their roles if they are assigned to several roles. 

postgres=# SET ROLE admin;

A user can restore to default role group 

postgres=# RESET ROLE;

Remove a role

postgres=# DROP ROLE admin;

List available users and roles

postgres=# \du

                               List of roles

   Role name   |                   Attributes                   | Member of 

---------------+------------------------------------------------+-----------

 admin         | Create DB, Cannot login                        | {}

 dbmsuser      | Superuser                                      | {admin}

 dbmsuserlogin | Superuser, Cannot login                        | {}

 postgres      | Superuser, Create role, Create DB, Replication | {}

Change privileges of a role
postgres=# ALTER ROLE admin WITH LOGIN CREATEDB;

Create a database and assign to a role

postgres=# CREATE DATABASE mycms WITH admin;

Delete a database

postgres=# DROP DATABASE mycms;

Done

The Window pgAdmin3

Try out SQL statements within these tools. I found one nice reference at http://www.itl.nist.gov/div897/ctg/dm/sql_examples.htm