PSQL account owner of a schema but can not create tables

Question

Within a PSQL database I have an schema named:

tmp_atlas_mdcr2003_2021

and the owner of this schema is the account atlasuser. This user is always used to "communicate and run queries" against the tmp_atlas_mdcr2003_2021 schema.

However, it was received the next error:

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create table tmp_atlas_mdcr2003_2021.temp_cohort_ylv7yw28

(

  COHORT_DEFINITION_ID int NOT NULL,

  SUBJECT_ID bigint NOT NULL,

  cohort_start_date date NOT NULL,

  cohort_end_date date NOT NULL

)]; nested exception is org.postgresql.util.PSQLException: ERROR: permission denied for schema tmp_atlas_mdcr2003_2021

How the owner of a psql schema can not create a table within his own schema? atlasuser is not the owner of the database where this schema has been created.

How can this error be corrected?

Answer 1

If the user atlasuser is indeed the owner of schema tmp_atlas_mdcr2003_2021, then the only explanation is that the user is missing the CREATE privilege on the schema. You can grant that with

GRANT CREATE ON SCHEMA tmp_atlas_mdcr2003_2021 TO atlasuser;

If that doesn't do the trick, one of your assumptions must be incorrect. Verify the ownership and permissions with

SELECT nspowner::regrole AS owner,
       coalesce(nspacl::text, 'NULL') AS permissions
FROM pg_namespace
WHERE nspname = 'tmp_atlas_mdcr2003_2021';