Database security is addressed at several levels: Data base file protection. All files stored within the database are protected from reading by any account other than the Postgres superuser account. Connections from a client to the database server are, by default, allowed only via a local Unix socket, not via TCP/IP sockets. The backend must be started with the -i option to allow non-local clients to connect. Client connections can be restricted by IP address and/or user name via the pg_hba.conf file in PG_DATA. Client connections may be authenticated vi other external packages. Each user in Postgres is assigned a username and (optionally) a password. By default, users do not have write access to databases they did not create. Users may be assigned to groups, and table access may be restricted based on group privileges. Authentication is the process by which the backend server and postmaster ensure that the user requesting access to data is in fact who he/she claims to be. All users who invoke Postgres are checked against the contents of the pg_user class to ensure that they are authorized to do so. However, verification of the user's actual identity is performed in a variety of ways: From the user shell A backend server started from a user shell notes the user's (effective) user-id before performing a setuid to the user-id of user postgres. The effective user-id is used as the basis for access control checks. No other authentication is conducted. From the network If the Postgres system is built as distributed, access to the Internet TCP port of the postmaster process is available to anyone. The DBA configures the pg_hba.conf file in the PGDATA directory to specify what authentication system is to be used according to the host making the connection and which database it is connecting to. See pg_hba.conf(5) for a description of the authentication systems available. Of course, host-based authentication is not fool-proof in Unix, either. It is possible for determined intruders to also masquerade the origination host. Those security issues are beyond the scope of Postgres. To define a new user, run the createuser utility program. To assign a user or set of users to a new group, one must define the group itself, and assign users to that group. In Postgres these steps are not currently supported with a create group command. Instead, the groups are defined by inserting appropriate values into the pg_group system table, and then using the grant command to assign privileges to the group. Currently, there is no easy interface to set up user groups. You have to explicitly insert/update the pg_group table. For example: jolly=> insert into pg_group (groname, grosysid, grolist) jolly=> values ('posthackers', '1234', '{5443, 8261}'); INSERT 548224 jolly=> grant insert on foo to group posthackers; CHANGE jolly=> The fields in pg_group are: * groname: the group name. This a name and should be purely alphanumeric. Do not include underscores or other punctuation. * grosysid: the group id. This is an int4. This should be unique for each group. * grolist: the list of pg_user id's that belong in the group. This is an int4[]. Postgres provides mechanisms to allow users to limit the access to their data that is provided to other users. Database superusers Database super-users (i.e., users who have pg_user.usesuper set) silently bypass all of the access controls described below with two exceptions: manual system catalog updates are not permitted if the user does not have pg_user.usecatupd set, and destruction of system catalogs (or modification of their schemas) is never allowed. Access Privilege The use of access privilege to limit reading, writing and setting of rules on classes is covered in grant/revoke(l). Class removal and schema modification Commands that destroy or modify the structure of an existing class, such as alter, drop table, and drop index, only operate for the owner of the class. As mentioned above, these operations are never permitted on system catalogs. Functions and rules allow users to insert code into the backend server that other users may execute without knowing it. Hence, both mechanisms permit users to trojan horse others with relative impunity. The only real protection is tight control over who can define functions (e.g., write to relations with SQL fields) and rules. Audit trails and alerters on pg_class, pg_user and pg_group are also recommended. Functions written in any language except SQL run inside the backend server process with the permissions of the user postgres (the backend server runs with its real and effective user-id set to postgres. It is possible for users to change the server's internal data structures from inside of trusted functions. Hence, among many other things, such functions can circumvent any system access controls. This is an inherent problem with user-defined C functions. Like SQL functions, rules always run with the identity and permissions of the user who invoked the backend server. There are no plans to explicitly support encrypted data inside of Postgres (though there is nothing to prevent users from encrypting data within user-defined functions). There are no plans to explicitly support encrypted network connections, either, pending a total rewrite of the frontend/backend protocol. User names, group names and associated system identifiers (e.g., the contents of pg_user.usesysid) are assumed to be unique throughout a database. Unpredictable results may occur if they are not.