PostgreSQL – How to Alter Default Privileges of A Schema

ByAdmin

You can use the ALTER DEFAULT PRIVILEGES statement in PostgreSQL database to set default privileges that will be applied to newly created objects in a schema. This is a powerful way to control access and permissions for objects created by a specific user or role in a particular schema.

Here’s the basic syntax for using ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES [ FOR { ROLE role_name | USER user_name } ] 
[ IN SCHEMA schema_name ] 
[ GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } 
[ ON { TABLES | SEQUENCES | FUNCTIONS | TYPES } ] 
TO { PUBLIC | role_name | user_name};

where

  • FOR { ROLE role_name | USER user_name }: Specifies the role (user or group) for which you want to set default privileges. If not specified, it defaults to the role that is currently executing the statement.
  • IN SCHEMA schema_name: Specifies the schema to which the default privileges apply. If not specified, it applies to the current schema.
  • GRANT { ... }: Specifies the privileges you want to GRANT for the specified roles.
  • ON { TABLES | SEQUENCES | FUNCTIONS | TYPES }: Specifies the type of objects (tables, sequences, functions, or types) to which the default privileges apply.
  • TO { PUBLIC | role_name | user_name | GROUP group_name }: Specifies the roles or users to which the privileges should be granted

Here’s an example of how to use ALTER DEFAULT PRIVILEGES to grant all privileges on tables created in a specific schema to a particular role:

ALTER DEFAULT PRIVILEGES FOR ROLE my_role IN SCHEMA my_schema GRANT ALL PRIVILEGES ON TABLES TO my_role;

This statement grants all privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER) on tables created in the my_schema schema to the my_role role by default.

Remember to use this feature carefully, as it can have a significant impact on security and access control within your PostgreSQL database.

This query grants ALL privileges to dev_role on tables created by sales_app role in sales schema

Similar Posts

PostgreSQL – How to Extract Date from Timestamp

ByAdmin

You can extract date from a timestamp using various functions depending on the specific part of the date you want to extract. Here are some commonly used functions for extracting different parts of a date from a timestamp: Extracting Date Only (Year, Month, and Day):To extract only the date (year, month, and day) from a…

PostgreSQL – How to Move Sequence to a Different Schema

ByAdmin

To move a sequence to a different schema in PostgreSQL, you can use the ALTER SEQUENCE command. The basic syntax is as follows: ALTER SEQUENCE current_schema.sequence_name SET SCHEMA new_schema; where current_schema is the name of the schema where the sequence currently resides, sequence_name is the name of the sequence, and new_schema is the name of…

PostgreSQL – How to Escape an apostrophe(single quote) within a String

ByAdmin

You can escape a single quote (also known as an apostrophe) within a string by using another single quote. This is a common practice in SQL to handle single quotes within string literals. Here’s how you can do it: To represent a single quote within a string, you can double the single quote character (”)….

PostgreSQL – How to Change a User’s Password

ByAdmin

To change a user’s password in PostgreSQL, you can use the ALTER USER statement. Here’s how you can change a user’s password: Connect to PostgreSQL: You need to connect to your PostgreSQL server using a user account with superuser or administrative privileges, as only superusers can change the passwords of other users. Change the Password:To…