Thread: CREATE USER and pg_user

CREATE USER and pg_user

From
"William ZHANG"
Date:
"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




Re: CREATE USER and pg_user

From
Bruno Wolff III
Date:
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.


Re: CREATE USER and pg_user

From
Tom Lane
Date:
"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


Re: CREATE USER and pg_user

From
"William ZHANG"
Date:
"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.




Re: CREATE USER and pg_user

From
"Jim C. Nasby"
Date:
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


Re: CREATE USER and pg_user

From
Tom Lane
Date:
"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


Re: CREATE USER and pg_user

From
"Jim Nasby"
Date:
> -----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


Re: CREATE USER and pg_user

From
Tom Lane
Date:
"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


Re: CREATE USER and pg_user

From
"Jim C. Nasby"
Date:
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


Re: CREATE USER and pg_user

From
Tom Lane
Date:
"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


Re: CREATE USER and pg_user

From
"William ZHANG"
Date:
""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




Re: CREATE USER and pg_user

From
"Jim C. Nasby"
Date:
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


Re: CREATE USER and pg_user

From
"Jim C. Nasby"
Date:
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


Re: CREATE USER and pg_user

From
"William ZHANG"
Date:
----- 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