In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. This feature is also known as Row-Level Security. By default, tables do not have any policies, so that if a user has access privileges to a table according to the SQL privilege system, all rows within it are equally available for querying or

It is possible to use SSH to encrypt the network connection between clients and a PostgreSQL server. Done properly, this provides an adequately secure network connection, even for non-SSL-capable clients. First make sure that an SSH server is running properly on the same machine as the PostgreSQL server and that you can log in using ssh as some user. Then you can establish a secure tunnel with a command like this from the client machine: ssh -L 63333:localhost:5432 joe@foo.com The first numbe

NameROLLBACK TO SAVEPOINT -- roll back to a savepoint Synopsis ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name Description Roll back all commands that were executed after the savepoint was established. The savepoint remains valid and can be rolled back to again later, if needed. ROLLBACK TO SAVEPOINT implicitly destroys all savepoints that were established after the named savepoint. Parameters savepoint_name The savepoint to roll back to. Notes Use RELEASE SA

PostgreSQL databases require periodic maintenance known as vacuuming. For many installations, it is sufficient to let vacuuming be performed by the autovacuum daemon, which is described in Section 24.1.6. You might need to adjust the autovacuuming parameters described there to obtain best results for your situation. Some database administrators will want to supplement or replace the daemon's activities with manually-managed VACUUM commands, which typically are executed according to a schedule b

In some situations it is worthwhile to rebuild indexes periodically with the REINDEX command or a series of individual rebuilding steps. B-tree index pages that have become completely empty are reclaimed for re-use. However, there is still a possibility of inefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated. Therefore, a usage pattern in which most, but not all, keys in each range are eventually deleted will see poor use of space. For su

A database role can have a number of attributes that define its privileges and interact with the client authentication system. login privilege Only roles that have the LOGIN attribute can be used as the initial role name for a database connection. A role with the LOGIN attribute can be considered the same as a "database user". To create a role with login privilege, use either: CREATE ROLE name LOGIN; CREATE USER name; (CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes

NameREVOKE -- remove access privileges Synopsis REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] (

NameROLLBACK -- abort the current transaction Synopsis ROLLBACK [ WORK | TRANSACTION ] Description ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be discarded. Parameters WORKTRANSACTION Optional key words. They have no effect. Notes Use COMMIT to successfully terminate a transaction. Issuing ROLLBACK outside of a transaction block emits a warning and otherwise has no effect. Examples To abort all changes: ROLLBACK;

It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles. To set up a group role, first create the role: CREATE ROLE name; Typically a role being used as a group would not have the LOGIN attribute, though you can set it if you wish. Once the grou

NameROLLBACK PREPARED -- cancel a transaction that was earlier prepared for two-phase commit Synopsis ROLLBACK PREPARED transaction_id Description ROLLBACK PREPARED rolls back a transaction that is in prepared state. Parameters transaction_id The transaction identifier of the transaction that is to be rolled back. Notes To roll back a prepared transaction, you must be either the same user that executed the transaction originally, or a superuser. But you do not have to be in