Thread: [HACKERS] Removal of deprecated views pg_user, pg_group, pg_shadow

Greetings,

The question of removing the pre-role, deprecated, views of pg_user,
pg_group and pg_shadow has come up again.

I figured a new thread was in order, however, to allow others to weigh
in on it.

Note that these views have not been consistently maintained and have
ended up including some role attributes from recent versions (eg:
bypassrls) but were missed when others were added (eg: createrole).
There are properly maintained and cared for role-based versions of all
of these views, which are pg_roles, pg_auth_members, and pg_authid,
respectively.

As we move forward with the other many changes in PG10, it seems like a
good time to remove these inconsistent and ancient views that were
introduced when roles were added in 2005.

Thanks!

Stephen

Stephen Frost <sfrost@snowman.net> writes:
> The question of removing the pre-role, deprecated, views of pg_user,
> pg_group and pg_shadow has come up again.

> I figured a new thread was in order, however, to allow others to weigh
> in on it.

> Note that these views have not been consistently maintained and have
> ended up including some role attributes from recent versions (eg:
> bypassrls) but were missed when others were added (eg: createrole).
> There are properly maintained and cared for role-based versions of all
> of these views, which are pg_roles, pg_auth_members, and pg_authid,
> respectively.

Umm ... what exactly is the argument that those views are really better,
and are not just destined to become legacy views in their turn?

> As we move forward with the other many changes in PG10, it seems like a
> good time to remove these inconsistent and ancient views that were
> introduced when roles were added in 2005.

This sounds like "v10 is a great time to break stuff", which we've
already agreed is not project policy.

If there's a positive reason why these old views are impeding progress,
then let's remove 'em, but I don't think you've presented one.  What
exactly will it hurt to leave them there?
        regards, tom lane

Tom,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > The question of removing the pre-role, deprecated, views of pg_user,
> > pg_group and pg_shadow has come up again.
>
> > I figured a new thread was in order, however, to allow others to weigh
> > in on it.
>
> > Note that these views have not been consistently maintained and have
> > ended up including some role attributes from recent versions (eg:
> > bypassrls) but were missed when others were added (eg: createrole).
> > There are properly maintained and cared for role-based versions of all
> > of these views, which are pg_roles, pg_auth_members, and pg_authid,
> > respectively.
>
> Umm ... what exactly is the argument that those views are really better,
> and are not just destined to become legacy views in their turn?

The pg_auth_members view includes more information about the role
relationships (who the grantor is, if the admin option has been granted)
and it also frames the relationships in the multi-level directed acyclic
graph structure that truely represents what role membership means.  The
pg_roles view includes *all* of the role attributes, not just an
arbitrary (and entirely unintentional, as far as I can tell) subset of
them.

> > As we move forward with the other many changes in PG10, it seems like a
> > good time to remove these inconsistent and ancient views that were
> > introduced when roles were added in 2005.
>
> This sounds like "v10 is a great time to break stuff", which we've
> already agreed is not project policy.

How about "it's not a bad time to break stuff."

> If there's a positive reason why these old views are impeding progress,
> then let's remove 'em, but I don't think you've presented one.  What
> exactly will it hurt to leave them there?

They're misleading by having an arbitrary subset of the role attributes
and implying that the role relationships are simpler than they actually
are.  Frankly, they're also not being consistently maintained based on
any proper policy, which I find quite objectionable.  I'll admit that I
hold PG to a pretty high standard when it comes to such things, but
that's simply because I have a very vested interest in having users feel
that PG is a solid, well designed, and well maintained database system.
These half-forgotten warts detract from that, even when they're warts
that I wrote in the first place.  12-year-ago me simply didn't have the
experience that now-me does, or the foresight to predict that these
views would still be hanging around at this point.

Of course, we could fix these issues- we could add the grantor to the
pg_groups view, and perhaps even change it to be an acyclic directed
graph structure, and we could add the role attributes to pg_user and
pg_shadow which are missing, but at that point all we're really doing,
it seems to me, is providing synonyms for the existing canonical views,
and that hardly seems useful.

