Postgres13.4: Unable to Create Tables in Schema

Question

Dear all: I am working with Postgres13.4, and trying to create tables within a schema. Unfortunately, I just cant seem to find the right combination when defining the database and schema objects. I need to create a Database and a schema as postgres - I dont want the users to own either the database or the schema - only the objects in the schema. I have tried several variations of the following and still run into trouble: (as user postgres) ...

CREATE DATABASE new1;

CREATE USER user1 WITH LOGIN PASSWORD 'password';

CREATE ROLE role1;

GRANT TEMPORARY ON DATABASE new1 TO role1;

GRANT CONNECT ON DATABASE new1 TO user1;

GRANT role1 TO user1;

\connect new1

CREATE SCHEMA schema1;

GRANT USAGE ON SCHEMA schema1 TO ROLE1;

-- *** ALTER PRIVILEGES - TABLES

ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT ALL PRIVILEGES ON TABLES TO role1;

-- *** ALTER PRIVILEGES - SEQUENCES
ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT ALL PRIVILEGES ON SEQUENCES TO role1;

-- *** ALTER PRIVILEGES - FUNCTIONS
ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT ALL PRIVILEGES ON FUNCTIONS TO role1;

-- *** ALTER PRIVILEGES - TYPES
ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT ALL PRIVILEGES ON TYPES TO role1;

... After creating this database, I then logout/login as user1.

• I verify that schema1 exists and is owned by postgres:

  SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER FROM INFORMATION_SCHEMA.SCHEMATA;

• Confirm that user1 does have privileges on the schema: SELECT rolname, has_schema_privilege(rolname, 'public', 'usage') from pg_roles where rolname='user1';

• The list of schemas does show that role1 has privileges (\dn+): role1=U/postgres

• However, as user user1, executing display privileges (\dp) indicates that user1 has no privileges: (0 rows)

• If user1 attempts to create a table, the following error is displayed: permission denied for schema schema1

Is there something obvious that I missed here? How to enable user1 to create a table (and other objects) within the schema? THANK YOU

Answer 1

In addition to Jeff's answer, the ALTER DEFAULT PRIVILEGES will only apply to objects created as the user that ran the statements. To automatically grant privileges on objects created by user1, you have to run

ALTER DEFAULT PRIVILEGES FOR ROLE user1 ...

Answer 2

You only granted USAGE on the schema, not CREATE.