I recently noticed that my default superuser "postgres" cannot create tables in new schemas I create, but can in some existing ones.
So as the postgres user I ran: afleads=# create schema mytest; CREATE SCHEMA afleads=# create table mytest.test (col1 int); ERROR: permission denied for schema mytest LINE 1: create table mytest.test (col1 int); So I tried to give usage & connect permission to another superuser I have, but I get the same error: afleads=# GRANT USAGE, CREATE ON SCHEMA mytest TO dhughes; GRANT afleads=# set role dhughes; SET afleads=# create table mytest.test (col1 int); ERROR: permission denied for schema mytest LINE 1: create table mytest.test (col1 int); So then I gave the same usage & create permission to a non-superuser. It's a group role we have: afleads=# GRANT USAGE, CREATE ON SCHEMA mytest TO creator_role; GRANT afleads=# set role creator_role; SET afleads=> create table mytest.test (col1 int); CREATE TABLE And then it works. This has me baffled as to why the two superusers I have do not have the permissions to create a table, when non-superusers can (once granted permissions). However I still seem to be able to create tables in older schemas I have: afleads=# select current_role; current_role -------------- postgres (1 row) afleads=# create table timeline.test (col1 int); CREATE TABLE Has anyone ran across something like this before?
