Thread: Something I don't understand with the use of schemas

Something I don't understand with the use of schemas

From
Guillaume LELARGE
Date:
Hi,

At my work, I have to dump a database and restore it on another database under 
a specific schema. My first idea was to create the new schema on the old 
database, move all the database objects on this new schema, dump the old db 
and restore on the new one. But I have to move quite a lot of objets. So, I 
took another way to do it : rename the public schema, dump and restore. 
Pretty simple and straightforward.

After the "ALTER SCHEMA public RENAME TO foobar", pgAdmin can't see it 
anymore. I made a patch to fix this, send a mail to the pgAdmin hackers and a 
thread began on the right way to handle this. I was pretty sure I was right 
but I'm not so sure anymore.

Apparently, I can rename all schemas, even system schemas !
metier=# alter schema pg_catalog rename to foobar;
ALTER SCHEMA

Doing so is a bit dumb because everything is now broken on this database. I 
can't use psql, pg_dump, ... But I think this command should protect the user 
from doing weird stuff. For example, DROP SCHEMA doesn't allow me to drop 
pg_* schemas and that seems right to me.

So here it is. I think we should disallow user from renaming system schemas 
and I think we should made clear if public is a system or public schema.

Regards.


-- 
Guillaume.
<!-- http://abs.traduc.org/    http://lfs.traduc.org/    http://traduc.postgresqlfr.org/ -->



Re: Something I don't understand with the use of schemas

From
Tom Lane
Date:
Guillaume LELARGE <guillaume.lelarge@gmail.com> writes:
> Apparently, I can rename all schemas, even system schemas !
> metier=# alter schema pg_catalog rename to foobar;
> ALTER SCHEMA

If you are superuser, you can do anything you want, up to and including
breaking the system irretrievably.  Compare "rm -rf /" on Unix.  We
won't be putting training wheels on superuser status for the same
reasons that no one finds it a good idea to restrict root's abilities.
        regards, tom lane


Re: Something I don't understand with the use of schemas

From
Alvaro Herrera
Date:
Tom Lane wrote:
> If you are superuser, you can do anything you want, up to and including
> breaking the system irretrievably.  Compare "rm -rf /" on Unix.  We
> won't be putting training wheels on superuser status for the same
> reasons that no one finds it a good idea to restrict root's abilities.

However there is an effort to get rid of root in some Unix lands,
separating its responsabilities with more granularity.  Maybe there
could be an effort, not to hand-hold the true superusers, but to
delegate some of its responsabilities to other users.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: Something I don't understand with the use of schemas

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> However there is an effort to get rid of root in some Unix lands,
> separating its responsabilities with more granularity.  Maybe there
> could be an effort, not to hand-hold the true superusers, but to
> delegate some of its responsabilities to other users.

We did that already (see CREATEROLE privilege in 8.1)
        regards, tom lane


Re: Something I don't understand with the use of schemas

From
"Joshua D. Drake"
Date:
>
> However there is an effort to get rid of root in some Unix lands,
> separating its responsabilities with more granularity.  Maybe there
> could be an effort, not to hand-hold the true superusers, but to
> delegate some of its responsabilities to other users.
>   
Like sudo?

Joshua D. Drake





Re: Something I don't understand with the use of schemas

From
Alvaro Herrera
Date:
Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > However there is an effort to get rid of root in some Unix lands,
> > separating its responsabilities with more granularity.  Maybe there
> > could be an effort, not to hand-hold the true superusers, but to
> > delegate some of its responsabilities to other users.
> 
> We did that already (see CREATEROLE privilege in 8.1)

Part of it.  We can still improve, I think.  Not that I have a concrete
proposal to make though.

Regarding CREATEROLE, I wonder why is that a role with that privilege is
able to create other roles containing any privileges (except
superuserness), and not just the privileges the creating role has.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: Something I don't understand with the use of schemas

