Superowners
So I was thinking about various annoying admin/security issues
recently, so I came up with this: a new type of user called a
“superowner”. It’s somewhere between a superuser and a normal user.
Superowner would own all objects defined by users, so it would do
useful things in contexts where superuser is not available.
It seems easiest to make this a property at database level. There is no default.
ALTER DATABASE name
SUPEROWNER TO { new_owner | CURRENT_USER | SESSION_USER }
CREATE DATABASE name SUPEROWNER [=] new_owner
Changes to various commands highlighted by ** **, in existing text from docs.
Changes to GRANT - Roles having CREATEROLE privilege can grant or
revoke membership in any role that is not a superuser **or
superowner**.
It should be noted that database superusers can access all objects
regardless of object privilege settings. This is comparable to the
rights of root in a Unix system. As with root, it's unwise to operate
as a superuser except when absolutely necessary.
**Superowners for a database can access any table, view, materialized
view, index or sequence in that database regardless of object
privilege settings. Superowners cannot create untrusted procedures,
administer catalog tables or schemas or perform internal operations
reserved only for superusers .**
If a superuser **or superowner** chooses to issue a GRANT or REVOKE
command, the command is performed as though it were issued by the
owner of the affected object. In particular, privileges granted via
such a command will appear to have been granted by the object owner.
Changes to ALTER ROLE - Superusers can change anyone's session
defaults. Roles having CREATEROLE privilege can change defaults for
non-superuser roles. **Superowners of a database can change defaults
for that role on their database only.** Ordinary roles can only set
defaults for themselves. Certain configuration variables cannot be set
this way, or can only be set if a superuser issues the command. Only
superusers can change a setting for all roles in all databases.
Any issues, additions or changes?
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Simon Riggs <simon@2ndquadrant.com> writes:
So I was thinking about various annoying admin/security issues
recently, so I came up with this: a new type of user called a
“superowner”. It’s somewhere between a superuser and a normal user.
Superowner would own all objects defined by users, so it would do
useful things in contexts where superuser is not available.
What about just saying that the database owner has those privileges?
After all, the ultimate privilege of an owner is to drop the object
(and then remake it as she pleases), and the DB owner has that option
w.r.t. the whole database. So I'm not sure we need to invent a new
concept.
With or without it being a separate property, there's a point I think
you missed: this should only extend to objects owned by normal users,
not by superusers. Otherwise there are all sorts of security issues.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 24 January 2017 at 13:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Simon Riggs <simon@2ndquadrant.com> writes:
So I was thinking about various annoying admin/security issues
recently, so I came up with this: a new type of user called a
“superowner”. It’s somewhere between a superuser and a normal user.
Superowner would own all objects defined by users, so it would do
useful things in contexts where superuser is not available.What about just saying that the database owner has those privileges?
After all, the ultimate privilege of an owner is to drop the object
(and then remake it as she pleases), and the DB owner has that option
w.r.t. the whole database. So I'm not sure we need to invent a new
concept.
Thinking about it, I've not seen dbowner != superuser in most cases,
so that works for me.
I guess I was expecting push back from people for backwards
compatibility, but it is 10.0
With or without it being a separate property, there's a point I think
you missed: this should only extend to objects owned by normal users,
not by superusers. Otherwise there are all sorts of security issues.
Sure. That sounds like the right definition of what I was trying to specify.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Simon,
* Simon Riggs (simon@2ndquadrant.com) wrote:
So I was thinking about various annoying admin/security issues
recently, so I came up with this: a new type of user called a
“superowner”. It’s somewhere between a superuser and a normal user.
I like the general idea, but I'm not really thrilled with the name (too
easy to confuse with superuser and people might, reasonably, assume
'superowner' can do more than 'superuser'), nor the proposed grammer-
why have only one superowner? How does role membership play into this?
Superowner would own all objects defined by users, so it would do
useful things in contexts where superuser is not available.
I also don't really like the idea of tossing out ownership-level rights
for regular users. I would *much* rather have two independent concepts:
- role attribute which makes a user be treated as if they own all
normal-user-owned database objects (or perhaps as if they are a member
of every role, which people have asked me for multiple times).
- Managmenet of object ownership; as in, have a way to set a DEFAULT
OWNER, or perhaps a FORCED OWNER. Setting DEFAULT OWNER would have
the same requirments that ALTER TABLE ... OWNER TO requires today and
we would re-check those rights at every object creation and error if
we are unable to set the ownership requested (alternatively, we could
allow it and just throw a warning, similar to what GRANT does when you
try to GRANT something and GRANT doesn't end up actually doing
anything).
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Simon Riggs <simon@2ndquadrant.com> writes:
So I was thinking about various annoying admin/security issues
recently, so I came up with this: a new type of user called a
“superowner”. It’s somewhere between a superuser and a normal user.
Superowner would own all objects defined by users, so it would do
useful things in contexts where superuser is not available.What about just saying that the database owner has those privileges?
I'm not thrilled with suddenly changing how a database owner works,
though I'll admit that *new* users tend to assume things work more in
this way.
Thinking through it a bit more though, I can see why Simon is proposing
that it be database-specific. That would seem to imply that what's
needed is a per-database set of role attributes, ala pg_db_authid (not a
great name, but the name can come later). Generally speaking, I like
that idea as I can see other possible use-cases, in particular the
question continues to come up about having a read-only user and being
able to set that for a given role in a given database would be great
(though I think it would also be nice to have one cluster-wide, but that
goes for Simon's proposed feature here too, imv).
After all, the ultimate privilege of an owner is to drop the object
(and then remake it as she pleases), and the DB owner has that option
w.r.t. the whole database. So I'm not sure we need to invent a new
concept.
I can see some overlap with that.
With or without it being a separate property, there's a point I think
you missed: this should only extend to objects owned by normal users,
not by superusers. Otherwise there are all sorts of security issues.
Agreed.
Thanks!
Stephen
Import Notes
Reply to msg id not found: 12791.1485263983@sss.pgh.pa.usCANP8+jKuoxD6aFmXCyGuyWR6U_mGiXzoXqUAnLTMayV0-OTxg@mail.gmail.com | Resolved by subject fallback
On 1/24/17 8:19 AM, Tom Lane wrote:
What about just saying that the database owner has those privileges?
After all, the ultimate privilege of an owner is to drop the object
(and then remake it as she pleases), and the DB owner has that option
w.r.t. the whole database. So I'm not sure we need to invent a new
concept.
A database owner does not necessarily have the permission to create a
new database.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 26, 2017 at 12:37:44PM -0500, Peter Eisentraut wrote:
On 1/24/17 8:19 AM, Tom Lane wrote:
What about just saying that the database owner has those privileges?
After all, the ultimate privilege of an owner is to drop the object
(and then remake it as she pleases), and the DB owner has that option
w.r.t. the whole database. So I'm not sure we need to invent a new
concept.A database owner does not necessarily have the permission to create a
new database.
Right.
Would a "TRUNCATE <database>;" (i.e. empty the database, but don't
delete it) make sense/be useful for that maybe?
Michael
--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de
credativ GmbH, HRB M�nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, J�rg Folz, Sascha Heuer
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thursday, January 26, 2017, Michael Banck <michael.banck@credativ.de>
wrote:
On Thu, Jan 26, 2017 at 12:37:44PM -0500, Peter Eisentraut wrote:
On 1/24/17 8:19 AM, Tom Lane wrote:
What about just saying that the database owner has those privileges?
After all, the ultimate privilege of an owner is to drop the object
(and then remake it as she pleases), and the DB owner has that option
w.r.t. the whole database. So I'm not sure we need to invent a new
concept.A database owner does not necessarily have the permission to create a
new database.Right.
Would a "TRUNCATE <database>;" (i.e. empty the database, but don't
delete it) make sense/be useful for that maybe?
Conceptually we might already have that.
https://www.postgresql.org/docs/current/static/sql-drop-owned.html
Drop Owned ...
Though I suspect there might be caveats since indications are it wasn't
designed with this particular use case in mind,
David J.
On 26 January 2017 at 17:37, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
On 1/24/17 8:19 AM, Tom Lane wrote:
What about just saying that the database owner has those privileges?
After all, the ultimate privilege of an owner is to drop the object
(and then remake it as she pleases), and the DB owner has that option
w.r.t. the whole database. So I'm not sure we need to invent a new
concept.A database owner does not necessarily have the permission to create a
new database.
So the concept I was looking for is already there: on pg_database the
database owner is referred to as the DBA.
I'd like to be able to give permision to someone to control all
user-owned objects in the database, yet not be allowed to do anything
that touches filesystem or creates potentially unsafe functions.
This allows a separation of duty between people that run a service and
people that use it.
That should include the ability to dump all objects, yet without any
security details. And it should allow someone to setup logical
replication easily, including both trigger based and new logical
replication. And GRANT ON ALL should work.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1/26/17 1:25 PM, Simon Riggs wrote:
That should include the ability to dump all objects, yet without any
security details. And it should allow someone to setup logical
replication easily, including both trigger based and new logical
replication. And GRANT ON ALL should work.
This basically sounds like a GRANT $privilege ON ALL $objecttype TO
$user. So you could have a user that can read everything, for example.
This kind of thing has been asked for many times, but that quieted down
when the default privileges feature appeared. I think it would still be
useful.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
On 1/26/17 1:25 PM, Simon Riggs wrote:
That should include the ability to dump all objects, yet without any
security details. And it should allow someone to setup logical
replication easily, including both trigger based and new logical
replication. And GRANT ON ALL should work.This basically sounds like a GRANT $privilege ON ALL $objecttype TO
$user. So you could have a user that can read everything, for example.This kind of thing has been asked for many times, but that quieted down
when the default privileges feature appeared. I think it would still be
useful.
Agreed. I would think we'd either do this with a default role or a role
attribute.
Thanks!
Stephen
On 1/29/17 4:44 PM, Stephen Frost wrote:
* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
On 1/26/17 1:25 PM, Simon Riggs wrote:
That should include the ability to dump all objects, yet without any
security details. And it should allow someone to setup logical
replication easily, including both trigger based and new logical
replication. And GRANT ON ALL should work.This basically sounds like a GRANT $privilege ON ALL $objecttype TO
$user. So you could have a user that can read everything, for example.This kind of thing has been asked for many times, but that quieted down
when the default privileges feature appeared. I think it would still be
useful.Agreed. I would think we'd either do this with a default role or a role
attribute.
Someone was asking for that on Slack the other day, because their
customer wanted it. Default privs would not fit the bill: they wanted to
grant specific roles the ability to read everything in the database (or
maybe cluster; I don't think the conversation got into that level of
detail).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Jim,
* Jim Nasby (Jim.Nasby@BlueTreble.com) wrote:
On 1/29/17 4:44 PM, Stephen Frost wrote:
* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
On 1/26/17 1:25 PM, Simon Riggs wrote:
That should include the ability to dump all objects, yet without any
security details. And it should allow someone to setup logical
replication easily, including both trigger based and new logical
replication. And GRANT ON ALL should work.This basically sounds like a GRANT $privilege ON ALL $objecttype TO
$user. So you could have a user that can read everything, for example.This kind of thing has been asked for many times, but that quieted down
when the default privileges feature appeared. I think it would still be
useful.Agreed. I would think we'd either do this with a default role or a role
attribute.Someone was asking for that on Slack the other day, because their
customer wanted it. Default privs would not fit the bill: they
wanted to grant specific roles the ability to read everything in the
database (or maybe cluster; I don't think the conversation got into
that level of detail).
... eh? If we create a default role called "pg_read_only" which admins
can grant to whomever they wish, how does that not "fit the bill"?
For my 2c, at least, evaluating the various requests and coming up with
some set of default roles and then implementing them would be a good
GSoC project..
Thanks!
Stephen
On 27 January 2017 at 22:48, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
On 1/26/17 1:25 PM, Simon Riggs wrote:
That should include the ability to dump all objects, yet without any
security details. And it should allow someone to setup logical
replication easily, including both trigger based and new logical
replication. And GRANT ON ALL should work.This basically sounds like a GRANT $privilege ON ALL $objecttype TO
$user. So you could have a user that can read everything, for example.This kind of thing has been asked for many times, but that quieted down
when the default privileges feature appeared. I think it would still be
useful.
I would call these "super privileges".
Peter suggests that we have a much more flexible structure for super-privileges.
In Peter's model, Tom's suggestion woud be to grant all of these
automatically to database owners.
GRANT ALL ON ALL TABLES TO $user
GRANT ALL ON ALL SEQUENCES TO $user
GRANT ALL ON ALL FUNCTIONS TO $user
Either of them would be good for me, as long as we implement the rule
as Tom suggests that this would never apply to objects owned by a
superuser.
After thinking about this, the things I care about are
* That super-privileges can be executed efficiently at run-time. If
they cause major overheads, especially in cases where they have not
been granted then we would have a big problem.
* Some super-privileges should exist by default. If super-privileges
exist only when granted by superusers and we have no access to
superusers then they never will exist, so they will be mostly useless
in many cases.
* This model is needed now, or at least change in this area will be
easier because of PG10.0
Neither of those things make me think deferring this and allowing a
student to work on it in the future would be a useful outcome. The
task looks to be a small amount of dev, a medium amount of design and
a lot of checking and documentation.
So my proposal is to implement what Tom suggests now, which I will now
describe as automatic super-privileges for database owning roles. And
we can implement a more flexible scheme for explicit GRANTs to other
roles in later releases.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Simon,
* Simon Riggs (simon@2ndquadrant.com) wrote:
On 27 January 2017 at 22:48, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:On 1/26/17 1:25 PM, Simon Riggs wrote:
That should include the ability to dump all objects, yet without any
security details. And it should allow someone to setup logical
replication easily, including both trigger based and new logical
replication. And GRANT ON ALL should work.This basically sounds like a GRANT $privilege ON ALL $objecttype TO
$user. So you could have a user that can read everything, for example.This kind of thing has been asked for many times, but that quieted down
when the default privileges feature appeared. I think it would still be
useful.I would call these "super privileges".
Peter suggests that we have a much more flexible structure for super-privileges.
In Peter's model, Tom's suggestion woud be to grant all of these
automatically to database owners.
GRANT ALL ON ALL TABLES TO $user
GRANT ALL ON ALL SEQUENCES TO $user
GRANT ALL ON ALL FUNCTIONS TO $user
We need to make sure that we're actually talking about the same things
here, because we've now shifted from ownership-like privileges to those
privielges which can be GRANT'd, and the two are far from the same.
The above model of GRANT ALL ON ALL TABLES TO $user; would *not*, for
example, GRANT the right to drop the object, or to ALTER it, as those
are not currently GRANT'able privileges.
Either of them would be good for me, as long as we implement the rule
as Tom suggests that this would never apply to objects owned by a
superuser.
Things are not quite so simple as this- part of the point of a read-only
role would be to allow that role to perform pg_dump-based backups. If
the above GRANT system does not apply to catalog tables, then trying to
create such a user by using:
GRANT SELECT ON ALL TABLES TO readonly;
wouldn't cover pg_authid and pg_dumpall would fail.
Of course, pg_authid is a shared catalog and we're talking about
per-database things here, for the most part, so perhaps that's not quite
the same, but what about extensions which are installed by a superuser
which include config tables that need to be dumped out?
After thinking about this, the things I care about are
* That super-privileges can be executed efficiently at run-time. If
they cause major overheads, especially in cases where they have not
been granted then we would have a big problem.
This seems to play into a question regarding the implementation which
then leads into user expectations a bit, I believe.
Would these super-privileges be seen when doing a \dp on a table? Are
we going to record the super-privileges into every object which exists
when the command is called and then also into new objects when they're
created? Or are we going to have an additional catalog lookup for every
privilege check?
At first blush, I tend to think the answers are:
- They exist as an independent catalog
- Yes, super-privs are shown in \dp, but through psql doing the
necessary lookups/joins to combine the ACL info.
- We check normal privileges first and fall back to looking for
super-privileges if the caller doesn't have any non-super privileges
on the table. This would hopefully minimize the impact of having
these additional checks, at least on existing systems.
* Some super-privileges should exist by default. If super-privileges
exist only when granted by superusers and we have no access to
superusers then they never will exist, so they will be mostly useless
in many cases.
We should at least consider if we really want upgrades to automatically
set up these super-privileges or not.
* This model is needed now, or at least change in this area will be
easier because of PG10.0
This doesn't strike me as a terribly good argument. I'm sure we could
do this for PG11 too.
Neither of those things make me think deferring this and allowing a
student to work on it in the future would be a useful outcome. The
task looks to be a small amount of dev, a medium amount of design and
a lot of checking and documentation.
For my 2c, at least, that makes it sound like a very good project for a
student to work on. That said, I'm not against doing it now, if someone
wants to spend the time to do so.
So my proposal is to implement what Tom suggests now, which I will now
describe as automatic super-privileges for database owning roles. And
we can implement a more flexible scheme for explicit GRANTs to other
roles in later releases.
I don't believe doing something quick now will lead us to a place where
we will easily be able to make it more flexible later. I'm much more
concerned that we'll end up painting ourselves into a corner that's
difficult to get out of as privileges are, generally speaking, not
something we can really whack around from release to release. They're
extremely user-visible and any issues will almost certainly lead to
security releases.
Thanks!
Stephen
On 30 January 2017 at 14:43, Stephen Frost <sfrost@snowman.net> wrote:
We need to make sure that we're actually talking about the same things
here, because we've now shifted from ownership-like privileges to those
privielges which can be GRANT'd, and the two are far from the same.
Agreed. Let me reiterate: all I want in this release is
super-ownership. Other stuff is way more complex and if it happens at
all it can happen in later release.
Would these super-privileges be seen when doing a \dp on a table?
No, keep it simple, treat like ownership
Are
we going to record the super-privileges into every object which exists
when the command is called and then also into new objects when they're
created?
No. Super privs are implicit and do not need to be added to each new object.
Or are we going to have an additional catalog lookup for every
privilege check?
No, my vision is to replace the superuser() test with a
has_super_privs() check, so the cost is identical to existing
overhead.
I envisage a very small patch. I'll write it so we can see, not in
time for Brussels though.
* Some super-privileges should exist by default. If super-privileges
exist only when granted by superusers and we have no access to
superusers then they never will exist, so they will be mostly useless
in many cases.We should at least consider if we really want upgrades to automatically
set up these super-privileges or not.
This wouldn't be needed if the database owner has the super-privs automatically.
* This model is needed now, or at least change in this area will be
easier because of PG10.0This doesn't strike me as a terribly good argument. I'm sure we could
do this for PG11 too.Neither of those things make me think deferring this and allowing a
student to work on it in the future would be a useful outcome. The
task looks to be a small amount of dev, a medium amount of design and
a lot of checking and documentation.For my 2c, at least, that makes it sound like a very good project for a
student to work on. That said, I'm not against doing it now, if someone
wants to spend the time to do so.So my proposal is to implement what Tom suggests now, which I will now
describe as automatic super-privileges for database owning roles. And
we can implement a more flexible scheme for explicit GRANTs to other
roles in later releases.I don't believe doing something quick now will lead us to a place where
we will easily be able to make it more flexible later. I'm much more
concerned that we'll end up painting ourselves into a corner that's
difficult to get out of as privileges are, generally speaking, not
something we can really whack around from release to release. They're
extremely user-visible and any issues will almost certainly lead to
security releases.
Which is why we should keep it simple, which is why/what I originally proposed.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jan 27, 2017 at 05:48:46PM -0500, Peter Eisentraut wrote:
On 1/26/17 1:25 PM, Simon Riggs wrote:
That should include the ability to dump all objects, yet without
any security details. And it should allow someone to setup logical
replication easily, including both trigger based and new logical
replication. And GRANT ON ALL should work.This basically sounds like a GRANT $privilege ON ALL $objecttype TO
$user. So you could have a user that can read everything, for
example.
This would still be tremendously useful.
So would a cogent, user-facing explanation of DCL in one obvious spot,
which I guess I've signed up to do now that I'm complaining about its
lack.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Simon,
* Simon Riggs (simon@2ndquadrant.com) wrote:
On 30 January 2017 at 14:43, Stephen Frost <sfrost@snowman.net> wrote:
We need to make sure that we're actually talking about the same things
here, because we've now shifted from ownership-like privileges to those
privielges which can be GRANT'd, and the two are far from the same.Agreed. Let me reiterate: all I want in this release is
super-ownership. Other stuff is way more complex and if it happens at
all it can happen in later release.
Alright.
Would these super-privileges be seen when doing a \dp on a table?
No, keep it simple, treat like ownership
Well, we do list ownership in \d.
Are
we going to record the super-privileges into every object which exists
when the command is called and then also into new objects when they're
created?No. Super privs are implicit and do not need to be added to each new object.
Alright, that then involves addiitonal checks happening run-time. I'm
not against that, simply pointing it out.
Or are we going to have an additional catalog lookup for every
privilege check?No, my vision is to replace the superuser() test with a
has_super_privs() check, so the cost is identical to existing
overhead.
It's unclear to me why you're suggesting the cost would be identical.
We check superuser() first and any change to that check to do something
additional (like, look up who the database owner is) would mean an
increase in overhead.
I'm not saying that such overhead would be an issue, to be clear, but
it would be there.
That would also be a change from how ownership is done today- in
particular, superusers simply *bypass* checks, owners do *not*. Users
are able to REVOKE the default rights that the owner has. Of course,
the owner can GRANT those back, but it's still not the same as
superuser.
How is role membership going to be handled with this change also? And
what about the information_schema? Surely we will need to report the
privileges of these "super-owners" through those views, just as we do
for owners today.
I envisage a very small patch. I'll write it so we can see, not in
time for Brussels though.
While that would certainly be nice, it doesn't seem very likely to work
out that way, unfortunately.
* Some super-privileges should exist by default. If super-privileges
exist only when granted by superusers and we have no access to
superusers then they never will exist, so they will be mostly useless
in many cases.We should at least consider if we really want upgrades to automatically
set up these super-privileges or not.This wouldn't be needed if the database owner has the super-privs automatically.
If the database owner is now a 'super owner' automatically then we
certainly do need to consider that we're changing the privilege system
for everyone as soon as they start using PG10. I'm not saying that it's
necessairly an issue, to be clear, and I believe Tom's argument is a
good one, but I do think we need to be sure we consider such a change
carefully.
Thanks!
Stephen
On 1/29/17 7:44 PM, Stephen Frost wrote:
I would think we'd either do this with a default role or a role
attribute.
That's not how I think about it. I think this would be a separate
aclitem[] stored somewhere. The pg_xxx_aclcheck() functions could
consult that implicitly.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1/30/17 9:04 AM, Simon Riggs wrote:
all I want in this release is
super-ownership.
What exactly is super-ownership, and what problems does it solve?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Simon Riggs <simon@2ndquadrant.com> writes:
Agreed. Let me reiterate: all I want in this release is
super-ownership.
While I'm not entirely convinced whether super-ownership is a good idea
or not, I am pretty sure that rushing to get it into v10 is a bad idea.
This is a rather fundamental change in our permissions model and it
might turn out to have undesirable consequences.
Or even more directly: any patch for this would necessarily be landing
in the last v10 commitfest. We have a project policy against major
changes showing up for the first time in the last fest of a cycle,
for good reasons.
Let's take our time and get it right.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 30 January 2017 at 16:43, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Simon Riggs <simon@2ndquadrant.com> writes:
Agreed. Let me reiterate: all I want in this release is
super-ownership.While I'm not entirely convinced whether super-ownership is a good idea
or not, I am pretty sure that rushing to get it into v10 is a bad idea.
This is a rather fundamental change in our permissions model and it
might turn out to have undesirable consequences.
Agreed. My view is that the current mechanism almost forces people to
use superusers for many things and that is definitely undesirable.
Or even more directly: any patch for this would necessarily be landing
in the last v10 commitfest. We have a project policy against major
changes showing up for the first time in the last fest of a cycle,
for good reasons.
I understand.
Let's take our time and get it right.
So we are able to see what is proposed, I attach a patch.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
db_owner_has_obj_privs.v1.patchapplication/octet-stream; name=db_owner_has_obj_privs.v1.patchDownload
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index d8ca39f..0a3b82e 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -463,15 +463,17 @@ GRANT <replaceable class="PARAMETER">role_name</replaceable> [, ...] TO <replace
all objects regardless of object privilege settings. This
is comparable to the rights of <literal>root</> in a Unix system.
As with <literal>root</>, it's unwise to operate as a superuser
- except when absolutely necessary.
+ except when absolutely necessary. Similarly, database owners can
+ access all objects regardless of object privilege settings, except
+ for objects that are owned by superusers.
</para>
<para>
- If a superuser chooses to issue a <command>GRANT</> or <command>REVOKE</>
- command, the command is performed as though it were issued by the
- owner of the affected object. In particular, privileges granted via
- such a command will appear to have been granted by the object owner.
- (For role membership, the membership appears to have been granted
+ If a superuser or database owner chooses to issue a <command>GRANT</> or
+ <command>REVOKE</> command, the command is performed as though it were
+ issued by the owner of the affected object. In particular, privileges
+ granted via such a command will appear to have been granted by the object
+ owner. (For role membership, the membership appears to have been granted
by the containing role itself.)
</para>
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index f4df6df..dfd437a 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3676,11 +3676,22 @@ pg_class_aclmask(Oid table_oid, Oid roleid,
return mask;
}
+ ownerId = classForm->relowner;
+
/*
- * Normal case: get the relation's ACL from pg_class
+ * If we have the privs of the database owner AND the owner of the object is
+ * not a superuser, then we bypass further permission-checking.
*/
- ownerId = classForm->relowner;
+ if (is_databaseowner(roleid) &&
+ !superuser_arg(ownerId))
+ {
+ ReleaseSysCache(tuple);
+ return mask;
+ }
+ /*
+ * Normal case: get the relation's ACL from pg_class
+ */
aclDatum = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_relacl,
&isNull);
if (isNull)
@@ -3797,6 +3808,14 @@ pg_proc_aclmask(Oid proc_oid, Oid roleid,
ownerId = ((Form_pg_proc) GETSTRUCT(tuple))->proowner;
+ /*
+ * If we have the privs of the database owner AND the owner of the object is
+ * not a superuser, then we bypass further permission-checking.
+ */
+ if (is_databaseowner(roleid) &&
+ !superuser_arg(ownerId))
+ return mask;
+
aclDatum = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_proacl,
&isNull);
if (isNull)
@@ -4272,6 +4291,14 @@ pg_type_aclmask(Oid type_oid, Oid roleid, AclMode mask, AclMaskHow how)
*/
ownerId = typeForm->typowner;
+ /*
+ * If we have the privs of the database owner AND the owner of the object is
+ * not a superuser, then we bypass further permission-checking.
+ */
+ if (is_databaseowner(roleid) &&
+ !superuser_arg(ownerId))
+ return mask;
+
aclDatum = SysCacheGetAttr(TYPEOID, tuple,
Anum_pg_type_typacl, &isNull);
if (isNull)
@@ -4565,6 +4592,14 @@ pg_class_ownercheck(Oid class_oid, Oid roleid)
ReleaseSysCache(tuple);
+ /*
+ * If we have the privs of the database owner AND the owner of the object is
+ * not a superuser, then we bypass further permission-checking.
+ */
+ if (is_databaseowner(roleid) &&
+ !superuser_arg(ownerId))
+ return true;
+
return has_privs_of_role(roleid, ownerId);
}
@@ -4591,6 +4626,14 @@ pg_type_ownercheck(Oid type_oid, Oid roleid)
ReleaseSysCache(tuple);
+ /*
+ * If we have the privs of the database owner AND the owner of the object is
+ * not a superuser, then we bypass further permission-checking.
+ */
+ if (is_databaseowner(roleid) &&
+ !superuser_arg(ownerId))
+ return true;
+
return has_privs_of_role(roleid, ownerId);
}
@@ -4643,6 +4686,14 @@ pg_proc_ownercheck(Oid proc_oid, Oid roleid)
ReleaseSysCache(tuple);
+ /*
+ * If we have the privs of the database owner AND the owner of the object is
+ * not a superuser, then we bypass further permission-checking.
+ */
+ if (is_databaseowner(roleid) &&
+ !superuser_arg(ownerId))
+ return true;
+
return has_privs_of_role(roleid, ownerId);
}
diff --git a/src/backend/utils/misc/superuser.c b/src/backend/utils/misc/superuser.c
index 175a067..89c36df 100644
--- a/src/backend/utils/misc/superuser.c
+++ b/src/backend/utils/misc/superuser.c
@@ -22,6 +22,7 @@
#include "access/htup_details.h"
#include "catalog/pg_authid.h"
+#include "catalog/pg_database.h"
#include "utils/inval.h"
#include "utils/syscache.h"
#include "miscadmin.h"
@@ -39,6 +40,11 @@ static bool roleid_callback_registered = false;
static void RoleidCallback(Datum arg, int cacheid, uint32 hashvalue);
+static Oid dbowner_roleid = InvalidOid;
+static bool dbowner_callback_registered = false;
+
+static void DBOwnerCallback(Datum arg, int cacheid, uint32 hashvalue);
+
/*
* The Postgres user running this command has Postgres superuser privileges
@@ -106,3 +112,46 @@ RoleidCallback(Datum arg, int cacheid, uint32 hashvalue)
/* Invalidate our local cache in case role's superuserness changed */
last_roleid = InvalidOid;
}
+
+/*
+ * The Postgres user running this command is the owner of current database
+ */
+bool
+is_databaseowner(Oid roleid)
+{
+ HeapTuple rtup;
+
+ /* Quick out for cache hit */
+ if (OidIsValid(dbowner_roleid))
+ return (dbowner_roleid == roleid);
+
+ /* If first time through, set up callback for cache flushes */
+ if (!dbowner_callback_registered)
+ {
+ CacheRegisterSyscacheCallback(DATABASEOID,
+ DBOwnerCallback,
+ (Datum) 0);
+ dbowner_callback_registered = true;
+ }
+
+ /* OK, look up the information in pg_database */
+ rtup = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));
+ if (!HeapTupleIsValid(rtup))
+ elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);
+
+ dbowner_roleid = ((Form_pg_database) GETSTRUCT(rtup))->datdba;
+ ReleaseSysCache(rtup);
+
+ return (dbowner_roleid == roleid);
+}
+
+/*
+ * DBOwnerCallback
+ * Syscache inval callback function
+ */
+static void
+DBOwnerCallback(Datum arg, int cacheid, uint32 hashvalue)
+{
+ /* Invalidate our local cache in case database's owner changed */
+ dbowner_roleid = InvalidOid;
+}
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 4c607b2..ebe1839 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -327,6 +327,7 @@ extern void SwitchBackToLocalLatch(void);
/* in utils/misc/superuser.c */
extern bool superuser(void); /* current user is superuser */
extern bool superuser_arg(Oid roleid); /* given user is superuser */
+extern bool is_databaseowner(Oid roleid); /* given user is database owner */
/*****************************************************************************
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 00dc7bd..50c5275 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -29,6 +29,7 @@ CREATE USER regress_user3;
CREATE USER regress_user4;
CREATE USER regress_user5;
CREATE USER regress_user5; -- duplicate
+CREATE USER regress_dbowner;
CREATE GROUP regress_group1;
CREATE GROUP regress_group2 WITH USER regress_user1, regress_user2;
@@ -39,6 +40,8 @@ ALTER GROUP regress_group2 ADD USER regress_user2; -- duplicate
ALTER GROUP regress_group2 DROP USER regress_user2;
GRANT regress_group2 TO regress_user4 WITH ADMIN OPTION;
+select 'ALTER DATABASE '||current_database()||' OWNER TO regress_dbowner' \gexec
+
-- test owner privileges
SET SESSION AUTHORIZATION regress_user1;
@@ -126,6 +129,21 @@ bar true
\.
SELECT * FROM atest1; -- ok
+--dbowner
+SET SESSION AUTHORIZATION regress_dbowner;
+CREATE TABLE atest3 ( a int, b text );
+CREATE INDEX ON atest1 (a);
+INSERT INTO atest1 VALUES (11, 'one');
+SELECT * FROM atest1 WHERE a = 11;
+DELETE FROM atest1 WHERE a = 11;
+UPDATE atest1 SET b = 'blech' WHERE a = 11;
+INSERT INTO atest3 SELECT * FROM atest1 WHERE a <> 11;
+TRUNCATE atest1;
+INSERT INTO atest1 SELECT * FROM atest3;
+DROP TABLE atest3;
+BEGIN;
+LOCK atest1 IN ACCESS EXCLUSIVE MODE;
+COMMIT;
-- groups
On 30 January 2017 at 16:34, Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
On 1/30/17 9:04 AM, Simon Riggs wrote:
all I want in this release is
super-ownership.What exactly is super-ownership, and what problems does it solve?
The problem is that there is no easy way for a DBA to have privs on
multiple sets of objects, so there is a request for superuser in many
cases. Superuser is too strong for most situations, so we are stuck.
We need some middle ground where a single user can manage many "normal
application objects" (tables, views, sequences, matviews, functions,
indexes, triggers) without problem, while not compromising other areas
that require higher security.
Probably more than 50% of PostgreSQL installs now use services that
block superuser accounts, so the majority of PostgreSQL users are
affected by these problems.
The permissions desirable for logical replication are a good example
of this, but not in any sense the only issue.
My hope is that we release v10 with a permissions model that allows
logical replication to be realistically usable when superuser is not
available. This is not a new requirement, but the privilege aspect of
the logical replication has been pushed back. While thinking about
other problems of access control I've rethought this so I now see the
wider problem and would like to solve that rather than just focus on
the needs of logical replication.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jan 30, 2017 at 5:33 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
I would call these "super privileges".
Peter suggests that we have a much more flexible structure for super-privileges.
In Peter's model, Tom's suggestion woud be to grant all of these
automatically to database owners.
GRANT ALL ON ALL TABLES TO $user
GRANT ALL ON ALL SEQUENCES TO $user
GRANT ALL ON ALL FUNCTIONS TO $userEither of them would be good for me, as long as we implement the rule
as Tom suggests that this would never apply to objects owned by a
superuser.
I like Peter's model better, or more precisely Stephen's suggestion of
doing this via some default roles. Tom's model breaks backward
compatibility in a security-sensitive way, and it doesn't generalize
to things like wanting a user who can read everything but who has no
elevated write privileges. The idea of having predefined roles called
pg_read_anything, pg_write_anything, etc. seems quite elegant and very
powerful, and nobody's existing permissions structure has to change
unless they so desire.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers