Thread: CREATE USER and pg_user
"create user foo with createdb" will create a user with createdb privilege. "create user bar with createuser" will create s superuser who can createdb, createuser, and update system catalog. Why not change the option "createuser" to "superuser", or do something to make is easier to understand? -- Best regards, William ZHANG
On Fri, Aug 12, 2005 at 18:11:54 +0800, William ZHANG <uniware@zedware.org> wrote: > "create user foo with createdb" will create a user with createdb privilege. > "create user bar with createuser" will create s superuser who can createdb, > createuser, and update system catalog. > > Why not change the option "createuser" to "superuser", or do something > to make is easier to understand? Currently being able to create users is effectively the same as having superuser privileges, because if you weren't already a superuser, you could just create one to get that access. Recently there was some discussion about having a createuser ability that only allowed one to create nonsuperuser accounts. From the development docs it looks like this is going to be in 8.1. However it looks like CREATEUSER privilege will continue to be an alias for super user access. I didn't notice a documentation update for the createuser program and don't know if it will take the new keywords or not. For more information take a look at the CREATE ROLE command in the developer docs.
"William ZHANG" <uniware@zedware.org> writes: > Why not change the option "createuser" to "superuser", Backwards compatibility with existing dump files. > or do something to make is easier to understand? See http://developer.postgresql.org/docs/postgres/sql-createrole.html SUPERUSER NOSUPERUSER These clauses determine whether the new role is a "superuser", who can override all access restrictions within the database. Superuser status is dangerous and should be used only when really needed. You must yourself be a superuserto create a new superuser. If not specified, NOSUPERUSER is the default. ... CREATEUSER NOCREATEUSER These clauses are an obsolete, but still accepted, spelling of SUPERUSER and NOSUPERUSER. Note that they are not equivalentto CREATEROLE as one might naively expect! regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:7731.1123857710@sss.pgh.pa.us... > "William ZHANG" <uniware@zedware.org> writes: > > Why not change the option "createuser" to "superuser", > > Backwards compatibility with existing dump files. > > > or do something to make is easier to understand? > > See > http://developer.postgresql.org/docs/postgres/sql-createrole.html > Got the idea. And I found that pgsql's role is very intresting. Will read it carefully.
On Fri, Aug 12, 2005 at 08:55:09AM -0500, Bruno Wolff III wrote: > On Fri, Aug 12, 2005 at 18:11:54 +0800, > William ZHANG <uniware@zedware.org> wrote: > > "create user foo with createdb" will create a user with createdb privilege. > > "create user bar with createuser" will create s superuser who can createdb, > > createuser, and update system catalog. > > > > Why not change the option "createuser" to "superuser", or do something > > to make is easier to understand? > > Currently being able to create users is effectively the same as having > superuser privileges, because if you weren't already a superuser, you > could just create one to get that access. > Recently there was some discussion about having a createuser ability that > only allowed one to create nonsuperuser accounts. From the development > docs it looks like this is going to be in 8.1. However it looks like > CREATEUSER privilege will continue to be an alias for super user access. > I didn't notice a documentation update for the createuser program and > don't know if it will take the new keywords or not. > For more information take a look at the CREATE ROLE command in the > developer docs. ISTM that it's a bug to be able to assign permissions that you don't yourself have. In this case, if you have CREATEROLE but not SUPERUSER, then you should be able to create roles, but not ones that have SUPERUSER status. If this isn't how it currently works then there should be a big warning under CREATEROLE. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes: > On Fri, Aug 12, 2005 at 08:55:09AM -0500, Bruno Wolff III wrote: >> For more information take a look at the CREATE ROLE command in the >> developer docs. > ISTM that it's a bug to be able to assign permissions that you don't > yourself have. In this case, if you have CREATEROLE but not SUPERUSER, > then you should be able to create roles, but not ones that have > SUPERUSER status. If this isn't how it currently works then there should > be a big warning under CREATEROLE. Did you read the docs Bruno pointed you to? http://developer.postgresql.org/docs/postgres/sql-createrole.html regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Monday, August 22, 2005 3:18 PM > To: Jim Nasby > Cc: Bruno Wolff III; William ZHANG; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] CREATE USER and pg_user > > > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > On Fri, Aug 12, 2005 at 08:55:09AM -0500, Bruno Wolff III wrote: > >> For more information take a look at the CREATE ROLE command in the > >> developer docs. > > > ISTM that it's a bug to be able to assign permissions that you don't > > yourself have. In this case, if you have CREATEROLE but not > SUPERUSER, > > then you should be able to create roles, but not ones that have > > SUPERUSER status. If this isn't how it currently works then > there should > > be a big warning under CREATEROLE. > > Did you read the docs Bruno pointed you to? > > http://developer.postgresql.org/docs/postgres/sql-createrole.html > > regards, tom lane Yes, but it doesn't really specify if you have to have a privilege in order to grant it, although reading one of the notes[1]tends to indicate that you must have a role in order to grant it. Unless I'm overlooking some part of the docs? [1]: "The INHERIT attribute governs inheritance of grantable privileges (that is, access privileges for database objectsand role memberships). It does not apply to the special role attributes set by CREATE ROLE and ALTER ROLE. For example,being a member of a role with CREATEDB privilege does not immediately grant the ability to create databases, evenif INHERIT is set; it would be necessary to become that role via SET ROLE before creating a database." -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
"Jim Nasby" <jnasby@pervasive.com> writes:
> Yes, but it doesn't really specify if you have to have a privilege in order to grant it, although reading one of the
notes[1]tends to indicate that you must have a role in order to grant it. Unless I'm overlooking some part of the
docs?
It says
You must yourself be a superuser to create a new superuser.
regards, tom lane
On Mon, Aug 22, 2005 at 09:19:46PM -0400, Tom Lane wrote: > "Jim Nasby" <jnasby@pervasive.com> writes: > > Yes, but it doesn't really specify if you have to have a privilege in order to grant it, although reading one of thenotes[1] tends to indicate that you must have a role in order to grant it. Unless I'm overlooking some part of the docs? > > It says > > You must yourself be a superuser to create a new superuser. Sorry, I guess we're talking past each other. My original point was that if you don't have permission to do something, you shouldn't be able to grant permissions to do it. This applies to all the permissions, not just superuser (though that one's obviously the most dangerous). Granted, at this point I think the only permission this would really matter on (other than SUPERUSER/CREATEUSER) is CREATEDB, but that will probably change if more privleges are added. It seems we should set the standard now that if you don't have a permission you can't grant it, rather than wait 'til later. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> My original point was that if you don't have permission to do something,
> you shouldn't be able to grant permissions to do it. This applies to all
> the permissions, not just superuser (though that one's obviously the
> most dangerous). Granted, at this point I think the only permission this
> would really matter on (other than SUPERUSER/CREATEUSER) is CREATEDB,
> but that will probably change if more privleges are added. It seems we
> should set the standard now that if you don't have a permission you
> can't grant it, rather than wait 'til later.
I'm not convinced. It seems reasonable to say that you can't grant
CREATEDB if you don't have it, but that's only because it's hard to
imagine why anyone would make a role that's CREATEROLE and not CREATEDB.
But, for example, we allow a CREATEROLE role to grant and revoke role
memberships without itself being an admin of those roles.
Basically the point of CREATEROLE is to be a safer kind of superuser:
you can do what you like with respect to creating and dropping and
altering users and groups (other than superusers), but you don't have
permission to, say, "DELETE FROM pg_proc". If we restrict CREATEROLE
to not have any privileges that an ordinary user wouldn't have except
the ability to create users, then we'll be back at square one in the
sense that lots of common administrative situations will require
superuser privilege.
It's quite likely that we don't have CREATEROLE fully done yet, and
that it still needs some more tweaks that we haven't thought of.
But I don't think I buy the premise that it ought to be confined to
creating users with no other special privileges. If we do that,
we'll just have to re-invent the limited-superuser facility under
a different name, because there's a serious need for that.
regards, tom lane
""Jim Nasby"" <jnasby@pervasive.com> write > Yes, but it doesn't really specify if you have to have a privilege in > order to grant it, although reading one of the notes[1] tends to indicate > that you must have a role in order to grant it. Unless I'm overlooking > some part of the docs? I am confused by the combination of USER and ROLE in 8.1 at first glance. In my memory, USER is a representation of somebody using the database, just as USER in OS. USER can also always login into the system, except the Admin prohibit it. ROLE is a collection of some priviledges. A USER can act as multiple ROLEs, but in real life, it cannot act as multiple ROLEs are the same time. That's why some system define that a USER can only act as one ROLE at a moment, but she can change her ROLE with "SET SESSION ROLE TO <anotherRole>". Maybe I will read more about SQL:2003, pgsql-8.1 doc and RBAC (Role Based Access Control, http://csrc.nist.gov/rbac/rbac-stds-roadmap.html) to understand it correctly. Regards, William ZHANG
On Mon, Aug 22, 2005 at 10:42:15PM -0400, Tom Lane wrote: > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > My original point was that if you don't have permission to do something, > > you shouldn't be able to grant permissions to do it. This applies to all <snip> > I'm not convinced. It seems reasonable to say that you can't grant > CREATEDB if you don't have it, but that's only because it's hard to > imagine why anyone would make a role that's CREATEROLE and not CREATEDB. I don't think it's unreasonable that an admin would want to allow someone to be able to grant permissions to others, but not create databases. Regardless, the real issue comes in when more privleges are added in the future. We can either cross that bridge when we come to it or we can look at it now. My argument is that we should look at it now. > But, for example, we allow a CREATEROLE role to grant and revoke role > memberships without itself being an admin of those roles. Which is arguably not good... > Basically the point of CREATEROLE is to be a safer kind of superuser: > you can do what you like with respect to creating and dropping and > altering users and groups (other than superusers), but you don't have > permission to, say, "DELETE FROM pg_proc". If we restrict CREATEROLE > to not have any privileges that an ordinary user wouldn't have except > the ability to create users, then we'll be back at square one in the > sense that lots of common administrative situations will require > superuser privilege. I definately don't think we should restrict CREATEROLE to the point where it's just another user, but I also don't think it should be given full reign, either. To an extent we've already addressed that with the special condition of SUPERUSER. My point is that instead of treating SUPERUSER as a special case, why not just restrict all privileges the same way? An alternative would be a second set of privileges that determine what privileges a role can assign, but that seems like overkill and a potential foot-gun. > It's quite likely that we don't have CREATEROLE fully done yet, and > that it still needs some more tweaks that we haven't thought of. > But I don't think I buy the premise that it ought to be confined to > creating users with no other special privileges. If we do that, > we'll just have to re-invent the limited-superuser facility under > a different name, because there's a serious need for that. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
On Tue, Aug 23, 2005 at 09:26:07AM +0800, William ZHANG wrote: > ""Jim Nasby"" <jnasby@pervasive.com> write > > Yes, but it doesn't really specify if you have to have a privilege in > > order to grant it, although reading one of the notes[1] tends to indicate > > that you must have a role in order to grant it. Unless I'm overlooking > > some part of the docs? > > I am confused by the combination of USER and ROLE in 8.1 at first glance. > In my memory, USER is a representation of somebody using the database, > just as USER in OS. USER can also always login into the system, except > the Admin prohibit it. ROLE is a collection of some priviledges. A USER > can act as multiple ROLEs, but in real life, it cannot act as multiple ROLEs > are the same time. That's why some system define that a USER can only > act as one ROLE at a moment, but she can change her ROLE with > "SET SESSION ROLE TO <anotherRole>". > > Maybe I will read more about SQL:2003, pgsql-8.1 doc and RBAC > (Role Based Access Control, > http://csrc.nist.gov/rbac/rbac-stds-roadmap.html) > to understand it correctly. You should take a look at http://lnk.nu/developer.postgresql.org/3mi.html, both 17.1 and 17.2. In particular: "CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not." -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
----- Original Message ----- From: "Jim C. Nasby" <jnasby@pervasive.com> To: "William ZHANG" <uniware@zedware.org> Cc: <pgsql-hackers@postgresql.org> Sent: Wednesday, August 24, 2005 3:27 AM Subject: Re: [HACKERS] CREATE USER and pg_user <skipped> > You should take a look at > http://lnk.nu/developer.postgresql.org/3mi.html, both 17.1 and 17.2. In > particular: > > "CREATE USER is equivalent to CREATE ROLE except that CREATE USER > assumes LOGIN by default, while CREATE ROLE does not." Thanks a lot. Already read it now and know sth. about pgsql's roles. > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com 512-569-9461