Thanks!

Stephen

On 2/10/17 12:04 AM, Stephen Frost wrote:
> They're misleading by having an arbitrary subset of the role attributes
> and implying that the role relationships are simpler than they actually
> are.  Frankly, they're also not being consistently maintained based on
> any proper policy, which I find quite objectionable.

+1

> Of course, we could fix these issues- we could add the grantor to the
> pg_groups view, and perhaps even change it to be an acyclic directed
> graph structure, and we could add the role attributes to pg_user and
> pg_shadow which are missing, but at that point all we're really doing,
> it seems to me, is providing synonyms for the existing canonical views,
> and that hardly seems useful.

Well, there's always the issue of breaking peoples existing code, which 
will probably remain an issue until we become more "in your face" with 
users about stuff we're trying to deprecate.

My vote would be to either kill the views or explicitly deprecate them 
and move them to contrib.
-- 
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)

On Thu, Feb 9, 2017 at 8:54 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Note that these views have not been consistently maintained and have
> ended up including some role attributes from recent versions

That's not a bug.  According to the documentation, these views exist
for compatibility with PostgreSQL versions before 8.1, so there's no
need to update them with newer fields.  Clients who are expecting to
talk with a pre-8.1 PostgreSQL won't expect those fields to be present
anyway.

My big objection to removing these views is that it will break pgAdmin
3, which uses all three of these views.  I understand that the pgAdmin
community is now moving away from pgAdmin 3 and toward pgAdmin 4, but
I bet that pgAdmin 3 still has significant usage and will continue to
have significant usage for at least a year or three.  When it's
thoroughly dead, then I think we can go ahead and do this, unless
there are other really important tools still depending on those views,
but it's only been 3 months since the final pgAdmin 3 release.

IMHO, these views aren't costing us much.  It'd be nice to get rid of
them eventually but a view definition doesn't really need much
maintenance.  (A contrib module doesn't either, but more than a view
definition.)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 02/13/2017 11:00 AM, Robert Haas wrote:
> My big objection to removing these views is that it will break pgAdmin
> 3, which uses all three of these views.  I understand that the pgAdmin
> community is now moving away from pgAdmin 3 and toward pgAdmin 4, but
> I bet that pgAdmin 3 still has significant usage and will continue to
> have significant usage for at least a year or three.  When it's
> thoroughly dead, then I think we can go ahead and do this, unless
> there are other really important tools still depending on those views,
> but it's only been 3 months since the final pgAdmin 3 release.

How long would you suggest is appropriate?  Postgres 11? 12?  Let's set
a target date; that way we can communicate it more than once.

-- 
Josh Berkus
Containers & Databases Oh My!

Robert Haas wrote:

> My big objection to removing these views is that it will break pgAdmin
> 3, which uses all three of these views.  I understand that the pgAdmin
> community is now moving away from pgAdmin 3 and toward pgAdmin 4, but
> I bet that pgAdmin 3 still has significant usage and will continue to
> have significant usage for at least a year or three.  When it's
> thoroughly dead, then I think we can go ahead and do this, unless
> there are other really important tools still depending on those views,
> but it's only been 3 months since the final pgAdmin 3 release.

Well, we can remove them from PG10 and pgAdmin3 (and others) be adjusted
to use the new ones, conditionally on server version.  Surely pgAdmin3
is going to receive further updates, given that it's still widely used?

> IMHO, these views aren't costing us much.  It'd be nice to get rid of
> them eventually but a view definition doesn't really need much
> maintenance.

