CREATE USER and pg_user

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
superuser to 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 equivalent to 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]"The INHERIT attribute governs inheritance of grantable privileges (that is, access privileges for database objects and 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, even if 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 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 objects and 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, even if 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 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

""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 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 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.

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

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.