PostgreSQL vagrant VM cannot create extension

I had some problems during installation of PostgreSQL and I eventually decided to go for a VM solution managed with vagrant, as explained in postgresql wiki.

(At the beginning my motivation was just to solve the installation issue but I find that a virtual machine solution has many advantages).

I started working with it and everything looked very good. But eventually I wanted to use crosstab(doc), however tablefunc extension was not installed in the virtual machine, and I can't find a way to install it.

The virtual machine creates a user for the application when you run it first time, which is fine, and it creates a database for the application as well. But if I try to install the extension with the application user

create extension tablefunc;

I get the following error (anyhow expected):

ERROR: permission denied to create extension "tablefunc"

SQL state: 42501

Hint: Must be superuser to create this extension.

The point is that I don't have the password for postgres user, so I cannot install the extension using pgAdmin. (This is already an issue that scares me a bit, if I have other problems and I need the administrator. But I've been browsing around and haven't found any comment about this.)

In postgres wiki, link above, it describes a way to connect to the VM as superuser, which I also tried,

vagrant ssh
sudo su - postgres

but again when I tried to run the command for the installation I was asked for the password of postgres user in the database. For example, one of the trials:

postgres@postgresql:~$ psql -h localhost -U postgres -d vic -c 'create extension tablefunc'
Password for user postgres: 
psql: FATAL:  password authentication failed for user "postgres"

My question is then, if there is a way to install the extension for the vagrant VM (or anyway to run any command with the superuser). Also, I'll be much happier having the superuser password, so that I could manage everything from pgAdmin.

Many thanks for rea

postgres has no password after an automated install, it's expected to authenticate through the peer method. sudo su - postgres is the first step to it. The second step is to connect through the Unix local domain socket, but your command doesn't do that, it connects through TCP, that's why you're stuck with the password problem.

The installed pg_hba.conf file probably starts with these two rules, apart from comments:

# Database administrative login by Unix domain socket
local all postgres peer

# IPv4 local connections:
host all all 127.0.0.1/32 md5
[...]

When doing a TCP connection with -h localhost... the first rule doesn't match. The second rule matches and it triggers the demand for a password, but as none was set this can only fail (empty passwords are not allowed either).

The solution is to remove -h localhost from psql invocation, for the first rule to be taken instead. As it's on a Unix system, it will attempt a connection through the Unix domain socket.

Once logged to psql as the postgres user, you may set a password with the \password command or ALTER USER postgres PASSWORD 'foobar';

See also https://help.ubuntu.com/community/PostgreSQL

In the section Using pgAdmin III GUI , they suggest to change the pg_hba.conf first rule from auth peer to md5, after having set a password. Personally I don't quite get why. I'd rather leave that rule alone and run pgAdmin from my own Unix account, choosing localhost TCP connections.

StackExchange Database Administrators Q#105985, answer score: 3