Maybe not, but the fact that they convey misleading information is bad.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, Feb 13, 2017 at 2:42 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> Robert Haas wrote:
>> My big objection to removing these views is that it will break pgAdmin
>> 3, which uses all three of these views.  I understand that the pgAdmin
>> community is now moving away from pgAdmin 3 and toward pgAdmin 4, but
>> I bet that pgAdmin 3 still has significant usage and will continue to
>> have significant usage for at least a year or three.  When it's
>> thoroughly dead, then I think we can go ahead and do this, unless
>> there are other really important tools still depending on those views,
>> but it's only been 3 months since the final pgAdmin 3 release.
>
> Well, we can remove them from PG10 and pgAdmin3 (and others) be adjusted
> to use the new ones, conditionally on server version.  Surely pgAdmin3
> is going to receive further updates, given that it's still widely used?

According to the pgAdmin web site, no.  (Yeah, that does seem odd.)

>> IMHO, these views aren't costing us much.  It'd be nice to get rid of
>> them eventually but a view definition doesn't really need much
>> maintenance.
>
> Maybe not, but the fact that they convey misleading information is bad.

Has anyone actually been confused by them?

I'm a bit skeptical about the idea that these are misleading people,
because the information is no more or less misleading now than it was
in PostgreSQL 8.1 when the views were introduced.  And evidently it
was not so misleading at that time as to make us thing that a hard
compatibility break was warranted.

On the other hand, I suppose that the last version of pgAdmin 3 isn't
likely to work with future major versions of PostgreSQL anyway unless
somebody updates it, and if somebody decides to update it for the
other changes in v10 then updating it for the removal of these views
won't be much extra work.  So maybe it doesn't matter.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Thu, Feb 9, 2017 at 8:54 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > Note that these views have not been consistently maintained and have
> > ended up including some role attributes from recent versions
>
> That's not a bug.  According to the documentation, these views exist
> for compatibility with PostgreSQL versions before 8.1, so there's no
> need to update them with newer fields.  Clients who are expecting to
> talk with a pre-8.1 PostgreSQL won't expect those fields to be present
> anyway.

Yet we added bypassrls to them, after a similar discussion of how
they're for backwards compat and we can't get rid of them, but we should
update them with new things, blah, blah.

> My big objection to removing these views is that it will break pgAdmin
> 3, which uses all three of these views.  I understand that the pgAdmin
> community is now moving away from pgAdmin 3 and toward pgAdmin 4, but
> I bet that pgAdmin 3 still has significant usage and will continue to
> have significant usage for at least a year or three.  When it's
> thoroughly dead, then I think we can go ahead and do this, unless
> there are other really important tools still depending on those views,
> but it's only been 3 months since the final pgAdmin 3 release.

IMHO, if it's dead enough to not get updated for such changes then we
shouldn't care enough about it to maintain backwards compat views in our
code-base for it.

> IMHO, these views aren't costing us much.  It'd be nice to get rid of
> them eventually but a view definition doesn't really need much
> maintenance.  (A contrib module doesn't either, but more than a view
> definition.)

Clearly, it does need some form of maintenance and consideration or it
ends up in a confusing and inconsistent state, as evidenced by the fact
that that's exactly where we are.  I don't really expect to actually win
this argument, as I've had the experience of trying to fight this fight
before, but I certainly don't agree that we should try to continue to
maintain them for pgAdmin3's sake.

Thanks!

Stephen

Robert,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Mon, Feb 13, 2017 at 2:42 PM, Alvaro Herrera
> <alvherre@2ndquadrant.com> wrote:
> > Well, we can remove them from PG10 and pgAdmin3 (and others) be adjusted
> > to use the new ones, conditionally on server version.  Surely pgAdmin3
> > is going to receive further updates, given that it's still widely used?
>
> According to the pgAdmin web site, no.  (Yeah, that does seem odd.)

I really do not think the PG core project should be held hostage by an
external and apparently not-really-maintained project.  What if we
introduce some other difference in PG10 that breaks pgAdmin3?  Are we
going to roll that change back?  Are we sure that none exists already?

