![]() 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. (Thus, a client is not limited to connect as the role matching its operating system user, just as a person's login name need not match his or her real name.) Since the role identity determines the set of privileges available to a connected client, it is important to carefully configure privileges when setting up a multiuser environment.When an object is created, it is assigned an owner. The set of database roles a given client connection can connect as is determined by the client authentication setup, as explained in Chapter 21. Therefore it is often convenient to maintain a naming correspondence between roles and operating system users. Many applications assume the name of the current operating system user by default (including createuser and psql). ![]() For example, the psql program uses the -U command line option to indicate the role to connect as. The role name to use for a particular database connection is indicated by the client that is initiating the connection request in an application-specific fashion. In order to create more roles you first have to connect as this initial role.Įvery connection to the database server is made using the name of some particular role, and this role determines the initial access privileges for commands issued in that connection. It is common, but not required, to arrange for this role to be named postgres. This role is always a “ superuser”, and by default it will have the same name as the operating system user that initialized the database cluster, unless another name is specified while running initdb. In order to bootstrap the database system, a freshly initialized system always contains one predefined login-capable role. The psql program's \du meta-command is also useful for listing the existing roles. SELECT rolname FROM pg_roles WHERE rolcanlogin Or to see just those capable of logging in: ![]() To determine the set of existing roles, examine the pg_roles system catalog, for example: More details appear below.) To remove an existing role, use the analogous DROP ROLE command:įor convenience, the programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the shell command line: (In practice, you will usually want to add additional options, such as LOGIN, to the command. Name follows the rules for SQL identifiers: either unadorned without special characters, or double-quoted. To create a role use the CREATE ROLE SQL command: Database roles are global across a database cluster installation (and not per individual database). In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are conceptually completely separate from operating system users. ![]()
0 Comments
Leave a Reply. |