pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
incorrect pg_dump output due to not handling dropped roles correctly
14+ messages / 5 participants
[nested] [flat]

* incorrect pg_dump output due to not handling dropped roles correctly
@ 2019-11-15 09:01  Floris Van Nee <florisvannee@Optiver.com>
  0 siblings, 2 replies; 14+ messages in thread

From: Floris Van Nee @ 2019-11-15 09:01 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: Ferdi Smit <FerdiSmit@Optiver.com>

I noticed I wasn't able to apply my usual pg_dump schema output without errors anymore after I dropped some roles. After some digging, I found it has to do with Postgres not correctly updating the pg_init_privs table upon dropping roles. I can reproduce a similar scenario with the following steps (output from v13devel, but AFAIK all versions affected, I ran into the issue on v11.2):


postgres=# create role test;
CREATE ROLE
postgres=# alter default privileges in schema public grant all privileges on tables to test;
ALTER DEFAULT PRIVILEGES
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# select * from pg_catalog.pg_init_privs
where objoid=(select 'pg_stat_statements'::regclass);
 objoid | classoid | objsubid | privtype |                                   initprivs
--------+----------+----------+----------+-------------------------------------------------------------------------------
  16409 |     1259 |        0 | e        | {florisvannee=arwdDxt/florisvannee,test=arwdDxt/florisvannee,=r/florisvannee}
(1 row)

postgres=# drop owned by test;
DROP OWNED
postgres=# drop role test;
DROP ROLE
postgres=# select * from pg_catalog.pg_init_privs
where objoid=(select 'pg_stat_statements'::regclass);
 objoid | classoid | objsubid | privtype |                                   initprivs
--------+----------+----------+----------+--------------------------------------------------------------------------------
  16409 |     1259 |        0 | e        | {florisvannee=arwdDxt/florisvannee,16404=arwdDxt/florisvannee,=r/florisvannee}
(1 row)



If we do a pg_dump on this, there'll be a line like this in the output:


REVOKE ALL ON TABLE public.pg_stat_statements FROM "16404";


This fails when restoring, because there's no role '16404'.



Can I manually fix this by updating pg_init_privs catalog table? Eg. in the example, I could run something like?

update pg_catalog.pg_init_privs
set initprivs=(select array_agg(p) from unnest(initprivs) p where not (p::text like '16404%'))
where initprivs <> (select array_agg(p) from unnest(initprivs) p where not (p::text like '16404%'))
;

Dropping/recreating the extension seems to work too, but I'd like to avoid that if possible (that may be a solution for pg_stat_statements, but isn't necessarily possible for every extension).


I'm pretty sure I once ran into a similar issue before, when doing a pg_upgrade in-place from 10 to 11. I couldn't run pg_upgrade until - back then I fixed it by dropping/recreating the extension, but didn't know exactly what was causing it, so I didn't report it here. From glancing at the code, this seems to be following some similar code paths in dump/restore. Just so you know the impact may not be limited to manual pg_dump actions, but also potentially pg_upgrade if my memory is correct.



-Floris

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2019-11-15 16:39  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Floris Van Nee <florisvannee@Optiver.com>
  1 sibling, 1 reply; 14+ messages in thread

From: Tom Lane @ 2019-11-15 16:39 UTC (permalink / raw)
  To: Floris Van Nee <florisvannee@Optiver.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Ferdi Smit <FerdiSmit@Optiver.com>; Stephen Frost <sfrost@snowman.net>

Floris Van Nee <florisvannee@Optiver.com> writes:
> I noticed I wasn't able to apply my usual pg_dump schema output without errors anymore after I dropped some roles. After some digging, I found it has to do with Postgres not correctly updating the pg_init_privs table upon dropping roles. I can reproduce a similar scenario with the following steps (output from v13devel, but AFAIK all versions affected, I ran into the issue on v11.2):

