Permissions issue?

[Christopher Murtagh]

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

[Christopher Murtagh]

Re: Permissions issue?

On Wed, 2003-12-17 at 13:20, Christopher Murtagh wrote:[color=blue]
> 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?[/color]

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

[Stephan Szabo]

Re: Permissions issue?

On Wed, 17 Dec 2003, Christopher Murtagh wrote:
[color=blue]
> 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.[/color]

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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Christopher Murtagh]

Re: Permissions issue?

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

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

[Tom Lane]

Re: Permissions issue?

Christopher Murtagh <christopher.mu rtagh@mcgill.ca > writes:[color=blue]
> Am I missing something obvious?[/color]

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 majordomo@postg resql.org

[Stephan Szabo]

Re: Permissions issue?


On Wed, 17 Dec 2003, Christopher Murtagh wrote:
[color=blue]
> On Wed, 2003-12-17 at 13:54, Stephan Szabo wrote:[color=green]
> > I think you probably want to revoke create on the public schema. Create on
> > databases controls the creation of schemas.
> > From the grant page:[/color]
>
> Hrm, thanks for the reply. I tried that too. Here's what I got (below).
> Am I missing something obvious?[/color]

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

[Christopher Murtagh]

Re: Permissions issue?

On Wed, 2003-12-17 at 15:25, Tom Lane wrote:[color=blue]
> Christopher Murtagh <christopher.mu rtagh@mcgill.ca > writes:[color=green]
> > Am I missing something obvious?[/color]
>
> 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.[/color]

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