From
Alvaro Herrera
Date:
Joshua D. Drake wrote:
>
> >However there is an effort to get rid of root in some Unix lands,
> >separating its responsabilities with more granularity.  Maybe there
> >could be an effort, not to hand-hold the true superusers, but to
> >delegate some of its responsabilities to other users.
>
> Like sudo?

I was thinking in the thing called "capabilities".

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: Something I don't understand with the use of schemas

From
"Joshua D. Drake"
Date:
>>
>> Like sudo?
>
> I was thinking in the thing called "capabilities".

I just meant as a metaphor ;)

>
>

-- 
Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/




Re: Something I don't understand with the use of schemas

From
Bruno Wolff III
Date:
On Sat, Dec 10, 2005 at 14:25:46 -0300, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Joshua D. Drake wrote:
> >
> > >However there is an effort to get rid of root in some Unix lands,
> > >separating its responsabilities with more granularity.  Maybe there
> > >could be an effort, not to hand-hold the true superusers, but to
> > >delegate some of its responsabilities to other users.
> >
> > Like sudo?
> 
> I was thinking in the thing called "capabilities".

Note that the linux 'capabilities' is not the same thing as 'capabilities'
is to some security researchers. To them a capability is sort of like a
file handle, and you can't do anything with an object until you get a file
handle to it. If you want to give some one else access to something you
have access to, you give them a copy of the file handle you hold. Doing things
this way simplifies some aspects of designing secure systems.


Re: Something I don't understand with the use of schemas

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Regarding CREATEROLE, I wonder why is that a role with that privilege is
> able to create other roles containing any privileges (except
> superuserness), and not just the privileges the creating role has.

