Richard's Diary

Wednesday, November 19, 2008

PostgreSQL
  1. postgres user is already created on linux (because postgres was already installed) so to change default password do "passwd postgres"
    1. had postgres not been installed, on fedora "yum install postgresql-server"
  2. to install database cluster and start server, starting as root user
    mkdir /usr/local/pgsql/data
    chown postgres /usr/local/pgsql/data
    su postgres
    initdb -D /usr/local/pgsql/data
    cd /usr/local/pgsql/data
    postgres -D /usr/local/pgsql/data >logfile 2>&1 &
    from instructions /usr/share/doc/packages/postgresql/html/install-short.html
  3. set postgres database user password per http://sqlrelay.sourceforge.net/sqlrelay/gettingstarted/postgresql.html because the default is nil and you can't log in as that. If successful, it will print ALTER ROLE
    psql -c "ALTER USER postgres WITH PASSWORD 'newpassword'" -d template1
  4. modify pg_hba.conf to require password for each connection. change from trust to password. not only is it a security issue, it won't drive you crazy when developing because you'll be forced to specify which database you want to connect to with what user/permissions
    /usr/share/doc/packages/postgresql/html/auth-pg-hba-conf.html
  5. pg_ctl -D . reload
    #to reload the pg conf files for the current database directory we're in so that step 4 takes effect immediately
  6. CREATE ROLE username PASSWORD 'string' LOGIN CREATEDB CREATEROLE; according to /usr/share/doc/packages/postgresql/html/role-attributes.html
  7. psql -U username -d template1 to test newly created login and to issue
    CREATE ROLE pickyricky PASSWORD 'string' LOGIN CREATEDB;
  8. create databases using rails if 2.0. Otherwise,
    psql -U pickyricky -d template1
    CREATE DATABASE pickyricky_production;
    CREATE DATABASE pickyricky_test;
    CREATE DATABASE pickyricky_development;
  9. start restricting users to databases in pg_hba.conf
How to start,stop,restart server
http://www.postgresql.org/docs/8.3/static/app-pg-ctl.html

© 2010 Picky Ricky, Inc. originalblog