By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

Permissions issue?

P: n/a
Greetings,

I'm trying to create a user without create privileges and I don't seem
to be able to do it. I could be clueless, but after my revoke
statements, the new user still seems to be able to create dbs, and then
have full privileges on them. Am I missing something?

Below is the output of my terminal window where I create a new user
(which doesn't have select privileges), but even after revoke can still
create new tables.

Any info would be much appreciated, I need to give someone select
access to a view and I can't do it if they can still create dbs.

Cheers,

Chris
chris@torvalds chris]$ createuser newuser
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
[chris@torvalds chris]$ psql a -U newuser
Welcome to psql 7.4RC2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

a=> select * from ind;
ERROR: permission denied for relation ind
a=> \q
[chris@torvalds chris]$ psql
Welcome to psql 7.4RC2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

chris=# revoke all on database a from newuser;
REVOKE
chris=# revoke create on database a from newuser;
REVOKE
chris=# \q
[chris@torvalds chris]$ psql a -U newuser
Welcome to psql 7.4RC2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

a=> create table foo(bar integer);
CREATE TABLE
a=> insert into foo values(1);
INSERT 6273211 1
a=> select * from foo;
bar
-----
1
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Wed, 2003-12-17 at 13:20, Christopher Murtagh wrote:
I'm trying to create a user without create privileges and I don't
seem to be able to do it. I could be clueless, but after my revoke
statements, the new user still seems to be able to create dbs, and
then have full privileges on them. Am I missing something?


Oops, I meant to say that they could create tables, etc. Not dbs.

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

P: n/a
On Wed, 17 Dec 2003, Christopher Murtagh wrote:
Greetings,

I'm trying to create a user without create privileges and I don't seem
to be able to do it. I could be clueless, but after my revoke
statements, the new user still seems to be able to create dbs, and then
have full privileges on them. Am I missing something?

Below is the output of my terminal window where I create a new user
(which doesn't have select privileges), but even after revoke can still
create new tables.


I think you probably want to revoke create on the public schema. Create on
databases controls the creation of schemas.
From the grant page:

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To
rename an existing object, you must own the object and have this privilege
for the containing schema.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #3

P: n/a
On Wed, 2003-12-17 at 13:54, Stephan Szabo wrote:
I think you probably want to revoke create on the public schema. Create on
databases controls the creation of schemas.
From the grant page:


Hrm, thanks for the reply. I tried that too. Here's what I got (below).
Am I missing something obvious?
[chris@torvalds chris]$ createuser newuser
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
[chris@torvalds chris]$ psql chris
Welcome to psql 7.4RC2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

chris=# REVOKE ALL ON SCHEMA public FROM newuser;
REVOKE
chris=# REVOKE ALL ON DATABASE chris FROM newuser;
REVOKE
chris=# \q
[chris@torvalds chris]$ psql chris -U newuser
Welcome to psql 7.4RC2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

chris=> create table foo(bar integer);
CREATE TABLE
chris=> insert into foo values (1);
INSERT 6274026 1
chris=> select * from foo;
bar
-----
1
(1 row)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4

P: n/a
Christopher Murtagh <ch*****************@mcgill.ca> writes:
Am I missing something obvious?


The permissions were granted to PUBLIC, not to newuser, and so the
REVOKE doesn't do anything. You'd need to revoke rights from PUBLIC and
then grant them back to whomever should have them.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #5

P: n/a

On Wed, 17 Dec 2003, Christopher Murtagh wrote:
On Wed, 2003-12-17 at 13:54, Stephan Szabo wrote:
I think you probably want to revoke create on the public schema. Create on
databases controls the creation of schemas.
From the grant page:


Hrm, thanks for the reply. I tried that too. Here's what I got (below).
Am I missing something obvious?


Ah, right, PUBLIC has rights to the public schema. You'll need to
revoke those and then grant usage to newuser I believe (and correct
permissions to other users as appropriate). Forgot about that.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #6

P: n/a
On Wed, 2003-12-17 at 15:25, Tom Lane wrote:
Christopher Murtagh <ch*****************@mcgill.ca> writes:
Am I missing something obvious?


The permissions were granted to PUBLIC, not to newuser, and so the
REVOKE doesn't do anything. You'd need to revoke rights from PUBLIC and
then grant them back to whomever should have them.


Ahhh. that's it! I was missing something obvious. Thanks for the clue!

Cheers,

Chris

--
Christopher Murtagh
Enterprise Systems Administrator
ISR / Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.