Thread: Something I don't understand with the use of schemas
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/ -->
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
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
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
> > 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
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
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.
>> >> 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/
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.
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
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/ -->
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
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---------------------------------
"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
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
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
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
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. --
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
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
"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
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/ -->
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