SuperUser permission denied when creating table.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

SuperUser permission denied when creating table.

Dave Hughes
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?
Reply | Threaded
Open this post in threaded view
|

Re: SuperUser permission denied when creating table.

Achilleas Mantzios
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



Reply | Threaded
Open this post in threaded view
|

Re: SuperUser permission denied when creating table.

Dave Hughes
Sure thing.  I attached it as a txt file.  Thanks for the help!

On Tue, Jul 9, 2019 at 9:29 AM Achilleas Mantzios <[hidden email]> 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




schema_output.txt (681K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SuperUser permission denied when creating table.

Achilleas Mantzios
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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: SuperUser permission denied when creating table.

Tom Lane-2
In reply to this post by Dave Hughes
Dave Hughes <[hidden email]> 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