Thread: Permissions issue?
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)
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
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.
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)
Christopher Murtagh <christopher.murtagh@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
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.
On Wed, 2003-12-17 at 15:25, Tom Lane wrote: > Christopher Murtagh <christopher.murtagh@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