Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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.

share|improve this question
2  
What is your search_path? Is it slightly possible that you have more that one such table? Alternatively, what does SELECT current_user; return? –  dezso Jul 25 '13 at 20:36
    
It was a fresh install of PostgreSQL so I don't think there could be another table. search_path is "$user",public, and SELECT current_user returns test1 –  Giles Thomas Jul 26 '13 at 12:34
    
Nice finding - please report back when you get an answer. –  dezso Jul 26 '13 at 21:14
    
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? Please follow up on the mailing list if you have more details. –  user26448 Jul 27 '13 at 12:34
    
Thanks, Christoph -- I'm investigating further and seeing if I can get a simple repro. I'll follow up on the mailing list and will post back here with any generally-useful information. –  Giles Thomas Jul 29 '13 at 14:15

2 Answers 2

That the owner of your new table turns out to be postgres is very odd.

Either way, you can make Postgres grant or revoke any privileges to / from any role by default with ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT INSERT, UPDATE, DELETE ON TABLES TO test1;
share|improve this answer
    
I don't have time to investigate the owner thing right now, but something is of there. –  Erwin Brandstetter Jul 25 '13 at 18:44
    
I'm wondering if it's a broken PostgreSQL package in a PPA -- will update the question with details –  Giles Thomas Jul 26 '13 at 17:23
up vote 1 down vote accepted

OK, so thanks to everyone for the help.

But as so often with really weird stuff, it turned out to be an extra factor that I'd completely forgotten was involved. We use Dimitri Fontaine's PostgreSQL Extension Whitelist module -- we're a shared hosting environment and need to lock things down a bit. It looks like this was updated on 23 July, and we've confirmed that the problem does not exist without the extension and does exist with it. I'll put together a minimal repro for Dimitri and file a bug report.

Sorry for taking up your time!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.