The point of CREATEROLE was to allow a role to do many of the things
you'd routinely need superuser status for (create/drop roles, change
group membership, fix forgotten passwords, etc) without having the
privileges that make superuserness so dangerous, ie, the ability to
inflict random alterations on system catalogs or tables you don't own.
If we were to try to restrict CREATEROLE to the point where it has no
ability to "escalate privileges" then I think we'd just destroy the
usefulness of the concept entirely, and people would go back to using a
superuser role for day-to-day administration.  Example: such a
restriction would require that you can't grant membership in a group
unless you already are a member of same.  But making your day-to-day
admin role be a member of every group isn't helpful, it's just a PITA,
and arguably it makes you less secure not more so (because your admin
role thereby gets privileges it probably doesn't need).

The design assumption here is really that the user of a CREATEROLE
account is the DBA, meaning he also has access to a superuser account.
Thus the idea of CREATEROLE is not to get in his way unnecessarily, but
just to make sure that he can't accidentally break the system when he
didn't intend to.

It's possible that we shouldn't have included the restrictions against
changing superuser accounts from a CREATEROLE account; that might be
contributing to a mistaken view about what CREATEROLE is for.  You don't
give out CREATEROLE to anyone you don't trust.

Lastly: there already are mechanisms within SQL for the sort of
restricted administrator role you seem to be contemplating.  Give
someone group membership WITH ADMIN OPTION, and you've got yourself
a mini admin role.  I'm not sure we need another level between that
and CREATEROLE.
        regards, tom lane


Re: Something I don't understand with the use of schemas

From
Guillaume LELARGE
Date:
Le Samedi 10 Décembre 2005 17:43, vous avez écrit :
> Guillaume LELARGE <guillaume.lelarge@gmail.com> writes:
> > Apparently, I can rename all schemas, even system schemas !
> > metier=# alter schema pg_catalog rename to foobar;
> > ALTER SCHEMA
>
> If you are superuser, you can do anything you want, up to and including
> breaking the system irretrievably.  Compare "rm -rf /" on Unix.  We
> won't be putting training wheels on superuser status for the same
> reasons that no one finds it a good idea to restrict root's abilities.
>

Seems pretty fair.

I've made more tests on schemas. I'm able to drop information_schema and
public schemas but I can't drop pg_catalog and pg_toast. It makes me think
that only pg_* are system schemas and that public and information_schema are
public schemas. Am I right on this one ?


--
Guillaume.
<!-- http://abs.traduc.org/    http://lfs.traduc.org/    http://traduc.postgresqlfr.org/ -->



Re: Something I don't understand with the use of schemas

From
"Jim C. Nasby"
Date:
On Sat, Dec 10, 2005 at 09:18:32AM -0800, Joshua D. Drake wrote:
> >
> >However there is an effort to get rid of root in some Unix lands,
> >separating its responsabilities with more granularity.  Maybe there
> >could be an effort, not to hand-hold the true superusers, but to
> >delegate some of its responsabilities to other users.
> >  
> Like sudo?

I think it would be a huge benefit to have something equivalent to sudo
for psql (though maybe it could be generalized more). Having to change
to a different connection/authorization anytime you need to do something
requiring superuser is a real pita, and encourages things like making
yourself a superuser.

In the case of shell commands like createdb, sudo is indeed a pretty
viable alternative; you just need to do something like sudo -u
postgres command. But there's no equivalent for psql; if you sudo -u
postgres psql it's the equivalent of su - root (though at least you
wouldn't need to know the password to the postgres account).

I'd love to see something like SUDO ALTER USER ... SUDO REINDEX ... etc.
That would make it easy to do 'normal' work with a non-superuser
account.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: Something I don't understand with the use of schemas

From
Frank Wiles
Date:
On Mon, 12 Dec 2005 14:05:03 -0600
"Jim C. Nasby" <jnasby@pervasive.com> wrote:

> I think it would be a huge benefit to have something equivalent to
> sudo for psql (though maybe it could be generalized more). Having to
> change to a different connection/authorization anytime you need to do
> something requiring superuser is a real pita, and encourages things
> like making yourself a superuser.
 Me too.  I think this would be a great feature. 
---------------------------------  Frank Wiles <frank@wiles.org>
http://www.wiles.org---------------------------------



Re: Something I don't understand with the use of schemas

From
Tom Lane
Date:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> I'd love to see something like SUDO ALTER USER ... SUDO REINDEX ... etc.
> That would make it easy to do 'normal' work with a non-superuser
> account.

You can already do most of this with SET/RESET ROLE:

regression=# create user tgl;
CREATE ROLE
regression=# create user admin createrole;
CREATE ROLE
regression=# grant admin to tgl;
GRANT ROLE
regression=# \c - tgl
You are now connected as new user "tgl".
regression=> create user boo;
ERROR:  permission denied to create role
regression=> set role admin;
SET
regression=> create user boo;
CREATE ROLE
regression=> reset role;
RESET
regression=> create user bar;
ERROR:  permission denied to create role
regression=>

        regards, tom lane


Re: Something I don't understand with the use of schemas

From
"Jim C. Nasby"
Date:
On Mon, Dec 12, 2005 at 05:00:45PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > I'd love to see something like SUDO ALTER USER ... SUDO REINDEX ... etc.
> > That would make it easy to do 'normal' work with a non-superuser
> > account.
> 
> You can already do most of this with SET/RESET ROLE:

Very cool, I didn't realize that. It would still be nice if there was a
way to do it on a per-command basis (since often you just need to run
one command as admin/dba/what-have-you), but I suspect adding that to
the grammar would be a real PITA. Perhapse it could be added to psql
though...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: Something I don't understand with the use of schemas

From
Andrew Dunstan
Date:

Jim C. Nasby wrote:

>On Mon, Dec 12, 2005 at 05:00:45PM -0500, Tom Lane wrote:
>  
>
>>"Jim C. Nasby" <jnasby@pervasive.com> writes:
>>    
>>
>>>I'd love to see something like SUDO ALTER USER ... SUDO REINDEX ... etc.
>>>That would make it easy to do 'normal' work with a non-superuser
>>>account.
>>>      
>>>
>>You can already do most of this with SET/RESET ROLE:
>>    
>>
>
>Very cool, I didn't realize that. It would still be nice if there was a
>way to do it on a per-command basis (since often you just need to run
>one command as admin/dba/what-have-you), but I suspect adding that to
>the grammar would be a real PITA. Perhapse it could be added to psql
>though...
>  
>

If it's one command can't you wrap it in a security definer function?

cheers

andrew


Re: Something I don't understand with the use of schemas

From
"Jim C. Nasby"
Date:
On Mon, Dec 12, 2005 at 05:27:33PM -0500, Andrew Dunstan wrote:
> >On Mon, Dec 12, 2005 at 05:00:45PM -0500, Tom Lane wrote:
> > 
> >
> >>"Jim C. Nasby" <jnasby@pervasive.com> writes:
> >>   
> >>
> >>>I'd love to see something like SUDO ALTER USER ... SUDO REINDEX ... etc.
> >>>That would make it easy to do 'normal' work with a non-superuser
> >>>account.
> >>>     
> >>>
> >>You can already do most of this with SET/RESET ROLE:
> >>   
> >>
> >
> >Very cool, I didn't realize that. It would still be nice if there was a
> >way to do it on a per-command basis (since often you just need to run
> >one command as admin/dba/what-have-you), but I suspect adding that to
> >the grammar would be a real PITA. Perhapse it could be added to psql
> >though...
> 
> If it's one command can't you wrap it in a security definer function?

Sure, if it's a command you'll be using over and over. Which I guess
some are, but it's still a pain.

Maybe what I'm asking for will only make sense to people who use sudo...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: Something I don't understand with the use of schemas

From
Rod Taylor
Date:
On Mon, 2005-12-12 at 16:35 -0600, Jim C. Nasby wrote:
> On Mon, Dec 12, 2005 at 05:27:33PM -0500, Andrew Dunstan wrote:
> > >On Mon, Dec 12, 2005 at 05:00:45PM -0500, Tom Lane wrote:
> > > 
> > >
> > >>"Jim C. Nasby" <jnasby@pervasive.com> writes:
> > >>   
> > >>
> > >>>I'd love to see something like SUDO ALTER USER ... SUDO REINDEX ... etc.
> > >>>That would make it easy to do 'normal' work with a non-superuser
> > >>>account.
> > >>>     
> > >>>
> > >>You can already do most of this with SET/RESET ROLE:
> > >>   
> > >>
> > >
> > >Very cool, I didn't realize that. It would still be nice if there was a
> > >way to do it on a per-command basis (since often you just need to run
> > >one command as admin/dba/what-have-you), but I suspect adding that to
> > >the grammar would be a real PITA. Perhapse it could be added to psql
> > >though...
> > 
> > If it's one command can't you wrap it in a security definer function?
> 
> Sure, if it's a command you'll be using over and over. Which I guess
> some are, but it's still a pain.

> Maybe what I'm asking for will only make sense to people who use sudo...

Having a set of fine-grained permissions that you could grant to roles
could be useful.

A sudo equivalent would be a version of psql that always connected to
the database using super-user and allowed command execution based on a
regular expression. Bit of a hack to say the least.
-- 



Re: Something I don't understand with the use of schemas

From
"Jim C. Nasby"
Date:
On Mon, Dec 12, 2005 at 05:43:47PM -0500, Rod Taylor wrote:
> > > >>>I'd love to see something like SUDO ALTER USER ... SUDO REINDEX ... etc.
> > > >>>That would make it easy to do 'normal' work with a non-superuser
> > > >>>account.
> 
> A sudo equivalent would be a version of psql that always connected to
> the database using super-user and allowed command execution based on a
> regular expression. Bit of a hack to say the least.

How is that at all what you're describing?

sudo gives you the ability to run a command as root, plain and simple.
IE:

sudo ls -la blah
sudo /usr/local/etc/rc.d/010.pgsql.sh stop
etc

Some SQL examples would be...

sudo CREATE USER ...
sudo UPDATE table SET ...

I have no idea what you're envisioning, but based on your description it
certainly doesn't sound like what I'm envisioning...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: Something I don't understand with the use of schemas

From
Tom Lane
Date:
Guillaume LELARGE <guillaume.lelarge@gmail.com> writes:
> I've made more tests on schemas. I'm able to drop information_schema and 
> public schemas but I can't drop pg_catalog and pg_toast. It makes me think 
> that only pg_* are system schemas and that public and information_schema are 
> public schemas. Am I right on this one ?

Yeah, both of the latter are intentionally droppable because nothing in
the core server depends on them.  The C code is explicitly aware of both
pg_catalog and pg_toast, so those are pinned.
        regards, tom lane


Re: Something I don't understand with the use of schemas

From
Tom Lane
Date:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Mon, Dec 12, 2005 at 05:43:47PM -0500, Rod Taylor wrote:
>> A sudo equivalent would be a version of psql that always connected to
>> the database using super-user and allowed command execution based on a
>> regular expression. Bit of a hack to say the least.

> How is that at all what you're describing?
> sudo gives you the ability to run a command as root, plain and simple.

Perhaps you should read the sudo documentation sometime ;-).  sudo
allows fairly fine-grained control over who can become which userid
and what commands they can issue.  (At least the Linux version does.)

