Setting up PostgreSQL on Solaris

PostgreSQL is bundled with Solaris 10 and is available from the primary OpenSolaris IPS repository.

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

Install Required Packages

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

Check if PostgreSQL SMF Services are Configured

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

On Solaris, PostgreSQL is managed by the SMF framework. If you're curious you can check the service manifest at /var/svc/manifest/application/database/postgresql.xml and the service methods at /lib/svc/method/postgresql. Many important parameters are stored in the service configuration file (postgresql.xml): if you want to change some parameters (such as PostgreSQL data directory) you must use svccfg to edit the service configuration.

PostgreSQL and RBAC

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.