Category Archives: PostgreSQL

Change password for postgres user in PostgreSQL

In order to change the password for the postgres user you should NEVER do this:

$ sudo passwd postgres

By default the postgres user is locked and if you change the password with passwd the account is immediately unlocked, thus anyone could login to the system with it. This could be very dangerous if you configure an easy password.

The proper way is doing it inside the psql terminal with the password command:

$ sudo -u postgres psql postgres
postgres=# password postgres
Enter new password: 
Enter it again: 
postgres=# q

Ref: http://serverfault.com/a/325596

Fix ‘ERROR: column “spclocation” does not exist’ in phpPgAdmin

I was going to create a new database with phpPgAdmin and it crashed with this error:

ERROR:  column "spclocation" does not exist
LINE 1: ...pg_catalog.pg_get_userbyid(spcowner) AS spcowner, spclocatio...
                                                             ^

php-postgresql

We’ll have to add some code to phpPgAdmin to fix this. In my case this worked for phpPgAdmin 5.0.4 and PostgreSQL 9.3 but if you have for example PostgreSQL 9.2 just change the number. It should work.

  1. Open your phpPgAdmin location and change to /classes/database.
  2. Open Connection.php and add a case line for your PostgreSQL version:
    // Detect version and choose appropriate database driver
    switch (substr($version,0,3)) {
        case '9.3': return 'Postgres93'; break;
        case '8.4': return 'Postgres'; break;
        case '8.3': return 'Postgres83'; break;
        case '8.2': return 'Postgres82'; break;
        case '8.1': return 'Postgres81'; break;
        case '8.0':
        case '7.5': return 'Postgres80'; break;
        case '7.4': return 'Postgres74'; break;
    }
  3. Copy Postgres84.php to Postgres93.php.
  4. Open Postgres.php and copy functions getTablespaces and getTablespace.
  5. Open Postgres93.php and change class name to Postgres93 and $major_version variable value to 9.3.
  6. Also in Postgres93.php paste the functions you copied before inside the class.
  7. Replace ", spclocation," with ", pg_tablespace_location(oid) as spclocation," in both functions.

Ref: http://stackoverflow.com/questions/13467949/error-on-creating-database-in-phppgadmin