Sample Header Ad - 728x90

PostgreSQL CREATE TABLE creates with incorrect owner

8 votes
2 answers
17919 views
I'm using PostgreSQL 9.2.4. When I create a table as a non-superuser in a database owned by that non-superuser, it is owned by the postgres user, so I can't put any data into it unless I explicitly grant myself permission. I created the non-superuser like this: admin_user=# create role "test1" NOINHERIT LOGIN ENCRYPTED PASSWORD 'wibble' CONNECTION LIMIT 10; Then I created a database owned by test1 like this: admin_user=# create database "test1$db1" with owner "test1"; Then I started a new psql as user test1, and created a table: test1$db1=> create table test_table (column_name varchar(50)); But I couldn't insert into it: test1$db1=> insert into test_table values ('some data'); ERROR: permission denied for relation test_table Checking the permissions shows that the table is owned by the postgres user: test1$db1=> \dt List of relations Schema | Name | Type | Owner --------+----------------+-------+---------- public | test_table | table | postgres However, I can grant myself permissions and do stuff: test1$db1=> grant insert, select on test_table to test1; GRANT test1$db1=> insert into test_table values ('some data'); INSERT 0 1 test1$db1=> select * from test_table; column_name ------------- some data (1 row) What's going on? I'm pretty sure this used to work. And the [PostgreSQL docs for CREATE TABLE](http://www.postgresql.org/docs/9.2/static/sql-createtable.html) say > CREATE TABLE will create a new, initially empty table in the current > database. The table will be owned by the user issuing the command. Having to grant permissions to myself on my own tables doesn't sound like it's what I should have to do. Any help much appreciated! **[UPDATE]** It looks like this might be caused by some kind of change to the PostgreSQL package at http://apt.postgresql.org/pub/repos/apt/ We have three machine images, all of which installed PostgreSQL from that source: * Install at 23 July 2013, 18:47 UTC -- correct table ownership * Install at 23 July 2013, 19:39 UTC -- correct table ownership * Install at 24 July 2013, 13:16 UTC -- error So perhaps something has been changed in the packaging. I'll drop a line to the mailing list for that.
Asked by Giles Thomas (231 rep)
Jul 25, 2013, 05:01 PM
Last activity: Oct 31, 2022, 04:02 AM