Thread: CREATE USER
Hi, I just notice that when you create a user using CREATE USER and give the user the ability to create other users it gets created as SUPERUSER. CREATE ROLE behaves as expected. Looking at src/backend/parser/gram.y seems it's intentional, for backwards compatibility. But the docs says: """ CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE. """ So, at least, we need to update docs or is undocumented intentionally too? also i really think the command should raise a WARNING so we now what's happening -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
Jaime Casanova <jaime@2ndquadrant.com> writes:
> I just notice that when you create a user using CREATE USER and give
> the user the ability to create other users it gets created as
> SUPERUSER.
Are you talking about the CREATEUSER option? That is documented, quite
clearly I think:
CREATEUSER
NOCREATEUSER
These clauses are an obsolete, but still accepted, spelling of
SUPERUSER and NOSUPERUSER. Note that they are not equivalent to
CREATEROLE as one might naively expect!
The only way we could really make this any better is to remove these
keywords, which might be something to consider. The preferred SUPERUSER
spelling has been accepted since 8.1 ... is that long enough to ensure
everyone's converted their client-side tools? Probably not :-(
regards, tom lane
On Fri, Dec 9, 2011 at 10:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Jaime Casanova <jaime@2ndquadrant.com> writes: >> I just notice that when you create a user using CREATE USER and give >> the user the ability to create other users it gets created as >> SUPERUSER. > > Are you talking about the CREATEUSER option? That is documented, quite > clearly I think: > yes, i was. and it's seems you're right is documented... obviously i was looking at the wrong place > > The only way we could really make this any better is to remove these > keywords, which might be something to consider. The preferred SUPERUSER > spelling has been accepted since 8.1 ... is that long enough to ensure > everyone's converted their client-side tools? Probably not :-( > 6 years since enough for me... actually i have been around even before that and didn't remember that. even if you think is not worth remove these, even a WARNING should be good -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On Fri, Dec 9, 2011 at 8:36 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > On Fri, Dec 9, 2011 at 10:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Jaime Casanova <jaime@2ndquadrant.com> writes: >>> I just notice that when you create a user using CREATE USER and give >>> the user the ability to create other users it gets created as >>> SUPERUSER. >> >> Are you talking about the CREATEUSER option? That is documented, quite >> clearly I think: >> > > yes, i was. and it's seems you're right is documented... obviously i > was looking at the wrong place > >> >> The only way we could really make this any better is to remove these >> keywords, which might be something to consider. The preferred SUPERUSER >> spelling has been accepted since 8.1 ... is that long enough to ensure >> everyone's converted their client-side tools? Probably not :-( >> > > 6 years since enough for me... actually i have been around even before > that and didn't remember that. > even if you think is not worth remove these, even a WARNING should be good Make sure the docs mention this and then remove it in the next major release? Works for me.
Tom Lane wrote: > Jaime Casanova <jaime@2ndquadrant.com> writes: > > I just notice that when you create a user using CREATE USER and give > > the user the ability to create other users it gets created as > > SUPERUSER. > > Are you talking about the CREATEUSER option? That is documented, quite > clearly I think: > > CREATEUSER > NOCREATEUSER > > These clauses are an obsolete, but still accepted, spelling of > SUPERUSER and NOSUPERUSER. Note that they are not equivalent to > CREATEROLE as one might naively expect! > > The only way we could really make this any better is to remove these > keywords, which might be something to consider. The preferred SUPERUSER > spelling has been accepted since 8.1 ... is that long enough to ensure > everyone's converted their client-side tools? Probably not :-( How does this relate to people restoring pre-8.1 pg_dumpall dumps? Seems removing this option would cause the restore to fail. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Tue, Dec 13, 2011 at 4:36 PM, Bruce Momjian <bruce@momjian.us> wrote: > Tom Lane wrote: >> Jaime Casanova <jaime@2ndquadrant.com> writes: >> > I just notice that when you create a user using CREATE USER and give >> > the user the ability to create other users it gets created as >> > SUPERUSER. >> >> Are you talking about the CREATEUSER option? That is documented, quite >> clearly I think: >> >> CREATEUSER >> NOCREATEUSER >> >> These clauses are an obsolete, but still accepted, spelling of >> SUPERUSER and NOSUPERUSER. Note that they are not equivalent to >> CREATEROLE as one might naively expect! >> >> The only way we could really make this any better is to remove these >> keywords, which might be something to consider. The preferred SUPERUSER >> spelling has been accepted since 8.1 ... is that long enough to ensure >> everyone's converted their client-side tools? Probably not :-( > > How does this relate to people restoring pre-8.1 pg_dumpall dumps? > Seems removing this option would cause the restore to fail. True, but 1: that's a really old release, and you should always dump from the version you're going to not, going from, and 2: If you dumped with 8.1 from 8.1 you're likely to have plenty of other issues loading into later versions as well. How about it's marked as deprecated to be removed in the next major release (9.1) and removed the one after that (9.2)?
Scott Marlowe wrote: > On Tue, Dec 13, 2011 at 4:36 PM, Bruce Momjian <bruce@momjian.us> wrote: > > Tom Lane wrote: > >> Jaime Casanova <jaime@2ndquadrant.com> writes: > >> > I just notice that when you create a user using CREATE USER and give > >> > the user the ability to create other users it gets created as > >> > SUPERUSER. > >> > >> Are you talking about the CREATEUSER option? ?That is documented, quite > >> clearly I think: > >> > >> ? ? ? CREATEUSER > >> ? ? ? NOCREATEUSER > >> > >> ? ? ? These clauses are an obsolete, but still accepted, spelling of > >> ? ? ? SUPERUSER and NOSUPERUSER. Note that they are not equivalent to > >> ? ? ? CREATEROLE as one might naively expect! > >> > >> The only way we could really make this any better is to remove these > >> keywords, which might be something to consider. ?The preferred SUPERUSER > >> spelling has been accepted since 8.1 ... is that long enough to ensure > >> everyone's converted their client-side tools? ?Probably not :-( > > > > How does this relate to people restoring pre-8.1 pg_dumpall dumps? > > Seems removing this option would cause the restore to fail. > > True, but 1: that's a really old release, and you should always dump > from the version you're going to not, going from, and 2: If you dumped > with 8.1 from 8.1 you're likely to have plenty of other issues loading > into later versions as well. How about it's marked as deprecated to > be removed in the next major release (9.1) and removed the one after > that (9.2)? Well, I know we support the new pg_dump dumping back to 7.1, but I don't know what our policy is for accepting old dumps. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> Well, I know we support the new pg_dump dumping back to 7.1, but I don't
> know what our policy is for accepting old dumps.
We're still going to very large lengths to load pre-7.3 dumps; see
for instance ConvertTriggerToFK() in trigger.c. I'm not aware of any
policy that would suggest blowing off pre-8.1 dumps should be
acceptable. Yeah, it's true that the *recommended* procedure is to
use the newer pg_dump, but people aren't always in a position to do
that.
I think it might be sane to emit a WARNING suggesting that CREATEUSER
might not mean what you think, but failing is probably not good.
regards, tom lane
On Tue, Dec 13, 2011 at 9:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bruce Momjian <bruce@momjian.us> writes: >> Well, I know we support the new pg_dump dumping back to 7.1, but I don't >> know what our policy is for accepting old dumps. > > We're still going to very large lengths to load pre-7.3 dumps; see > for instance ConvertTriggerToFK() in trigger.c. I'm not aware of any > policy that would suggest blowing off pre-8.1 dumps should be > acceptable. Yeah, it's true that the *recommended* procedure is to > use the newer pg_dump, but people aren't always in a position to do > that. > > I think it might be sane to emit a WARNING suggesting that CREATEUSER > might not mean what you think, but failing is probably not good. I guess there's really no reason to get rid of it, just drop it from that part of the docs, mark is as deprecated?
On Tue, Dec 13, 2011 at 11:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bruce Momjian <bruce@momjian.us> writes: >> Well, I know we support the new pg_dump dumping back to 7.1, but I don't >> know what our policy is for accepting old dumps. > > We're still going to very large lengths to load pre-7.3 dumps; see > for instance ConvertTriggerToFK() in trigger.c. I'm not aware of any > policy that would suggest blowing off pre-8.1 dumps should be > acceptable. Yeah, it's true that the *recommended* procedure is to > use the newer pg_dump, but people aren't always in a position to do > that. > > I think it might be sane to emit a WARNING suggesting that CREATEUSER > might not mean what you think, but failing is probably not good. > are we going to do this in this release? i never was able to think in a good phrasing for this, though -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On Tue, Apr 24, 2012 at 2:55 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > On Tue, Dec 13, 2011 at 11:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Bruce Momjian <bruce@momjian.us> writes: >>> Well, I know we support the new pg_dump dumping back to 7.1, but I don't >>> know what our policy is for accepting old dumps. >> >> We're still going to very large lengths to load pre-7.3 dumps; see >> for instance ConvertTriggerToFK() in trigger.c. I'm not aware of any >> policy that would suggest blowing off pre-8.1 dumps should be >> acceptable. Yeah, it's true that the *recommended* procedure is to >> use the newer pg_dump, but people aren't always in a position to do >> that. >> >> I think it might be sane to emit a WARNING suggesting that CREATEUSER >> might not mean what you think, but failing is probably not good. >> > > are we going to do this in this release? > i never was able to think in a good phrasing for this, though I actually think we should just leave this alone. There is a limitless number of things that someone could potentially be confused by if they fail to read the documentation, and we can't warn about all of them. Now, one thing we could do is add a deprecation warning, stating that CREATEUSER may be removed in a future release, assuming we want to eventually remove it. But I don't think warning people that CREATEUSER means SUPERUSER and not CREATEROLE is very helpful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Wed, May 2, 2012 at 12:09 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Tue, Apr 24, 2012 at 2:55 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: >> On Tue, Dec 13, 2011 at 11:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> >>> I think it might be sane to emit a WARNING suggesting that CREATEUSER >>> might not mean what you think, but failing is probably not good. >>> >> >> are we going to do this in this release? >> i never was able to think in a good phrasing for this, though > > I actually think we should just leave this alone. There is a > limitless number of things that someone could potentially be confused > by if they fail to read the documentation, and we can't warn about all > of them. > maybe is not very helpful, but it can't hurt... hey! it can save you because you maybe used CREATEUSER with the intention of CREATEROLE, and ended up with a user with restricted privileges that is actually a SUPERUSER... that's bad and is a POLA violation. is worse because we are the ones causing the confusion consider the syntax: CREATE USER = CREATE ROLE IN GROUP = IN ROLE USER = ROLE CREATEUSER != CREATEROLE CREATEUSER = SUPERUSER -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación
On Thu, May 3, 2012 at 02:05:49PM -0500, Jaime Casanova wrote: > On Wed, May 2, 2012 at 12:09 PM, Robert Haas <robertmhaas@gmail.com> wrote: > > On Tue, Apr 24, 2012 at 2:55 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > >> On Tue, Dec 13, 2011 at 11:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >>> > >>> I think it might be sane to emit a WARNING suggesting that CREATEUSER > >>> might not mean what you think, but failing is probably not good. > >>> > >> > >> are we going to do this in this release? > >> i never was able to think in a good phrasing for this, though > > > > I actually think we should just leave this alone. There is a > > limitless number of things that someone could potentially be confused > > by if they fail to read the documentation, and we can't warn about all > > of them. > > > > maybe is not very helpful, but it can't hurt... hey! it can save you > because you maybe used CREATEUSER with the intention of CREATEROLE, > and ended up with a user with restricted privileges that is actually a > SUPERUSER... that's bad and is a POLA violation. > > is worse because we are the ones causing the confusion consider the syntax: > CREATE USER = CREATE ROLE > IN GROUP = IN ROLE > USER = ROLE > > CREATEUSER != CREATEROLE > CREATEUSER = SUPERUSER I looked at this and can't see a way to make CREATEUSER != CREATEROLE clearer: The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +