1

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

Improve this question

2 Answers 2

Reset to default
2

You only granted USAGE on the schema, not CREATE.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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 ...
Improve this answer

3 Comments

Guys: THANK YOU VERY MUCH. I have been working on this for days to get this working. You provided exactly what I needed to resolve the problem. THANK YOU. An interesting side note: some commands are still returning nothing. Examples: display privileges (\dp) is still showing (0 rows) after I create tables. Display tables (\dt) returns "Did not find any relations". Any ideas why? THANK YOU
They are not on the search_path. But that is unrelated.
I appreciate the help. Again, Thank You!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.