Slapdash

Postgres

Postgres.app

  • If installed PG some other way (like homebrew), uninstall: http://daniel.fone.net.nz/blog/2014/12/01/fixing-connection-errors-after-upgrading-postgres/
  • Install from http://postgresapp.com/
  • To get command-line access, add export PATH="/Applications/Postgres.app/Contents/Versions/9.4/bin:$PATH" to .bash_profile
  • gem install pg -- --with-pg-config=/Applications/Postgres.app/Contents/Versions/latest/bin/pg_config
  • Don't need to create any users
  • Just remove username/password from DB config in Rails, then run bin/rake db:create:all and it will create the DBs
  • Connect with any Postgres client using username of your Mac username, no password

Postgres with Homebrew

brew install postgresql@17
brew services start postgresql@17
brew services list
psql

If you can't connect, check the output of brew services list, look at the .plist file, check the ProgramArguments, run that command, and see the error it gives you

createuser shawsome
createdb -Oshawsome shawsome_development
createdb -Oshawsome shawsome_test

ALTER USER "myuser" WITH SUPERUSER; -- to create databases
ALTER USER "myuser" WITH PASSWORD 'mypassword';

(for some reason the double vs single quotes matter)

http://blog.willj.net/2011/05/31/setting-up-postgresql-for-ruby-on-rails-development-on-os-x/

  • \du - list users
  • \l - list databases
  • \connect mydbname - switch to db
  • \dt - list tables
  • \d+ mytablename - describe columns
  • \q - quit
psql -h host -p port -U username -d database
pg_dump dbname > outfile
psql dbname < outfile

Run as postgres user on Ubuntu:

sudo -i -u postgres [psql or pg_dump etc]

SUPERUSER

Seems to not be granted by default

  • Stop service: sudo service postgresql stop
  • Switch to postgres user: sudo su - postgres
  • Run single user mode: /usr/lib/postgresql/9.5/bin/postgres --single -D /etc/postgresql/9.5/main/
  • ALTER USER postgres WITH SUPERUSER;
  • If you want to confirm added, select usesuper from pg_user where usename = CURRENT_USER;
  • Ctrl-D to quit
  • Start service: sudo service postgresql start
  • Run migration that requires SUPERUSER
  • Remove superuser
  • ALTER USER postgres WITH NOSUPERUSER;

Grant User Privileges on Table

GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user;

connect to that db

-- Grant access to existing tables
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;

-- Grant access to existing sequences
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;

-- Allow access to the schema itself
GRANT USAGE, CREATE ON SCHEMA public TO my_user;