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
Last activity: Oct 31, 2022, 04:02 AM