Skip site navigation (1) Skip section navigation (2)

Created non-owner user cannot see database

From: "Daniel J(dot) Summers" <daniel(at)djs-consulting(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Created non-owner user cannot see database
Date: 2008-07-20 02:47:30
Message-ID: 4882A742.4060500@djs-consulting.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)djs-consulting(dot)com <mailto:daniel(at)djs-consulting(dot)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++++

Responses

pgsql-admin by date

Next:From: Niklas JohanssonDate: 2008-07-20 12:16:54
Subject: Re: Created non-owner user cannot see database
Previous:From: Ernesto Eduardo Medina NúñezDate: 2008-07-19 21:06:46
Subject: server lacks of instrumentation functions in CentOS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group