And, as I understand it, pgAdmin3 hasn't got support for features
introduced as far back as 9.5 either, surely it's not going to have
support added to it for the publication/subscription things or
declarative partitioning, should we rip those out to accomedate
pgAdmin3?

> >> IMHO, these views aren't costing us much.  It'd be nice to get rid of
> >> them eventually but a view definition doesn't really need much
> >> maintenance.
> >
> > Maybe not, but the fact that they convey misleading information is bad.
>
> Has anyone actually been confused by them?

This isn't something we can prove.  Nor can we prove that no one has
ever been confused.  What we can show is that they're clearly misleading
and inconsistent.  Even if no one is ever confused by them, having them
is bad.

> I'm a bit skeptical about the idea that these are misleading people,
> because the information is no more or less misleading now than it was
> in PostgreSQL 8.1 when the views were introduced.  And evidently it
> was not so misleading at that time as to make us thing that a hard
> compatibility break was warranted.

No, that's not how I recall the discussion going down when I introduced
them in 8.1.  It was more along the lines of "Here's a patch, oh, and I
added these views too."  The archives might prove me wrong, as memory
does fade after years, but I certainly don't recall any lengthy
discussion about if we should add these views or not.  In short, my
recollection is that we added them because it was easy to do at the time
and we didn't have the foresight to realize just how hard they would
become to get rid of and how much time they would suck up from people
arguing about them.

This is part of the bigger picture that we need to consider whenever we
think about backwards-compat mis-features.

> On the other hand, I suppose that the last version of pgAdmin 3 isn't
> likely to work with future major versions of PostgreSQL anyway unless
> somebody updates it, and if somebody decides to update it for the
> other changes in v10 then updating it for the removal of these views
> won't be much extra work.  So maybe it doesn't matter.

Indeed.

Thanks!

Stephen

On Mon, Feb 13, 2017 at 5:29 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> On Thu, Feb 9, 2017 at 8:54 PM, Stephen Frost <sfrost@snowman.net> wrote:
>> > Note that these views have not been consistently maintained and have
>> > ended up including some role attributes from recent versions
>>
>> That's not a bug.  According to the documentation, these views exist
>> for compatibility with PostgreSQL versions before 8.1, so there's no
>> need to update them with newer fields.  Clients who are expecting to
>> talk with a pre-8.1 PostgreSQL won't expect those fields to be present
>> anyway.
>
> Yet we added bypassrls to them, after a similar discussion of how
> they're for backwards compat and we can't get rid of them, but we should
> update them with new things, blah, blah.

My recollection of that conversation is a little fuzzy and I can't
immediately find the email in the archives, but I don't think I argued
for that; I think I may have argued against it; I don't think that I
understood the point of it then and I don't now.  In short, if you're
feeling under some kind of obligation to update those views, don't
feel obliged on my account.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Feb 13, 2017 at 5:38 PM, Stephen Frost <sfrost@snowman.net> wrote:
> I really do not think the PG core project should be held hostage by an
> external and apparently not-really-maintained project.  What if we
> introduce some other difference in PG10 that breaks pgAdmin3?  Are we
> going to roll that change back?  Are we sure that none exists already?

As a general rule, I don't agree that taking account of what will
break external projects constitutes being "held hostage".  I think
that kind of hyperbole is unhelpful.  How about we call it "trying not
to gratuitously break popular third-party tools"?

But in this case, I conceded the exact point that you are making here
later on in the exact same email to which you are replying, so I'm a
little mystified by the way you wrote this response.

> In short, my
> recollection is that we added them because it was easy to do at the time
> and we didn't have the foresight to realize just how hard they would
> become to get rid of and how much time they would suck up from people
> arguing about them.

I'm pretty sure we've spent more time arguing about them than it would
have taken to maintain them from now until 2030, and I don't really
understand why you're on the warpath to get rid of them.  But I don't
really care about it enough to keep arguing now that I've realized
pgAdmin3 isn't going to work with PG 10 either way.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company