Best PostgreSQL Install Practices

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Alfred
    #1

    Best PostgreSQL Install Practices

    The PHP web app that I'm building uses PostgreSQL. Of course it won't
    work right off the bat, so my web app is going to have to detect that
    it has never been configured with a database and have to instruct the
    sysop on what to do. What are developers in the PHP industry doing with
    their installers to ensure everything with PostgreSQL is going to work
    out right?

    In particular I'm going to want the web app to have a local database
    (by default), although a remote one is an option. The web app will
    require a username and password to reach the database. What pg_hba.conf
    configuration would you recommend most highly for new installations?

    Tags: None
    • Erwin Moller
      #2
      Re: Best PostgreSQL Install Practices

      Alfred wrote:
      The PHP web app that I'm building uses PostgreSQL. Of course it won't
      work right off the bat, so my web app is going to have to detect that
      it has never been configured with a database and have to instruct the
      sysop on what to do. What are developers in the PHP industry doing with
      their installers to ensure everything with PostgreSQL is going to work
      out right?
      Just describe clearly what users on the database are made and what their
      rights should be.
      Maybe add some tutorial with commands for GRANT or something.
      Then let the guy that installs the database read that document.

      Also add a SQL-script that creates the tables/relations/etc.
      Do not make a binary dump, but the text-SQL variant, so people can actually
      read what tables are created.

      You do not want to make a seperate installer for Postgresql, are you?

      >
      In particular I'm going to want the web app to have a local database
      (by default), although a remote one is an option. The web app will
      require a username and password to reach the database. What pg_hba.conf
      configuration would you recommend most highly for new installations?
      Well, the pg_hba.conf is a file that should be maintained by the sysop.
      So just mention WHICH users should be added with what rights.
      Maybe suggest the lines to add to pg_hba.conf.

      For security, do not allow others on the same machine (trust-setting) to
      connect to your database. The 'fallthrough' approach is used in
      postgresql's pg_hba.conf, so you must explicity say that other cannot
      connect if you have a few users in the environment.
      To make this sure, add a 'reject' as last line for your listed allowed
      connections to your database.

      Good luck, and wise choice (Postgres). :-)

      Regards,
      Erwin Moller

        Comment

        • Alfred
          #3
          Re: Best PostgreSQL Install Practices

          Erwin Moller, thanks for your opinion. That's pretty much how I was
          going to do it:

          1. Provide DB's CREATEDB and GRANT statements for the user account used
          by the web app.

          2. Suggest the pg_hba.conf changes for the sysop, and try to prohibit
          anything connecting to my web app except the ID specified, and only
          from the IP address of where the web app is hosted.

          3. Generate a SQL script to load the default database tables.


          I was going to have the web app detect it's not installed by default
          and start to display a wizard-like set of screens that describe the
          process of setting the application up in really user-friendly terms.

            Comment

            • Erwin Moller
              #4
              Re: Best PostgreSQL Install Practices

              Alfred wrote:
              Erwin Moller, thanks for your opinion. That's pretty much how I was
              going to do it:
              >
              1. Provide DB's CREATEDB and GRANT statements for the user account used
              by the web app.
              >
              2. Suggest the pg_hba.conf changes for the sysop, and try to prohibit
              anything connecting to my web app except the ID specified, and only
              from the IP address of where the web app is hosted.
              >
              3. Generate a SQL script to load the default database tables.
              >
              >
              I was going to have the web app detect it's not installed by default
              and start to display a wizard-like set of screens that describe the
              process of setting the application up in really user-friendly terms.
              Sounds good. :-)

              Good luck!

              Regards,
              Erwin Moller

                Comment

                • Alfred
                  #5
                  Re: Best PostgreSQL Install Practices

                  BTW, Erwin Moller (and others), a friend sent me these links on how to
                  optimize postgresql.conf for more speed. By default this is usually
                  tuned way back on most Linux distros. These hyperlinks should be good
                  information for PHP programmers who use PostgreSQL.

                  404 Not Found
                  http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html


                  http://www.powerpostgresql.com/Downloads/annotated_conf_80.html


                    Comment

                    Previous template Next
                    Working...