I'm running PostgreSQL 8.3 on Kubuntu 8.04. My goal is to create a
database with one user as its owner, and another as a user with
non-administrative access to this database that I'll use as the runtime
user for a web application. As the user "postgres", I executed the
following commands in psql; (names changed to protect the innocent)
create database custom_database;
create user user_no_1 with password 'thisPassword';
alter database custom_database owner to user_no_1;
create user user_no_2 with password 'anotherPw';
grant usage on database custom_database to user_no_2;
Now, none of these commands failed - they all came back with "CREATE
ROLE" (or the appropriate response). Then, as the owner user, I was
able to run my create schema/table script. I grant "usage" on the
schema to this user, and within this script, each CREATE TABLE is
followed by a
grant select, insert, update, delete on table this_schema.this_table to
user_no_2;
However, when I log in with this user using phpPgAdmin, I cannot see any
databases, and when I try to execute a SQL statement through my web
application (using PHP's PDO), I'm told that the table I'm trying to hit
doesn't exist (specifically, SQL state 42P01, "Undefined table: 7 ERROR:
relation "this_table" does not exist"). I tried
grant all privileges on custom_database to user_no_2;
, but that still doesn't do anything for me. I also created this user
as a Linux user, with the same password as it has in the database, but
that didn't do anything either. I've been Googling this and banging my
head (figuratively, of course) over this for a few days, but I haven't
come to any good conclusion. I'd rather not run the web application as
the schema owner - I'm sure there's something I'm missing. I'd
appreciate any advice anyone may have - thanks!
--
Daniel J. Summers
Owner, DJS Consulting
E-mail - daniel@djs-consulting.com <mailto:daniel@djs-consulting.com>
Website - http://www.djs-consulting.com <http://www.djs-consulting.com/>
Technology Blog - http://www.djs-consulting.com/linux/blog
GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w !O M--
V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e h---- r+++ y++++