testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?

From: Lou Picciano <loupicciano(at)comcast(dot)net>
To: pgsql-testers <pgsql-testers(at)postgresql(dot)org>
Subject: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?
Date: 2010-05-11 20:41:00
Message-ID: 1987554247.9039561273610460655.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-testers

[TEST REPORT]

[Release]: 9.0Beta1

[Test Type]: Privileges assignment on new database.

[Test]: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?

[Platform]: Solaris 10 SPARC Enterprise 450 Quad

[Parameters]:

[Failure]: Don't know. Is this a (new) default behavior?

[Results]: on a newly-installed cluster,

1) create a new SUPERUSER 'admin', with all the bells and whistles.

2) CREATE a new database 'test', including several tables, with owner 'admin' in PUBLIC.

3) psql \dt command verifies owner 'admin' for all tables

4) psql \dp command shows NO Access privileges listed. OK?

5) INSERT data into (test)table1. WORKS.

6) CREATE new user "Mr. User" LOGIN, etc.

7) GRANT ALL ON DATABASE test TO "Mr. User";

8) SELECT * FROM table1; --------------- ERROR: permission denied for relation table1

9) GRANT ALL ON TABLE table1 TO "Mr. User";

10) SELECT * FROM table1; --------------- WORKS

[Comments]: Is this consistent behavior? Is it necessary to explicitly grant privileges to each object in a database?

We are aware of the new ALTER DEFAULT PRIVILEGES command, but, per docs, it only affects tables (including views), sequences, and functions , correct?
IE, it cannot affect database privileges.

Responses

Browse pgsql-testers by date

  From Date Subject
Next Message Josh Berkus 2010-05-11 21:15:02 Re: testing simple SELECT commands on newly-created databases. Apparently blocked by (lack of) privileges?
Previous Message Lou Picciano 2010-05-07 23:47:29 ERROR: cannot drop objects owned by role postgres because they are required by the database system