MySQL and Postgres user management

Creating a User / Role

Mysql: mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'%' IDENTIFIED BY 'dbpass';
(The above grants full access to the named database to the named user connecting from any host on the internet identifying using the given password. If granting privileges this way, it’s important to lock down access using iptables on the host.)

Postgresql: linux# createuser -U postgres -s -P dbuser
(The above creates a superuser which can authenticate using a password for which you will be prompted.) In addition to the above, it’s important to grant access to the client host by creating a line similar to this in the pg_hba.conf file:

host    all         all         0.0.0.0/0     password

(The above allows any host on the internet to authenticate as any user using an encrypted password. When granting access this way, it’s important to lock down access to the postgresql server using both iptables and ip6tables.)

Finding an existing User / Pass

• If it’s a rails app, check APP_ROOT/config/database.yml (Thanks Rycar)
• You could also SELECT * from pg_user & guess which user is being used. (Thanks Brian)

List users

psql
\du

Change database owner

psql
CREATE USER blog WITH PASSWORD 'N8TdKrC7';
ALTER DATABASE foo OWNER TO blob;
\du
\dt

Change owner of tables, sequences, and views inside a database

See https://support.bluebox.net/tickets/B18160#post-2533121

Changing a User / Role’s password

Mysql: mysql> UPDATE mysql.user SET password = PASSWORD('dbpass') WHERE user='dbuser';

Postgreslq: postgres=# ALTER ROLE dbuser UNENCRYPTED PASSWORD 'dbpass';

Restoring from backups

pg_basebackup If a basebackup was taken (and unless the customer is migrating from another host, that’s what it should be), it’s as simple as

1. stop postgres
2. move the current contents of the data directory out of the way
3. untar the backup to the data directory, e.g. untar -jxvf base.tar.bz2 -C /var/lib/pgsql/9.2/data
4. start postgresql

Heroku/binary dumps use pg_restore Plain text dumps

If pg_dump was used to create a plain text dump, pg_restore will not work; psql must be used instead.

First extract the dump file (if necessary) and chown it to the postgres user. Switch to postgres and execute:

psql dbname < uncompressed_dump_file

Skipping authentication (“single user mode” for resetting passwords)

Put this line and only this line in pg_hba.conf:

local   all             postgres                                 ident

restart postgres.

Reset the postgres password

psql
ALTER USER postgres WITH ENCRYPTED PASSWORD 'newpassword';

In Postgres 8.4, just run:

sudo passwd postgres

Alternately: sudo su – postgres (and you won’t be prompted for a password)