On Wed, Feb 11, 2009 at 5:38 PM, Tony Berth <tonybe...@googlemail.com>wrote:
> On Wed, Feb 11, 2009 at 5:31 PM, Jason Dixon <ja...@dixongroup.net> wrote: > >> On Wed, Feb 11, 2009 at 05:24:41PM +0100, Tony Berth wrote: >> > On Wed, Feb 11, 2009 at 5:17 PM, Jason Dixon <ja...@dixongroup.net> >> wrote: >> > >> > > On Wed, Feb 11, 2009 at 05:07:45PM +0100, Tony Berth wrote: >> > > > On Wed, Feb 11, 2009 at 4:13 PM, Jason Dixon <ja...@dixongroup.net> >> > > wrote: >> > > > >> > > > > On Wed, Feb 11, 2009 at 03:59:53PM +0100, Tony Berth wrote: >> > > > > > Dear List, >> > > > > > >> > > > > > in a 4.4 box with Postgresql 8.3.3 I try to create a new >> tablespace >> > > in a >> > > > > > different filesystem and get the following error: >> > > > > > >> > > > > > ----------------------------------------------- >> > > > > > template1=# CREATE TABLESPACE <tbspace-name> OWNER <DB-user> >> LOCATION >> > > > > > '/home/<DB-user>/db'; >> > > > > > ERROR: could not set permissions on directory >> "/home/<DB-user>/db": >> > > > > > Permission denied >> > > > > > ----------------------------------------------- >> > > > > > >> > > > > > "/home/<DB-user>/db" does exist and belongs to <DB-user> who has >> the >> > > same >> > > > > > name in Postgresql and in Unix! >> > > > > >> > > > > It sounds to me like "DB-user" is a role account. Any >> filesystem-level >> > > > > changes would probably be performed by the _postgresql user that >> the >> > > > > database process runs at. >> > > > > >> > > > > >> http://www.postgresql.org/docs/8.3/static/manage-ag-tablespaces.html >> > > > >> > > > I did assign that directory to '_postgresql' user too but I still >> get the >> > > > same error! >> > > > >> > > > <DB-user> is indeed a role account and a Unix user! >> > > > >> > > > I can't connect via psql using '_postgresql'. I can only connect as >> > > > 'postgres'! >> > > >> > > Obviously you're doing something wrong. But you're not showing us >> your >> > > commands or the errors, so we're not going to be much help. >> > > >> > > P.S. I also suggest re-reading the PostgreSQL documentation. You >> need >> > > a better understanding of the role accounts and how they differ from >> the >> > > system user postgresql runs as. >> > >> > I did the following: >> > >> > - as '_postgresql' UNIX user I issued psql -d template1 postgres >> > - then issued twice the CREATE TABLESPACE cmd as already described and >> got >> > the error (with OWNER defined both 'postgres' and '<DB-user>') >> > >> > that's all I did! >> >> Ok, let's try this one more time. Put yourself in my shoes. I'd like >> to help, but you're giving me ZERO useful information. What else might >> be useful? Perhaps to see the actual directory permissions? To see >> your commands from the actual command-line, and not a re-typing of what >> you *think* you ran? >> >> -- >> Jason Dixon >> DixonGroup Consulting >> http://www.dixongroup.net/ >> > > when using 'OWNER <DB-user>', the permissions of the directory were set to > the UNIX '<DB-user>' and when using 'OWNER postgres' the permissions were > set to '_postgresql'! > > Now this should be wrong but there is no UNIX user 'postgres' in OpenBSD! I > tried exactly the same scenario in a debian box and it did work but there, I > did have a UNIX 'postgres' user instead! > > I'll do the same thing all over again and I'll copy the commands and send > to you in case I missed something! > > Thanks > > Tony > case#1: my "/home/<DB-user>/db" dir is owned by _postgresql:_postgresql $su - _postgresql Password: $ psql -d template1 postgres Password for user postgres: Welcome to psql 8.3.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit template1=# CREATE TABLESPACE kp OWNER postgres LOCATION '/home/<DB-user>/db'; ERROR: could not set permissions on directory "/home/<DB-user>/db": Permission denied case #2: my "/home/<DB-user>/db" dir is owned by <DB-user>:users $ psql -d template1 <DB-user> Password for user <DB-user>: Welcome to psql 8.3.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit template1=# CREATE TABLESPACE kp OWNER <DB-user> LOCATION '/home/kp/db'; ERROR: could not set permissions on directory "/home/<DB-user>/db": Permission denied Thanks Tony
