From: Daniel J. Summers <daniel@djs-consulting.com>
To: pgsql-admin@postgresql.org
Subject: Created non-owner user cannot see database
Date: Sat, 19 Jul 2008 20:47:30 -0600
Message-ID: <4882A742.4060500@djs-consulting.com> (raw)
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++++
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: daniel@djs-consulting.com
Subject: Re: Created non-owner user cannot see database
In-Reply-To: <4882A742.4060500@djs-consulting.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox