Postgresql – Can’t create new tablespace for PostgreSQL data. Permissions denied

postgresql

For some reason I don't seem to be allowed to set my PostgreSQL data folder to a drive other than the C drive (Windows 8 machine).

1) If I try to do so when installing PostgreSQL, the installation fails. Therefore I need to choose the C drive to make installation work.

2) If I try to move the data folder later on (https://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows), PostgreSQL service stops running.

3) Finally, if I try to create a tablespace in the D drive, I am not allowed to do so.

When I run the following query:

CREATE TABLESPACE drive_D
OWNER postgres
LOCATION 'D:\PostgreSQL\data'

I get the following error:

ERROR:  could not set permissions on directory "D:/PostgreSQL/data": Permission denied
SQL state: 42501

I am an admin on that machine and I have made sure that I have 'full control' on the D drive.

I also followed advice given on this link "How to create a tablespace on Windows 7 for Postgresql 9.2?". I gave 'NETWORK SERVICE' full control on the D drive, but that didn't solve the problem.

Best Answer

For some reason I don't seem to be allowed to change my PostgreSQL data folder to a drive other than the C drive.

This is not the correct way to do that anyway. Creating a tablespace and moving the whole cluster are different things.

I am an admin on that machine and I have made sure that I have 'full control' on the D drive.

It doesn't matter who you are, what matters is who is the service account running PostgreSQL, and what permissions that account has.

Related Solutions

PostgreSQL Permissions – Unable to Use Views After Restoring Data

To troubleshoot, I would start by looking at the output of SELECT current_user; and SHOW search_path;. Also, the output of \dp termin in psql.

My suspicion here is that you are stumbling over mixed case identifiers. The error message mentions a table

Map

Notice the capital letter M. Are you sure there is no mix-up with another table named map (lower case)?
Are PostgreSQL column names case-sensitive?

(Or, in another schema coming earlier in the search_path?)

PostgreSQL – Fixing Vagrant VM Extension Creation Issues

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.

Best Answer

Related Solutions

PostgreSQL Permissions – Unable to Use Views After Restoring Data

PostgreSQL – Fixing Vagrant VM Extension Creation Issues

Related Question