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.
