pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCreated non-owner user cannot see database
5+ messages / 3 participants
[nested] [flat]
* Created non-owner user cannot see database
@ 2008-07-20 02:47 Daniel J. Summers <daniel@djs-consulting.com>
0 siblings, 2 replies; 5+ messages in thread
From: Daniel J. Summers @ 2008-07-20 02:47 UTC (permalink / raw)
To: 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@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++++
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Created non-owner user cannot see database
@ 2008-07-20 12:16 Niklas Johansson <niklas@codecraft.se>
parent: Daniel J. Summers <daniel@djs-consulting.com>
1 sibling, 0 replies; 5+ messages in thread
From: Niklas Johansson @ 2008-07-20 12:16 UTC (permalink / raw)
To: Daniel J. Summers <daniel@djs-consulting.com>; +Cc: pgsql-admin
On 20 jul 2008, at 04.47, Daniel J. Summers wrote:
> 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).
Are you sure?
'GRANT USAGE ON DATABASE...' is invalid syntax. You probably want
'GRANT CONNECT ON DATABASE...'.
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Created non-owner user cannot see database
@ 2008-07-20 12:50 Niklas Johansson <pgmailings@codecraft.se>
parent: Daniel J. Summers <daniel@djs-consulting.com>
1 sibling, 1 reply; 5+ messages in thread
From: Niklas Johansson @ 2008-07-20 12:50 UTC (permalink / raw)
To: Daniel J. Summers <daniel@djs-consulting.com>; +Cc: pgsql-admin
On 20 jul 2008, at 04.47, Daniel J. Summers wrote:
> 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).
Are you sure?
'GRANT USAGE ON DATABASE...' is invalid syntax. You probably want
'GRANT CONNECT ON DATABASE...'.
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Created non-owner user cannot see database
@ 2008-07-20 14:09 Daniel J. Summers <daniel@djs-consulting.com>
parent: Niklas Johansson <pgmailings@codecraft.se>
0 siblings, 1 reply; 5+ messages in thread
From: Daniel J. Summers @ 2008-07-20 14:09 UTC (permalink / raw)
To: pgsql-admin
Niklas Johansson wrote:
>
> On 20 jul 2008, at 04.47, Daniel J. Summers wrote:
>> 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).
>
> Are you sure?
>
> 'GRANT USAGE ON DATABASE...' is invalid syntax. You probably want
> 'GRANT CONNECT ON DATABASE...'.
Ah - it had been a while since I originally set it up. I just tried
"GRANT CONNECT", and the problem still exists. Thanks for the
suggestion. :)
--
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++++
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Created non-owner user cannot see database
@ 2008-07-20 17:32 Niklas Johansson <pgmailings@codecraft.se>
parent: Daniel J. Summers <daniel@djs-consulting.com>
0 siblings, 0 replies; 5+ messages in thread
From: Niklas Johansson @ 2008-07-20 17:32 UTC (permalink / raw)
To: Daniel J.Summers <daniel@djs-consulting.com>; +Cc: pgsql-admin
On 20 jul 2008, at 16.09, Daniel J. Summers wrote:
> I just tried "GRANT CONNECT", and the problem still exists.
If you try to login to the database using psql, instead of
phpPgAdmin, what does it tell you? Error messages?
Sincerely,
Niklas Johansson
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-07-20 17:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-20 02:47 Created non-owner user cannot see database Daniel J. Summers <daniel@djs-consulting.com>
2008-07-20 12:16 ` Niklas Johansson <niklas@codecraft.se>
2008-07-20 12:50 ` Niklas Johansson <pgmailings@codecraft.se>
2008-07-20 14:09 ` Daniel J. Summers <daniel@djs-consulting.com>
2008-07-20 17:32 ` Niklas Johansson <pgmailings@codecraft.se>
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