Thread: Setting up Postgresql on Linux

Setting up Postgresql on Linux

From
Phil Campaigne
Date:
Hello,
I originally installed postgresql as root user and now I am setting up a
development environment with cvs and a java ide and tomcat. I have
everything with the exception of postgresql integreted using a non-root
user.
THe process I am using is  to logon as postges and start the database
and do queries from the command line using psql. Then I logoff and logon
as phil and start tomcat and my java ide.

1.Is there a better way to start the database and the web application?

2. During setup of postgresql I chown and ghgrp the /usr/local/pgsql
directoy tree to postgres?

3.However my development environment(i.e. jdbc connection from my
application) doesn't seem to have permission to access to all the files
it needs in postgres?

4. Is is best to install postgresql as user "phil" in order to gain
access for my integrated develoment environment?

thanks,
Phil


Re: Setting up Postgresql on Linux

From
"Keith Bottner"
Date:
Take a look at your pg_hba.conf file it will be located in your data
directory, on my system (Suse) /var/lib/pgsql/data. That file sets the user
permissions of who is allowed to connect to postgres and to what databases.
In addition you need to make sure that the -i option is used to startup
postgres if you are connecting over TCP/IP. And don't forget you also have
to add the user that you are using to connect through jdbc into the user
list for the database.

Summary
1) pg_hba.conf
2) -i option to enable TCP/IP connections
3) add the jdbc user you will be connecting as to the list of users for the
database.

I hope that helps.

Keith

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Phil Campaigne
Sent: Monday, March 01, 2004 5:22 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Setting up Postgresql on Linux


Hello,
I originally installed postgresql as root user and now I am setting up a
development environment with cvs and a java ide and tomcat. I have
everything with the exception of postgresql integreted using a non-root
user.
THe process I am using is  to logon as postges and start the database
and do queries from the command line using psql. Then I logoff and logon
as phil and start tomcat and my java ide.

1.Is there a better way to start the database and the web application?

2. During setup of postgresql I chown and ghgrp the /usr/local/pgsql
directoy tree to postgres?

3.However my development environment(i.e. jdbc connection from my
application) doesn't seem to have permission to access to all the files
it needs in postgres?

4. Is is best to install postgresql as user "phil" in order to gain
access for my integrated develoment environment?

thanks,
Phil


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly


Re: Setting up Postgresql on Linux

From
Paul Thomas
Date:
On 01/03/2004 23:22 Phil Campaigne wrote:
> Hello,
> I originally installed postgresql as root user and now I am setting up a
> development environment with cvs and a java ide and tomcat. I have
> everything with the exception of postgresql integreted using a non-root
> user.
> THe process I am using is  to logon as postges and start the database
> and do queries from the command line using psql. Then I logoff and logon
> as phil and start tomcat and my java ide.
>
> 1.Is there a better way to start the database and the web application?

Start PostgreSQL off as service (it's probably there already but
configured to start on power-up/reboot). In a production environment I set
tomcat up as a service as well. In a developmemt environment, I
install/run it under my user.

>
> 2. During setup of postgresql I chown and ghgrp the /usr/local/pgsql
> directoy tree to postgres?
>
> 3.However my development environment(i.e. jdbc connection from my
> application) doesn't seem to have permission to access to all the files
> it needs in postgres?

It doesn't need any postgres files. It communicates via tcp/ip. Keith has
already posted the config changes you need to make.

> 4. Is is best to install postgresql as user "phil" in order to gain
> access for my integrated develoment environment?

See above.

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Re: Setting up Postgresql on Linux

From
"scott.marlowe"
Date:
On Mon, 1 Mar 2004, Phil Campaigne wrote:

> Hello,
> I originally installed postgresql as root user and now I am setting up a
> development environment with cvs and a java ide and tomcat. I have
> everything with the exception of postgresql integreted using a non-root
> user.
> THe process I am using is  to logon as postges and start the database
> and do queries from the command line using psql. Then I logoff and logon
> as phil and start tomcat and my java ide.
>
> 1.Is there a better way to start the database and the web application?

Yes, you can edit your /etc/rc.d/rc.local file and add lines something
like this:

su - postgres -c -- 'pg_ctl start -l $PGDATA/pgsql.log'
su - httpd -c -- 'apachectl start'

etc...

> 2. During setup of postgresql I chown and ghgrp the /usr/local/pgsql
> directoy tree to postgres?

Not necessary.  You just need to chown postgres.postgres whatever
directory you're putting in $PGDATA.

> 3.However my development environment(i.e. jdbc connection from my
> application) doesn't seem to have permission to access to all the files
> it needs in postgres?

Someone else mentioned it, it's in pg_hba.conf. Also check your firewall
installation.

> 4. Is is best to install postgresql as user "phil" in order to gain
> access for my integrated develoment environment?

Not needed.  If you createuser as the postgres superuser the user phil and
assign him the power to add users, you make him a superuser and he will
have every bit as much power as the postgres user.  You might want to make
two accounts, one for you as a superuser, and one for the application
which is just a normal user with security to keep it in place.

Enjoy!


Re: Setting up Postgresql on Linux

From
Ron St-Pierre
Date:
Phil Campaigne wrote:

> Hello,
> I originally installed postgresql as root user and now I am setting up
> a development environment with cvs and a java ide and tomcat. I have
> everything with the exception of postgresql integreted using a
> non-root user.
> THe process I am using is  to logon as postges and start the database
> and do queries from the command line using psql. Then I logoff and
> logon as phil and start tomcat and my java ide.
>
> 1.Is there a better way to start the database and the web application?
>
> 2. During setup of postgresql I chown and ghgrp the /usr/local/pgsql
> directoy tree to postgres?
>
> 3.However my development environment(i.e. jdbc connection from my
> application) doesn't seem to have permission to access to all the
> files it needs in postgres?
>
> 4. Is is best to install postgresql as user "phil" in order to gain
> access for my integrated develoment environment?
>
> thanks,
> Phil
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>
For aditional reference you can check out the docs at
http://www.postgresql.org/docs/7.4/static/postmaster-start.html
especially section 16.3 as it pertains to linux (assuming that's your
os). Below are some of the steps I use when I install postgres on a new
machine, check out steps 10 and 11 specifically:

---- my instructions ----
8. Install:

# cd /usr/local/postgresql-7.x
# make install
# make install-all-headers
# cd /usr/local
# chown -R postgres.postgres pgsql


9. Initialize the database as user postgres:

# su - postgres
# initdb -E UNICODE -D /usr/local/pgsql/data


10. Configure (as root) the PostgreSQL SysV Script. This script is
useful for starting, stopping, and checking the status of PostgreSQL.

# cd /usr/local/src/postgresql-7.x
# cp contrib/start-scripts/linux /etc/init.d/postgres
# chmod 755 /etc/init.d/postgres

To have PostgreSQL start automatically when the computer boots add
symbolic links from the correct /etc/rc*.d/ directories to
/etc/init.d/postgres. If the normal runlevel is 3 then you really only
need to add it to rc3.d:

# ln -s /etc/init.d/postgres /etc/rc2.d/S85postgres
# ln -s /etc/init.d/postgres /etc/rc3.d/S85postgres

etc ...

11. Start PostgreSQL for the first time:

# /etc/init.d/postgres start


12. Create a new db:

# su - postgres
$ createdb testdb
$ psql testdb

----\ my instructions ----
Ron

*Note:* these are taken from the book Practical Postgres by Worsley and
Drake and are slightly(? can't remember, it's been so long since I made
the list) modified.