Schema Permissions

Created at: 2025-05-09

• By default, users cannot access any objects in schemas they do not own
• To allow that, the owner of the schema must grant the USAGE privilege on the schema.

  CREATE SCHEMA IF NOT EXISTS sweet_schema;
  CREATE USER sweet_user;
  
  GRANT USAGE ON SCHEMA sweet_schema TO sweet_user;
  
  -- USAGE: accessing objects in the schema.
  -- CREATE: creating objects in the schema.
  SELECT
    has_schema_privilege('sweet_user', 'sweet_schema', 'USAGE') as usage_privilege,
    has_schema_privilege('sweet_user', 'sweet_schema', 'CREATE') as create_privilege;
  -- usage_privilege  | t
  -- create_privilege | f
  

• By default, every user has USAGE privilege in the "public" schema.

  SELECT
    has_schema_privilege('sweet_user', 'public', 'USAGE') as usage_privilege,
    has_schema_privilege('sweet_user', 'public', 'CREATE') as create_privilege;
  -- usage_privilege  | t
  -- create_privilege | f
  

• To allow users to make use of the objects in a schema, additional privileges might need to be granted, as appropriate for the object.

  DROP TABLE IF EXISTS sweet_schema.foo;
  CREATE TABLE sweet_schema.foo (id SERIAL PRIMARY KEY);
  INSERT INTO sweet_schema.foo SELECT generate_series(1, 1000);
  
  SELECT
    grantee,
    table_schema AS schema,
    table_name,
    privilege_type AS privilege,
    grantor
  FROM
    information_schema.table_privileges
  WHERE
    grantee = 'sweet_user';
  -- (0 rows)
  
  GRANT SELECT ON sweet_schema.foo TO sweet_user;
  GRANT INSERT ON sweet_schema.foo TO sweet_user;
  GRANT UPDATE ON sweet_schema.foo TO sweet_user;
  GRANT DELETE ON sweet_schema.foo TO sweet_user;
  GRANT TRUNCATE ON sweet_schema.foo TO sweet_user;
  GRANT REFERENCES ON sweet_schema.foo TO sweet_user;
  GRANT TRIGGER ON sweet_schema.foo TO sweet_user;
  --  grantee   |    schema    | table_name | privilege  |      grantor
  --------------+--------------+------------+------------+-------------------
  -- sweet_user | sweet_schema | foo        | INSERT     | marcelo.fernandes
  -- sweet_user | sweet_schema | foo        | SELECT     | marcelo.fernandes
  -- sweet_user | sweet_schema | foo        | UPDATE     | marcelo.fernandes
  -- sweet_user | sweet_schema | foo        | DELETE     | marcelo.fernandes
  -- sweet_user | sweet_schema | foo        | TRUNCATE   | marcelo.fernandes
  -- sweet_user | sweet_schema | foo        | REFERENCES | marcelo.fernandes
  -- sweet_user | sweet_schema | foo        | TRIGGER    | marcelo.fernandes
  

Tips

For testing permissions, you can always:

SET ROLE sweet_user;
-- test stuff here
SET ROLE "marcelo.fernandes";

In databases upgraded from PostgreSQL 14 or earlier, everyone has that privilege on the schema public. Some usage patterns call for revoking that privilege:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;