Thread: Odd table ownership for PostgreSQL

Odd table ownership for PostgreSQL

From
Giles Thomas
Date:
Hi there,

We're installing PostgreSQL to Ubuntu raring machines using the
http://apt.postgresql.org/pub/repos/apt/ PPA.  Something strange seems
to have happened at some point after 19:39 UTC on 23 July.

Details of the error are here
<http://dba.stackexchange.com/questions/47005/postgresql-create-table-creates-with-incorrect-owner>
but to summarise, we're creating a user, creating a database owned by
that user, and then as that user we're creating a table in the
database.  The new table turns out to be owned by user postgres rather
than by the new user.  The new user does not have (but can grant
himself) permissions on the table.

This problem is not present on a machine that installed PostgreSQL from
the PPA at 19:39 UTC on 23 July.  But it is present in one that
installed it at 13:16 UTC on 24 July, and in every machine we've
installed it on since.

Any help would be much appreciated!


All the best,

Giles

--
Giles Thomas <giles@pythonanywhere.com>

PythonAnywhere: Develop and host Python from your browser
<http://pythonanywhere.com/>

PythonAnywhere LLP
17a Clerkenwell Road, London EC1M 5RD, UK
VAT No.: GB 893 5643 79
Registered in England and Wales as company number OC378414.
Registered address: 28 Ely Place, 3rd Floor, London EC1N 6TD, UK



Re: Odd table ownership for PostgreSQL

From
Christoph Berg
Date:
Re: Giles Thomas 2013-07-26 <51F2B37E.5070501@pythonanywhere.com>
> We're installing PostgreSQL to Ubuntu raring machines using the
> http://apt.postgresql.org/pub/repos/apt/ PPA.  Something strange
> seems to have happened at some point after 19:39 UTC on 23 July.
>
> Details of the error are here
<http://dba.stackexchange.com/questions/47005/postgresql-create-table-creates-with-incorrect-owner>
> but to summarise, we're creating a user, creating a database owned
> by that user, and then as that user we're creating a table in the
> database.  The new table turns out to be owned by user postgres
> rather than by the new user.  The new user does not have (but can
> grant himself) permissions on the table.
>
> This problem is not present on a machine that installed PostgreSQL
> from the PPA at 19:39 UTC on 23 July.  But it is present in one that
> installed it at 13:16 UTC on 24 July, and in every machine we've
> installed it on since.
>
> Any help would be much appreciated!

Nothing has changed on apt.postgresql.org since July 22nd, so this is
not caused by a packaging change. I cannot reproduce the problem using
9.2.4-1.pgdg+1 on sid/amd64.

Is there any pooler between you and the database that might be messing
up with the current user?

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

Attachment

Re: Odd table ownership for PostgreSQL

From
Giles Thomas
Date:
Hi there,

On 27/07/2013 13:37, Christoph Berg wrote:
> Re: Giles Thomas 2013-07-26 <51F2B37E.5070501@pythonanywhere.com>
>> We're installing PostgreSQL to Ubuntu raring machines using the
>> http://apt.postgresql.org/pub/repos/apt/ PPA.  Something strange
>> seems to have happened at some point after 19:39 UTC on 23 July.
>>
>> Details of the error are here
<http://dba.stackexchange.com/questions/47005/postgresql-create-table-creates-with-incorrect-owner>
>> but to summarise, we're creating a user, creating a database owned
>> by that user, and then as that user we're creating a table in the
>> database.  The new table turns out to be owned by user postgres
>> rather than by the new user.  The new user does not have (but can
>> grant himself) permissions on the table.
>>
>> This problem is not present on a machine that installed PostgreSQL
>> from the PPA at 19:39 UTC on 23 July.  But it is present in one that
>> installed it at 13:16 UTC on 24 July, and in every machine we've
>> installed it on since.
>>
>> Any help would be much appreciated!
> Nothing has changed on apt.postgresql.org since July 22nd, so this is
> not caused by a packaging change. I cannot reproduce the problem using
> 9.2.4-1.pgdg+1 on sid/amd64.
>
> Is there any pooler between you and the database that might be messing
> up with the current user?

Apologies for taking up your bandwith with this one, it turns out that
there was one other factor involved that we'd forgotten -- we were using
PostgreSQL Extension Whitelist extension
(<https://github.com/dimitri/pgextwlist/>) and we've confirmed that the
problem is present iff that extension is installed.  It looks like it
was last updated on 23 July, so I think we've found our culprit.

All the best, and thanks very much for getting back to us on this.


Giles

--
Giles Thomas <giles@pythonanywhere.com>

PythonAnywhere: Develop and host Python from your browser
<http://pythonanywhere.com/>

PythonAnywhere LLP
17a Clerkenwell Road, London EC1M 5RD, UK
VAT No.: GB 893 5643 79
Registered in England and Wales as company number OC378414.
Registered address: 28 Ely Place, 3rd Floor, London EC1N 6TD, UK



Re: Odd table ownership for PostgreSQL

From
Dimitri Fontaine
Date:
Giles Thomas <giles@pythonanywhere.com> writes:
> Apologies for taking up your bandwith with this one, it turns out that there
> was one other factor involved that we'd forgotten -- we were using
> PostgreSQL Extension Whitelist extension
> (<https://github.com/dimitri/pgextwlist/>) and we've confirmed that the
> problem is present iff that extension is installed.  It looks like it was
> last updated on 23 July, so I think we've found our culprit.

Currently working on it to fix a security problem we had with the
previous version. The fix is not complete yet, but the extension code
will now run as if executed by a superuser, so owned by him, where it
used to be owned by the connected user.

I now see the following issue, please refer to that as I'll answer there
for more details:

  https://github.com/dimitri/pgextwlist/issues/6

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support