Create Database and user in Postgres and Mysql.

Since I worked in SOA area, haven’t touched the database for couple years, although in some projects, I need to test against various database (Mysql, Postgres, Oracle etc), but it is very basic stuff, like create database, users etc.

This post basically is a memo for me, I need to work with various DBs from time to time, and think it is best to record it in my blog, instead of looking into the manual again. ;-). Also, you could see this is a follow-up post for my previous postgres installation blog.

1. Postgres

1) connect Postgres

psql -h localhost -U postgres

2) add user

create user jeff with password ‘jeff’

3) create database

create database jeffdb

4) grant db to user

grant all privileges on database jeffdb to jeff

Log out with “\q” command

Log in jeffdb through user jeff:

psql -d jeffdb -U jeff

2. Mysql

1) connect mysql

mysql -u root -p ‘urpassword’

2) create database

create database jeffdb

3) allow user jeff to connect to the server from localhost using the password jeff

grant usage on *.* to jeff@localhost identified by ‘jeff’;

4)grant all privileges on the jeffdb database to this user

grant all privileges on jeffdb.* to jeff@localhost ;

Log out and log in through jeff user.

mysql -u jeff -p’jeff’ jeffdb

[Reference]
1. How to add postgres user and create db
2. Create mysql database and set privileges into a user

Postgresql installation/connection in Fedora

Because I wan to try the JON 2.0, I gotta have a database, currently the JON supports two databases, Postgres and Oracle. So here I choose the Postgres as it is open source.

I am using the Fedora8, so install the postgres just simply need to run

yum install postgresql-server

After I installed the postgres, I created it with following command:

service postgresql initdb
service postgresql start

And then I try to (as reference[1] described)

createuser -h 127.0.0.1 -p 5432 -U postgres -S -D -R rhqadmin

it tells me that ” FATAL: Ident authentication failed for user “postgres”. This error mostly means you need to update the “pg_hba.conf” file.

try to run

ps aux | grep postmaster

to find out where the “pg_hba.conf” located. (Here thanks to illya77 and pilhuhn in #rhq at irc.freenode.net help me solve this problem).

Update the pg_hba.conf suggested, and then

Edit the postgres host based access configuration file (pg_hba.conf), which typically would be at: /var/lib/pgsql/data/pg_hba.conf

Modify the local line to use “trust” based authentication rather than “identity”. Please review the PostgreSQL documentation before making this change and take the security

local all  all   trust

After that, restart the postgresql to make the change taking effect.

service postgresql restart

At last: run

psql -h localhost -U postgres

to see if it can log in.

[Reference]
1. Postgres Quick Start Installation