How to Setup PostgreSQL database on Solaris 10

By default PostgreSQL comes with solaris 10. To check if PostgreSQL is installed in your Solaris instance you can use the following command:

$ svcs "*postgres*"
STATE STIME FMRI
disabled Feb_16 svc:/application/database/postgresql:version_81
disabled 16:11:25 svc:/application/database/postgresql:version_82

If you don't see any PosgreSQL instance in your Solaris box then proceed and install the following packages (the list may actually change over time):
* SUNWpostgr
* SUNWpostgr-contrib
* SUNWpostgr-devel
* SUNWpostgr-docs
* SUNWpostgr-jdbc
* SUNWpostgr-libs
* SUNWpostgr-pl
* SUNWpostgr-server
* SUNWpostgr-server-data
* SUNWpostgr-tcl


After installation, SMF services should be listed by (the output may depend on the actual PostgreSQL version you installed):

$ svcs "*postgres*"
STATE STIME FMRI
disabled Feb_16 svc:/application/database/postgresql:version_81
disabled 16:11:25 svc:/application/database/postgresql:version_82




PostgreSQL on Solaris uses RBAC to give users permissions over the database instance. When you install Solaris' PostgreSQL packages an RBAC role is setup for you:


[/etc/passwd]
postgres:x:90:90:PostgreSQL Reserved UID:/:/usr/bin/pfksh

This user is setup as an RBAC role in /etc/user_attr file:

[/etc/user_attr]
postgres::::type=role;profiles=Postgres Administration,All

Permission for the Postgres Administration profiles are setup in the /etc/security/exec_attr file:

[/etc/security/exec_attr]
Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/initdb:uid=postgres
Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/ipcclean:uid=postgres
Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/pg_controldata:uid=postgres
Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/pg_ctl:uid=postgres
Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/pg_resetxlog:uid=postgres
Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/postgres:uid=postgres
Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/postmaster:uid=postgres

Starting PostgreSQL
You can start PostgreSQL using the following SMF command from an account with the appropriate privileges:

$ su - postgres
$ svcadm enable svc:/application/database/postgresql:version_82

Initial Configuration
By default, PostgreSQL is configured to trust all of the local users. That's not a good practice because all your local users may connect to PostgreSQL as a superuser. The first to do is setting up a password for the postgres user:

$ psql -U postgres
postgres=# alter user postgres with password 'your-password';

Exit psql with the \q command and change the /var/postgres/8.2/data/pg_hba.conf file to set an appropriate authentication method and change the following line:

[/var/postgres/8.2/data/pg_hba.conf]
local all all trust

with, for example:

[/var/postgres/8.2/data/pg_hba.conf]
local all all md5

Next time you connect, PostgreSQL will be asking you for the user password. Now, let's refresh the PostgreSQL service so that PostgreSQL will receive a SIGHUP signal an re-read the pg_hba.conf file:

$ svcadm refresh svc:/application/database/postgresql:version_82

Done!
You're now running a PostgreSQL instance on your Solaris box ready to be given to your database administrator, ready for production use.