The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Can't create PostgreSQL db's.

Discussion in 'General Discussion' started by SXR1337, Feb 18, 2008.

  1. SXR1337

    SXR1337 Member

    Joined:
    May 4, 2007
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    I have postgreSQL installed on a cPanel server but am unable to create a postgre db. All I do is just fill in the details and hit create , and it does it, it does not return any errors , the database just doesnt show up.

    Can anyone help me please?

    Regards,
    Michel
     
  2. JPC-Keith

    JPC-Keith Registered

    Joined:
    Dec 26, 2007
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Only the main cPanel user has access to pgsql databases using phpPgAdmin by default.

    If you wish to grant full ownership of the database to a particular user, you
    will first need to alter the database owner by executing the following command from CLI:

    psql -U postgres -c 'ALTER DATABASE database_name OWNER TO user_name' template1

    Note: database_name and user_name should be replaced with the database and user
    names respectively.

    Once you have done this, you will need to grant access to each individual
    table. To get a listing of all tables contained in a database, execute the
    following:

    psql -U postgres -c '\dt' database_name

    You can then execute the following for each of the tables:

    psql -U postgres -c 'GRANT ALL ON table_name TO user_name' database_name

    The following portions of the vendor documentation describe the syntax for
    these operations.

    http://www.postgresql.org/docs/8.1/s...rdatabase.html
    http://www.postgresql.org/docs/8.1/s...ltertable.html
     
  3. mm1250

    mm1250 Well-Known Member

    Joined:
    Nov 10, 2006
    Messages:
    108
    Likes Received:
    0
    Trophy Points:
    16
    I think I know what your issue is. If you also try to goto the phpPGsql and it doesn't let you log in and gives access denied try the following:

    Make sure the password is in /var/lib/pgsql/.pgpass

    The format is as follows...

    *:*:*:postgres:PASSWORD

    Modify /var/lib/pgsql/data/pg_hba.conf

    It should contain the following...

    local all all md5
    host all all 127.0.0.1 255.255.255.255 md5

    Change 'md5' to 'trust' to disable authentication then restart postgres.

    `/etc/init.d/postgresql restart`

    Now you should be able to connect to postgresql as user 'postgres' without a password to modify the password.

    `psql -u template1`

    Run the following sql command.

    "alter user postgres with password 'NEW PASSWORD HERE';"

    Finally change /var/lib/pgsql/data/pg_hba.conf back to its original format and restart postgresql one more time. Now you should be able to authenticate using user postgres and the password you specified.

    After that restart /etc/rc.d/init.d/postgres restart
     
  4. djbob2

    djbob2 Well-Known Member

    Joined:
    May 14, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    This really seems like a design bug. I'm going to put this in as a feature request for cPanel - hopefully they will allow PHPPgSQL access of personal databases by default.

    Same here. This kind of negates the purpose of automating PostgreSQL database creation...

    UPDATE
    I have not yet filed a bug, as it seems this problem is not on cPanel's end. I had this error, but it was caused by attempting to use a fix somebody on the forums suggested so that PHPPgAdmin would work. The fix was to use md5 authentication instead of ident sameuser authentication.

    It seems that cPanel needs ident sameuser authentication, but PHPPgAdmin needs md5 authentication. I am looking into combining the two right now.

    ANOTHER UPDATE
    After reading the PostgreSQL manual page on pg_hba.conf (http://postgresql.mirrors-r-us.net/docs/8.2/static/auth-pg-hba-conf.html), it seems like PgSQL does not allow multiple authentication methods - you can use either md5 authentication or ident sameuser authentication.

    This basically means you can either use the cPanel PostgreSQL interface or PHPPgAdmin. Not the ideal configuration.

    MORE UPDATES
    Confirmed here that "ident sameuser" authentication is not compatible with PHPPgAdmin.
     
    #4 djbob2, Aug 4, 2008
    Last edited: Aug 4, 2008
  5. djbob2

    djbob2 Well-Known Member

    Joined:
    May 14, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    Let me summarize the problem here:

    1) cPanel needs to be able to access PostgreSQL. The only way cPanel is able to authenticate is via the "ident sameuser" authentication method, or just using "trust" to allow anyone.
    2) PHPPgAdmin needs to be able to access PostgreSQL. The only way PHPpgAdmin is able to authenticate is via the "password" or "md5" authentication methods, or just using "trust" to allow anyone.
    3) "Trust" is not an OK configuration because it allows users to access other users' databases.
    4) PostgreSQL does not allow multiple authentication methods.

    Seems like we're in a deadlock. Does anybody have any possible solutions for this problem?
     
    #5 djbob2, Aug 4, 2008
    Last edited: Aug 4, 2008
  6. RobertF

    RobertF Member

    Joined:
    May 22, 2006
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    Hello,
    This is something you can try.

    pg_hba.pg:
    local all all ident sameuser
    host all all 127.0.0.1 255.255.255.255 md5

    Then in cpanel/base/3rdparty/phpPgAdmin/conf/config.inc.php:
    Change $conf['servers'][0]['host'] = '';
    to
    $conf['servers'][0]['host'] = 'localhost';

    This will allow cpanel to use ident sameuser for local connection but will let pgadmin users use the md5 passwords.

    I also think that this will require a restart of postgres. I don't think a reload would do it.
     
Loading...

Share This Page