Tuesday, February 25, 2014

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

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;

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

Create a database and assign to a role
postgres=# CREATE DATABASE mycms WITH admin;

Delete a database
postgres=# DROP DATABASE mycms;


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

No comments: