From: | Dave Hughes <dhughes20(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | SuperUser permission denied when creating table. |
Date: | 2019-07-09 12:31:05 |
Message-ID: | CAFTBbFAML_FWxMH4iLbRZ80v5NCHoUtpYn-yfmbtEEk899s0+g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2019-07-09 12:59:34 | Re: PGSQL 11.4: shared_buffers and /dev/shm size |
Previous Message | Konstantin Malanchev | 2019-07-09 12:27:56 | Re: PGSQL 11.4: shared_buffers and /dev/shm size |