7/21/2023 0 Comments List user postgres![]() ![]() This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf). CONNECTĪllows the grantee to connect to the database. Note that revoking this privilege will not alter the existence or location of existing objects. To rename an existing object, you must own the object and have this privilege for the containing schema.įor tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. CREATEįor databases, allows new schemas and publications to be created within the database, and allows trusted extensions to be installed within the database.įor schemas, allows new objects to be created within the schema. TRIGGERĪllows creation of a trigger on a table, view, etc. (In practice, any nontrivial DELETE command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete.) TRUNCATEĪllows creation of a foreign key constraint referencing a table, or specific column(s) of a table. DELETEĪllows DELETE of a row from a table, view, etc. ![]() For large objects, this privilege allows writing or truncating the object. For sequences, this privilege allows use of the nextval and setval functions. FOR SHARE also require this privilege on at least one column, in addition to the SELECT privilege. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.) SELECT. UPDATEĪllows UPDATE of any column, or specific column(s), of a table, view, etc. Can be granted on specific column(s), in which case only those columns may be assigned to in the INSERT command (other columns will therefore receive default values). INSERTĪllows INSERT of a new row into a table, view, etc. For large objects, this privilege allows the object to be read. For sequences, this privilege also allows use of the currval function. This privilege is also needed to reference existing column values in UPDATE, DELETE, or MERGE. But owners are always treated as holding all grant options, so they can always re-grant their own privileges.Īllows SELECT from any column, or specific column(s), of a table, view, materialized view, or other table-like object. For details see the GRANT and REVOKE reference pages.Īn object's owner can choose to revoke their own ordinary privileges, for example to make a table read-only for themselves as well as others. If the grant option is subsequently revoked then all who received the privilege from that recipient (directly or through a chain of grants) will lose the privilege. However, it is possible to grant a privilege “ with grant option”, which gives the recipient the right to grant it in turn to others. Ordinarily, only the object's owner (or a superuser) can grant or revoke privileges on an object. To revoke a previously-granted privilege, use the fittingly named REVOKE command: Also, “ group” roles can be set up to help manage privileges when there are many users of a database - for details see Chapter 22. The special “ role” name PUBLIC can be used to grant a privilege to every role on the system. Writing ALL in place of a specific privilege grants all privileges that are relevant for the object type. For example, if joe is an existing role, and accounts is an existing table, the privilege to update the table can be granted with: To assign privileges, the GRANT command is used. Superusers can always do this ordinary roles can only do it if they are both the current owner of the object (or a member of the owning role) and a member of the new owning role. (However, like all privileges, that right can be inherited by members of the owning role see Section 22.3.)Īn object can be assigned to a new owner with an ALTER command of the appropriate kind for the object, for exampleĪLTER TABLE table_name OWNER TO new_owner The right to modify or destroy an object is inherent in being the object's owner, and cannot be granted or revoked in itself. The following sections and chapters will also show you how these privileges are used. More detail about the meanings of these privileges appears below. The privileges applicable to a particular object vary depending on the object's type (table, function, etc.). ![]() There are different kinds of privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE, SET and ALTER SYSTEM. To allow other roles to use it, privileges must be granted. For most kinds of objects, the initial state is that only the owner (or a superuser) can do anything with the object. The owner is normally the role that executed the creation statement. When an object is created, it is assigned an owner. ![]()
0 Comments
Leave a Reply. |