Hm, looks like we forgot to teach the dependency mechanism about
pg_init_privs privileges?  (I didn't look yet.)

> Can I manually fix this by updating pg_init_privs catalog table?

Yeah, I think that would work.  Obviously best to test in a throwaway
database ...

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2019-11-17 20:56  Floris Van Nee <florisvannee@Optiver.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 14+ messages in thread

From: Floris Van Nee @ 2019-11-17 20:56 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Ferdi Smit <FerdiSmit@Optiver.com>; Stephen Frost <sfrost@snowman.net>

> Yeah, I think that would work.  Obviously best to test in a throwaway
> database ...

Thanks, Tom. Fixed it with that query.

As for the proper fix, indeed it looks like the 'drop role' needs to be taught to update pg_init_privs.

-Floris






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2019-11-18 03:10  Michael Paquier <michael@paquier.xyz>
  parent: Floris Van Nee <florisvannee@Optiver.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Michael Paquier @ 2019-11-18 03:10 UTC (permalink / raw)
  To: Floris Van Nee <florisvannee@Optiver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Ferdi Smit <FerdiSmit@Optiver.com>; Stephen Frost <sfrost@snowman.net>

On Sun, Nov 17, 2019 at 08:56:13PM +0000, Floris Van Nee wrote:
> As for the proper fix, indeed it looks like the 'drop role' needs to
> be taught to update pg_init_privs.

The dependencies related to the ACL entries exist in pg_shdepend
between the role and the revoked objects, and these get removed when
issuing DROP OWNED BY.  So it seems to me that the cleanup needs to
happen when issuing the DROP OWNED BY query, and not DROP ROLE.
Looking at the code, it seems to me that we should just patch
shdepDropOwned() to handle properly the removal of the role in ACL
objects in pg_init_privs for all the objects we are removing a
dependency on.  I am just diving into a patch..
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2019-11-18 04:14  Michael Paquier <michael@paquier.xyz>
  parent: Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 14+ messages in thread

From: Michael Paquier @ 2019-11-18 04:14 UTC (permalink / raw)
  To: Floris Van Nee <florisvannee@Optiver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Ferdi Smit <FerdiSmit@Optiver.com>; Stephen Frost <sfrost@snowman.net>

On Mon, Nov 18, 2019 at 12:10:59PM +0900, Michael Paquier wrote:
> The dependencies related to the ACL entries exist in pg_shdepend
> between the role and the revoked objects, and these get removed when
> issuing DROP OWNED BY.  So it seems to me that the cleanup needs to
> happen when issuing the DROP OWNED BY query, and not DROP ROLE.
> Looking at the code, it seems to me that we should just patch
> shdepDropOwned() to handle properly the removal of the role in ACL
> objects in pg_init_privs for all the objects we are removing a
> dependency on.  I am just diving into a patch..

Okay, I have been looking more at the code and as CREATE EXTENSION has
been creating the entry depending on the role, I would tend to think
that the simplest solution is that for each SHARED_DEPENDENCY_ACL we
should call a new routine, say RemoveRoleFromInitPriv(), which would
check for the presence of the object whose dependency is removed in
pg_init_privs and then remove from the ACL item list any trace of the
role whose ownerships are dropped.  The removal would require a logic
similar to what is done in RemoveRoleFromObjectPolicy(), where the
previous ACL is rebuilt but without the role removed.

It may be cleaner to invent a new type of dependency for pg_shdepend,
say SHARED_DEPENDENCY_INIT_PRIVS which would remove the dependency to
the object in pg_init_privs but that would not be backpatchable :(

Tom, any thoughts perhaps?
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2019-11-18 09:16  Floris Van Nee <florisvannee@Optiver.com>
  parent: Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 14+ messages in thread

From: Floris Van Nee @ 2019-11-18 09:16 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Ferdi Smit <FerdiSmit@Optiver.com>; Stephen Frost <sfrost@snowman.net>


> The dependencies related to the ACL entries exist in pg_shdepend
> between the role and the revoked objects, and these get removed when
> issuing DROP OWNED BY.  So it seems to me that the cleanup needs to
> happen when issuing the DROP OWNED BY query, and not DROP ROLE.
> Looking at the code, it seems to me that we should just patch
> shdepDropOwned() to handle properly the removal of the role in ACL
> objects in pg_init_privs for all the objects we are removing a
> dependency on.  I am just diving into a patch..

Forgive me for not following the logic here completely, as I haven't done a deep dive into the code.
I agree doing it in the DROP OWNED BY makes more sense, however I was suggesting to do it during 'DROP ROLE', because it is at least not enough to do it *only* in the DROP OWNED BY. For example, we can also manually remove the permissions and then drop the role, without using DROP OWNED BY.
So, if we do it during DROP OWNED BY, we should also handle it during one of the below REVOKE commands. Perhaps DROP OWNED BY already calls one of these functions internally - in that case you can ignore my comment. Just wanted to make sure we catch all possible cases this can occur.

-- before this, create role role, assign default privs and then create extension, then:

postgres=# select * from pg_catalog.pg_init_privs
where objoid=(select 'pg_stat_statements'::regclass);
 objoid | classoid | objsubid | privtype |                                   initprivs                                   
--------+----------+----------+----------+-------------------------------------------------------------------------------
  24583 |     1259 |        0 | e        | {florisvannee=arwdDxt/florisvannee,test=arwdDxt/florisvannee,=r/florisvannee}
(1 row)

postgres=# alter default privileges in schema public revoke all privileges on tables from test;
ALTER DEFAULT PRIVILEGES

postgres=# revoke all on pg_stat_statements from test;
REVOKE

postgres=# drop role test;
DROP ROLE
postgres=# select * from pg_catalog.pg_init_privs
where objoid=(select 'pg_stat_statements'::regclass);
 objoid | classoid | objsubid | privtype |                                   initprivs                                    
--------+----------+----------+----------+--------------------------------------------------------------------------------
  24583 |     1259 |        0 | e        | {florisvannee=arwdDxt/florisvannee,24578=arwdDxt/florisvannee,=r/florisvannee}
(1 row)


-Floris





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2019-11-18 11:56  Michael Paquier <michael@paquier.xyz>
  parent: Floris Van Nee <florisvannee@Optiver.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Michael Paquier @ 2019-11-18 11:56 UTC (permalink / raw)
  To: Floris Van Nee <florisvannee@Optiver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Ferdi Smit <FerdiSmit@Optiver.com>; Stephen Frost <sfrost@snowman.net>

On Mon, Nov 18, 2019 at 09:16:06AM +0000, Floris Van Nee wrote:
> So, if we do it during DROP OWNED BY, we should also handle it
> during one of the below REVOKE commands. Perhaps DROP OWNED BY
> already calls one of these functions internally - in that case you
> can ignore my comment. Just wanted to make sure we catch all
> possible cases this can occur. 

One problem here is that you would need to scan and track down all the
entries in pg_init_privs if you'd like to make sure that all the
traces of the role have been removed, so that's really not good for
performance.  The fix I was actually imagining upthread would be to
add a new type of entry in pg_shdepend linking the role with
pg_init_privs for the object where an ACL is added.  This would
prevent DROP ROLE to complete with your scenarios, and if the role
ownerships are dropped we could use the link between the object and
the role to remove directly the ACL for the role in the object.
That's not really backpatchable unfortunately.  On top of the new
pg_depend type we would need a new cleanup routine similar to what
happens for policy ACLs when dropping a role.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2019-12-03 20:27  Stephen Frost <sfrost@snowman.net>
  parent: Floris Van Nee <florisvannee@Optiver.com>
  1 sibling, 1 reply; 14+ messages in thread

From: Stephen Frost @ 2019-12-03 20:27 UTC (permalink / raw)
  To: Floris Van Nee <florisvannee@Optiver.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Ferdi Smit <FerdiSmit@Optiver.com>

Greetings,

* Floris Van Nee (florisvannee@Optiver.com) wrote:
> I noticed I wasn't able to apply my usual pg_dump schema output without errors anymore after I dropped some roles. After some digging, I found it has to do with Postgres not correctly updating the pg_init_privs table upon dropping roles. I can reproduce a similar scenario with the following steps (output from v13devel, but AFAIK all versions affected, I ran into the issue on v11.2):

Ok, this is ... interesting.

> postgres=# create role test;
> CREATE ROLE
> postgres=# alter default privileges in schema public grant all privileges on tables to test;
> ALTER DEFAULT PRIVILEGES
> postgres=# create extension pg_stat_statements;
> CREATE EXTENSION

So- in this case, the 'test' role is being granted these privileges
because it was given default privs in the public schema for objects that
are created by the superuser, even though the 'test' role never shows up
in the actual pg_stat_statements sql script.

I'm on the fence about if all of the objects which are created by an
extension should actually be subject to default privileges or not, but I
definitely don't think that the pg_init_privs system should be treating
those privileges that come from default privileges, instead of from the
extension's sql script, as being part of the 'initial privileges' for
the extension.

In short, I don't think any of the downthread discussion about how to
fix this is at all correct- the problem, as I view it, is that these
entries are getting into pg_init_privs in the first place and they
really shouldn't be because these privileges aren't coming from the
*extension* which is what pg_init_privs is trying to track.

Another way to view this is that I think the way we should be thinking
about the order of operations here is:

create role test;
alter default privs;
create extension;
  -- extension .sql runs WITHOUT any default privs being applied
  -- ACLs are recorded into pg_init_privs from the .sql script
  -- default privs are applied to the objects from the extension

Maybe we implement it that way, maybe we don't, but the above is my
feeling as to what the perception should be.

This would also mean that pg_dump would automatically figure out that
these privileges have been added AFTER the extension was created (and
aren't part of the extension's .sql script) and therefore there should
be some independent GRANT commands to add those privileges back included
in the pg_dump file.

Maybe something else to point out is that if we keep these entries in
pg_init_privs the way the downthread discussion seems to be assuming,
then pg_dump would *not* include the GRANT commands to add them back and
therefore you'd have to imagine re-ordering things in pg_dump so that
the default privileges are installed before the extension gets created,
and I'm hoping everyone here agress that'd be pretty crazy to try and
do.

Thanks,

Stephen

Attachments:

  signature.asc (application/pgp-signature, 818B) download

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2019-12-03 20:31  Stephen Frost <sfrost@snowman.net>
  parent: Michael Paquier <michael@paquier.xyz>
  0 siblings, 0 replies; 14+ messages in thread

From: Stephen Frost @ 2019-12-03 20:31 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Floris Van Nee <florisvannee@Optiver.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Ferdi Smit <FerdiSmit@Optiver.com>

Greetings Michael,

* Michael Paquier (michael@paquier.xyz) wrote:
> On Mon, Nov 18, 2019 at 09:16:06AM +0000, Floris Van Nee wrote:
> > So, if we do it during DROP OWNED BY, we should also handle it
> > during one of the below REVOKE commands. Perhaps DROP OWNED BY
> > already calls one of these functions internally - in that case you
> > can ignore my comment. Just wanted to make sure we catch all
> > possible cases this can occur. 
> 
> One problem here is that you would need to scan and track down all the
> entries in pg_init_privs if you'd like to make sure that all the
> traces of the role have been removed, so that's really not good for
> performance.  The fix I was actually imagining upthread would be to
> add a new type of entry in pg_shdepend linking the role with
> pg_init_privs for the object where an ACL is added.  This would
> prevent DROP ROLE to complete with your scenarios, and if the role
> ownerships are dropped we could use the link between the object and
> the role to remove directly the ACL for the role in the object.
> That's not really backpatchable unfortunately.  On top of the new
> pg_depend type we would need a new cleanup routine similar to what
> happens for policy ACLs when dropping a role.

Please review the message I just posted on this thread in response to
the original complaint.  I really don't think we want these entries to
end up in pg_init_privs in the first place and all of this around the
dependency handling is, at best, an independent thing and not actually
the root cause of this problem that we should be addressing.

I'm not sure that there's a case where this dependency handling would
actually be needed that's being described here, assuming we address the
root issue that these entries are being added in the first place- maybe
there is if an extension's .sql script has a built-in assumption that
some role already exists and then a user goes and drops that role, but
it seems like the right answer there might be to require the user to
drop the extension itself and not the role and we should track the
dependency that way.

Thanks,

Stephen

Attachments:

  signature.asc (application/pgp-signature, 818B) download

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2020-01-17 21:26  Alvaro Herrera <alvherre@2ndquadrant.com>
  parent: Stephen Frost <sfrost@snowman.net>
  0 siblings, 1 reply; 14+ messages in thread

From: Alvaro Herrera @ 2020-01-17 21:26 UTC (permalink / raw)
  To: Stephen Frost <sfrost@snowman.net>; +Cc: Floris Van Nee <florisvannee@Optiver.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Ferdi Smit <FerdiSmit@Optiver.com>

Stephen, are you intending to get this bug fixed?

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





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2020-01-29 19:07  Stephen Frost <sfrost@snowman.net>
  parent: Alvaro Herrera <alvherre@2ndquadrant.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Stephen Frost @ 2020-01-29 19:07 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Ferdi Smit <FerdiSmit@optiver.com>; Floris Van Nee <florisvannee@optiver.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Greetings,

On Fri, Jan 17, 2020 at 16:26 Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

> Stephen, are you intending to get this bug fixed?


I had been hoping to get some kind of feedback or even a response regarding
to the email I posted about what’s going on and how we might want to think
about handling this situation.

In particular- should we really be setting default privileges for objects
that are being created by an extension, at all?  The more I think about it,
the less I like it, particularly when we start to think about the trusted
extension work that Tom’s doing.

In short, I’m leaning towards the argument that this was a missing check in
the default ACL code to explicitly *not* add default privileges on objects
that are being created by an extension.  With such a check, the entire
wouldn’t end up in pg_init_privs and the issue that started this thread
wouldn’t happen.

Thanks,

Stephen

>

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2020-01-29 19:46  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Stephen Frost <sfrost@snowman.net>
  0 siblings, 1 reply; 14+ messages in thread

From: Tom Lane @ 2020-01-29 19:46 UTC (permalink / raw)
  To: Stephen Frost <sfrost@snowman.net>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Ferdi Smit <FerdiSmit@optiver.com>; Floris Van Nee <florisvannee@optiver.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Stephen Frost <sfrost@snowman.net> writes:
> In particular- should we really be setting default privileges for objects
> that are being created by an extension, at all?  The more I think about it,
> the less I like it, particularly when we start to think about the trusted
> extension work that Tom’s doing.

> In short, I’m leaning towards the argument that this was a missing check in
> the default ACL code to explicitly *not* add default privileges on objects
> that are being created by an extension.  With such a check, the entire
> wouldn’t end up in pg_init_privs and the issue that started this thread
> wouldn’t happen.

Hmm.  I think we'd still wish pg_upgrade to preserve whatever privileges
have been granted on extension objects, but that doesn't necessarily
have to happen through pg_init_privs.  I've forgotten the details ---
would the change you suggest be enough to make upgrade work correctly,
or would we need additional hacking?

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2020-01-29 20:42  Stephen Frost <sfrost@snowman.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 14+ messages in thread

From: Stephen Frost @ 2020-01-29 20:42 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Ferdi Smit <FerdiSmit@optiver.com>; Floris Van Nee <florisvannee@optiver.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Greetings,

On Wed, Jan 29, 2020 at 14:46 Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Stephen Frost <sfrost@snowman.net> writes:
> > In particular- should we really be setting default privileges for objects
> > that are being created by an extension, at all?  The more I think about
> it,
> > the less I like it, particularly when we start to think about the trusted
> > extension work that Tom’s doing.
>
> > In short, I’m leaning towards the argument that this was a missing check
> in
> > the default ACL code to explicitly *not* add default privileges on
> objects
> > that are being created by an extension.  With such a check, the entire
> > wouldn’t end up in pg_init_privs and the issue that started this thread
> > wouldn’t happen.
>
> Hmm.  I think we'd still wish pg_upgrade to preserve whatever privileges
> have been granted on extension objects, but that doesn't necessarily
> have to happen through pg_init_privs.  I've forgotten the details ---
> would the change you suggest be enough to make upgrade work correctly,
> or would we need additional hacking?


Err, privileges on upgrade *are* preserved, even with this. Guess I wasn’t
clear.

Right now:

If someone sets up default privileges for a user, and that user then
creates an extension, the default privs are applied to all of the
individual objects that are created by the extension, and then get recorded
in pg_init_privs, as if it was the extension setting up those privileges.

Having those privileges end up in pg_init_privs is pretty clearly wrong
because only those privileges that are installed by the extension should
end up in init privs, because all OTHER privileges are what will be dumped
out.

However, I’m not sure that those objects, which are inside the extension,
should really be having default privs applied to them in the first place!
If I have default privs set up to grant EXECUTE to some user on functions,
and then I install pg adminpack, is it really right for all the functions
in pg adminpack to now have execute rights granted to whomever this user
is- even when the extension revoked the grant to public because the
function is unsafe for general users to have access to..?

I feel like default privs should apply when the user is EXPLICITLY creating
a specific object, not when it’s being created as part of an extension, and
therefore we should have a check in the creation system, where the default
privs are handled, to basically make the default privs code be a no-op when
the object is being created by an extension. Users will have to go grant
access to those objects in the extension explicitly instead, but that seems
perfectly reasonable to me.

Currently traveling and so not actually looking at the code, but that’s my
recollection of things.

Thanks,

Stephen

>

^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: incorrect pg_dump output due to not handling dropped roles correctly
@ 2020-02-05 03:44  Stephen Frost <sfrost@snowman.net>
  parent: Stephen Frost <sfrost@snowman.net>
  0 siblings, 0 replies; 14+ messages in thread

From: Stephen Frost @ 2020-02-05 03:44 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Ferdi Smit <FerdiSmit@optiver.com>; Floris Van Nee <florisvannee@optiver.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Greetings,

* Stephen Frost (sfrost@snowman.net) wrote:
> On Wed, Jan 29, 2020 at 14:46 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Stephen Frost <sfrost@snowman.net> writes:
> > > In particular- should we really be setting default privileges for objects
> > > that are being created by an extension, at all?  The more I think about
> > it,
> > > the less I like it, particularly when we start to think about the trusted
> > > extension work that Tom’s doing.
> >
> > > In short, I’m leaning towards the argument that this was a missing check
> > in
> > > the default ACL code to explicitly *not* add default privileges on
> > objects
> > > that are being created by an extension.  With such a check, the entire
> > > wouldn’t end up in pg_init_privs and the issue that started this thread
> > > wouldn’t happen.
> >
> > Hmm.  I think we'd still wish pg_upgrade to preserve whatever privileges
> > have been granted on extension objects, but that doesn't necessarily
> > have to happen through pg_init_privs.  I've forgotten the details ---
> > would the change you suggest be enough to make upgrade work correctly,
> > or would we need additional hacking?
> 
> Err, privileges on upgrade *are* preserved, even with this. Guess I wasn’t
> clear.
> 
> Right now:
> 
> If someone sets up default privileges for a user, and that user then
> creates an extension, the default privs are applied to all of the
> individual objects that are created by the extension, and then get recorded
> in pg_init_privs, as if it was the extension setting up those privileges.
> 
> Having those privileges end up in pg_init_privs is pretty clearly wrong
> because only those privileges that are installed by the extension should
> end up in init privs, because all OTHER privileges are what will be dumped
> out.
> 
> However, I’m not sure that those objects, which are inside the extension,
> should really be having default privs applied to them in the first place!
> If I have default privs set up to grant EXECUTE to some user on functions,
> and then I install pg adminpack, is it really right for all the functions
> in pg adminpack to now have execute rights granted to whomever this user
> is- even when the extension revoked the grant to public because the
> function is unsafe for general users to have access to..?
> 
> I feel like default privs should apply when the user is EXPLICITLY creating
> a specific object, not when it’s being created as part of an extension, and
> therefore we should have a check in the creation system, where the default
> privs are handled, to basically make the default privs code be a no-op when
> the object is being created by an extension. Users will have to go grant
> access to those objects in the extension explicitly instead, but that seems
> perfectly reasonable to me.
> 
> Currently traveling and so not actually looking at the code, but that’s my
> recollection of things.

Back from said travel now and reviewed the thread and discussion and
this looks like exactly what's happening- so, does someone want to
comment on this, or..?

Specifically, if one does:

postgres=# create role test;
CREATE ROLE
postgres=# alter default privileges in schema public grant all privileges on tables to test;
ALTER DEFAULT PRIVILEGES
postgres=# create extension pg_stat_statements;
CREATE EXTENSION

Should the pg_stat_statements view, that's actually created by the
extension as part of its install, end up with the default privileges of
"grant all privileges on tables to test"?

At this point, my vote is 'no, that is a bug that we should fix.', but
it's also clearly a behavior change and maybe others have a different
opinion..?  If not, it should at least be a relatively easy fix..

Thanks,

Stephen

Attachments:

  signature.asc (application/pgp-signature, 818B) download

^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2020-02-05 03:44 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-15 09:01 incorrect pg_dump output due to not handling dropped roles correctly Floris Van Nee <florisvannee@Optiver.com>
2019-11-15 16:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-11-17 20:56   ` Floris Van Nee <florisvannee@Optiver.com>
2019-11-18 03:10     ` Michael Paquier <michael@paquier.xyz>
2019-11-18 04:14       ` Michael Paquier <michael@paquier.xyz>
2019-11-18 09:16         ` Floris Van Nee <florisvannee@Optiver.com>
2019-11-18 11:56           ` Michael Paquier <michael@paquier.xyz>
2019-12-03 20:31             ` Stephen Frost <sfrost@snowman.net>
2019-12-03 20:27 ` Stephen Frost <sfrost@snowman.net>
2020-01-17 21:26   ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-01-29 19:07     ` Stephen Frost <sfrost@snowman.net>
2020-01-29 19:46       ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-29 20:42         ` Stephen Frost <sfrost@snowman.net>
2020-02-05 03:44           ` Stephen Frost <sfrost@snowman.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox