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