I'm not sure that a psql sudo would have to have all that, since to
some extent it'd duplicate the existing SQL permissions machinery,
but at the very least it needs to allow specification of the target
userid.  There isn't any universal equivalent to "root" that we could
sensibly default to in Postgres.  So you're really talking aboutsudo postgres create user joe ... ;
versusset role postgres;create user joe ... ;reset role;
which is not *that* amazing a savings in typing, and becomes very
rapidly less so when you need to execute multiple commands as the
more-privileged user.

Implementing sudo on the psql side would be a bit of a PITA, because
of the problem of "how do you reset role if the called command fails
(and thereby aborts your open transaction)?"  On the backend side I
think it could use the same reset mechanism that already exists for
security-definer functions...
        regards, tom lane


Re: Something I don't understand with the use of schemas

From
Guillaume LELARGE
Date:
Le Mardi 13 Décembre 2005 00:13, Tom Lane a écrit :
> Guillaume LELARGE <guillaume.lelarge@gmail.com> writes:
> > I've made more tests on schemas. I'm able to drop information_schema and
> > public schemas but I can't drop pg_catalog and pg_toast. It makes me
> > think that only pg_* are system schemas and that public and
> > information_schema are public schemas. Am I right on this one ?
>
> Yeah, both of the latter are intentionally droppable because nothing in
> the core server depends on them.  The C code is explicitly aware of both
> pg_catalog and pg_toast, so those are pinned.
>

