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 Get Current and Next Values of Sequence

ByAdmin

You can use Sequences to generate unique integer values, often used as auto-incrementing primary keys in tables. You can obtain the current value and the next value of a sequence using the currval and nextval functions, respectively. These functions are typically used within SQL statements or in PL/pgSQL code. Current Value (currval): To get the…

PostgreSQL – How to Update Multiple Columns

ByAdmin

To update multiple columns in a PostgreSQL table, you can use the UPDATE statement. Below is the basic syntax for updating multiple columns: UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; where Here’s an example of how to update multiple columns in a PostgreSQL table: Suppose you have a table named…

PostgreSQL – How to Convert Timestamp to String

ByAdmin

You can convert a timestamp to a string in PostgreSQL database using the TO_CHAR function, which allows you to format the timestamp as a string with a specific format. Here’s how you can do it: SELECT TO_CHAR(your_timestamp_column, ‘YYYY-MM-DD HH24:MI:SS’) AS timestamp_string FROM your_table; where The format string ‘YYYY-MM-DD HH24:MI:SS’ specifies the desired format for the…

PostgreSQL – How to Generate and Insert uuid

ByAdmin

The gen_random_uuid() function is used to generate a random UUID (Universally Unique Identifier) in PostgreSQL database. UUIDs are 128-bit identifiers that are often used as unique keys in database tables or for generating random values. Here’s how you can use gen_random_uuid() to generate a random UUID in PostgreSQL: SELECT gen_random_uuid(); When you execute above SQL…