psql:scaffolding.sql:11: ERROR: permission denied for database operation "alter user stratcon set search_path to noit,public;"

[kirantpatil]

System Information:

Fedora Linux 15

[akshatha@localhost reconnoiter]$ postgres --version
postgres (PostgreSQL) 9.0.6

Earlier I tried to run the scaffolding.sql using psql, it gave me error, so I reordered the scaffolding.sql file content as follows,

snippet of scaffolding.sql file

create user reconnoiter;
create database reconnoiter with owner = reconnoiter;
create user stratcon with encrypted password 'stratcon';
create user prism with encrypted password 'prism';
\c reconnoiter reconnoiter;

create language plpgsql;
create schema noit;
create schema stratcon;
create schema prism;
alter user stratcon set search_path to noit,public;
alter user prism set search_path to noit,public;

begin;

grant usage on schema stratcon to stratcon;
grant usage on schema stratcon to prism;
grant usage on schema noit to stratcon;
grant usage on schema noit to prism;
grant usage on schema prism to prism;

---

I am getting psql:scaffolding.sql:11: ERROR: permission denied error on line 11 and 12 which are,
alter user stratcon set search_path to noit,public;
alter user prism set search_path to noit,public;

Except those two errors, the rest of scaffolding.sql installs properly.

Error Output:

-bash-4.2$ psql
psql (9.0.6)
Type "help" for help.

postgres=# \i scaffolding.sql
psql:scaffolding.sql:1: ERROR: role "reconnoiter" already exists
psql:scaffolding.sql:2: ERROR: database "reconnoiter" already exists
psql:scaffolding.sql:3: ERROR: role "stratcon" already exists
psql:scaffolding.sql:4: ERROR: role "prism" already exists
You are now connected to database "reconnoiter" as user "reconnoiter".
psql:scaffolding.sql:7: ERROR: language "plpgsql" already exists
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA
psql:scaffolding.sql:11: ERROR: permission denied
psql:scaffolding.sql:12: ERROR: permission denied
BEGIN
GRANT
GRANT
GRANT
GRANT
GRANT
CREATE FUNCTION

[kirantpatil]

Earlier I enabled trust for only local.

Now I enabled trust for host and the db connection happens.

/var/lib/pgsql/data/pg_hba.conf snippet:

TYPE DATABASE USER CIDR-ADDRESS METHOD

"local" is for Unix domain socket connections only

local all all ident

local all all trust

IPv4 local connections:

host all all 127.0.0.1/32 ident

host all all 127.0.0.1/32 trust

IPv6 local connections:

host all all ::1/128 ident

[kirantpatil]

Sorry, this is not yet resolved.

[kirantpatil]

I am closing this, since I will not use Fedora 15 and right now testing on Ubuntu 10.04. Next I will try it on CentOS/Scientific Linux v6.2.

root@gtt-ubuntu:~# psql --version
psql (PostgreSQL) 8.4.8