SuperUser permission denied when creating table.
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?
On 9/7/19 3:31 μ.μ., Dave Hughes wrote:
Has anyone ran across something like this before?
can you show your pg_dumpall -x --schema-only
?
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
Sure thing. I attached it as a txt file. Thanks for the help!
On Tue, Jul 9, 2019 at 9:29 AM Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:
Show quoted text
On 9/7/19 3:31 μ.μ., Dave Hughes wrote:
Has anyone ran across something like this before?
can you show your pg_dumpall -x --schema-only
?--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
Attachments:
schema_output.txttext/plain; charset=US-ASCII; name=schema_output.txtDownload
On 9/7/19 4:50 μ.μ., Dave Hughes wrote:
Sure thing. I attached it as a txt file. Thanks for the help!
PostgreSQL version?
On Tue, Jul 9, 2019 at 9:29 AM Achilleas Mantzios <achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.com>> wrote:
On 9/7/19 3:31 μ.μ., Dave Hughes wrote:
Has anyone ran across something like this before?
can you show your pg_dumpall -x --schema-only
?--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
Dave Hughes <dhughes20@gmail.com> writes:
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);
I think you're shooting yourself in the foot with those event triggers,
ie trg_create_table is switching the role to something that lacks
permissions to do the CREATE.
regards, tom lane