Thanks for your answer.


--
Guillaume.
<!-- http://abs.traduc.org/    http://lfs.traduc.org/    http://traduc.postgresqlfr.org/ -->



Re: Something I don't understand with the use of schemas

From
"Jim C. Nasby"
Date:
On Mon, Dec 12, 2005 at 06:37:03PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > On Mon, Dec 12, 2005 at 05:43:47PM -0500, Rod Taylor wrote:
> >> A sudo equivalent would be a version of psql that always connected to
> >> the database using super-user and allowed command execution based on a
> >> regular expression. Bit of a hack to say the least.
> 
> > How is that at all what you're describing?
> > sudo gives you the ability to run a command as root, plain and simple.
> 
> Perhaps you should read the sudo documentation sometime ;-).  sudo
> allows fairly fine-grained control over who can become which userid
> and what commands they can issue.  (At least the Linux version does.)
> 
> I'm not sure that a psql sudo would have to have all that, since to
> some extent it'd duplicate the existing SQL permissions machinery,

Yeah, that's taking my analogy farther than I intended. :)

> but at the very least it needs to allow specification of the target
> userid.  There isn't any universal equivalent to "root" that we could
> sensibly default to in Postgres.  So you're really talking about

Database owner?

> Implementing sudo on the psql side would be a bit of a PITA, because
> of the problem of "how do you reset role if the called command fails
> (and thereby aborts your open transaction)?"  On the backend side I
> think it could use the same reset mechanism that already exists for
> security-definer functions...

Heh, I figured adding this to the grammar would be a nightmare compared
to anything else; shows what (little) I know. :)
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461