MySQL and/or PostgreSQL databases for new users?

[Andrew Hammond]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> 2. How do I create a database and a user for each new user on the
> system, preferably in both MySQL and PostgreSQL?

For PostgreSQL, assuming you're root,

1) You need to prepare the root account to do this. I'm assuming you're
using the standard "postgres" account for the postgres master.

cat > /root/.pgpass <<EOF
#host:port:dbname:dbuser:dbpass
*:*:*:postgres:$POSTGRES_PASSWORD
EOF
chmod 400 /root/.pgpass

2) For each new account...

# create a postgres account for the new user
echo $PASSWORD | createuser -U postgres -A -D -P -E $USER

# create a .pgpass file in the new user's dir for this database
cat > /home/$USER/.pgpass <<EOF
# host:port:dbname:dbuser:dbpass
*:*:*:$USER:$PASSWORD
EOF
chmod 400 /home/$USER/.pgpass
chown $USER /home/$USER/.pgpass

# create a database for the new user, name it after user
createdb -U postgres -O $USER $USER

3) You may want to do some things to template1, the default template for
new databases. Think of it as the /etc/skel of databases. I'd suggest at
least adding the plpgsql procedural language.

createlang -U postgres plpgsql template1

I haven't used the RHEL distro postgres (we do custom compiles here),
but there are plenty more pl's which range from the ugly (plperl /
pltcl) to the esoteric (plR). You might even want to go so far as to
create a very simple example database. Contact me if you're interested
in doing this and I'll set you up with a good example.

- --
Andrew Hammond    416-673-4138    ahammond-swQf4SbcV9C7WVzo/KQ3Mw at public.gmane.org
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFCi1sRgfzn5SevSpoRAgw9AJ9sVTPHjd4GgOrvyyqA9z9Rmzax6gCfZ3S4
GyLYkdHAQVCF/eRte8cDxIw=
=ZLVi
-----END PGP SIGNATURE-----
--
The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml