Search PostgreSQL documentation

Safely Designing a Validator ModuleManual ChaptersPG1850.1. Safely Designing a Validator Module Warning Read and understand the entirety of this section before implementing a validator module. A malfunctioning validator is potentially worse tha…Safely Designing a Validator Module Sample OutputManual ChaptersPG18F.25.8. Sample Output regression=# SELECT n.nspname, c.relname, count(*) AS buffers FROM pg_buffercache b JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND b.reldatabase IN …Sample Output Sample OutputManual ChaptersPG18F.27.2. Sample Output postgres=# SELECT * FROM pg_freespace('foo'); blkno | avail -------+------- 0 | 0 1 | 0 2 | 0 3 | 32 4 | 704 5 | 704 6 | 704 7 | 1216 8 | 704 9 | 704 10 | 704 11 | 704 …Sample Output Sample OutputManual ChaptersPG18F.31.2. Sample Output =# SELECT * FROM pgrowlocks('t1'); locked_row | locker | multi | xids | modes | pids ------------+--------+-------+-------+----------------+-------- (0,1) | 609 | f | {…Sample Output Sample OutputManual ChaptersPG18F.32.5. Sample Output bench=# SELECT pg_stat_statements_reset(); $ pgbench -i bench $ pgbench -c10 -t300 bench bench=# \x bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_b…Sample Output Sample UsesManual ChaptersPG18F.18.2. Sample Uses Many database systems have the notion of a many to many table. Such a table usually sits between two indexed tables, for example: CREATE TABLE left_table (id INT PRIMARY …Sample Uses Sampling Method Support FunctionsManual ChaptersPG1859.1. Sampling Method Support Functions The TSM handler function returns a palloc'd TsmRoutine struct containing pointers to the support functions described below. Most of the functions are …Sampling Method Support Functions SASL AuthenticationManual ChaptersPG1854.3. SASL Authentication SASL is a framework for authentication in connection-oriented protocols. At the moment, PostgreSQL implements three SASL authentication mechanisms: SCRAM-SHA-256, S…SASL Authentication SAVEPOINTManual ChaptersPG18SAVEPOINT SAVEPOINT — define a new savepoint within the current transaction Synopsis SAVEPOINT savepoint_nameSAVEPOINT Scalar SubqueriesManual ChaptersPG184.2.11. Scalar Subqueries A scalar subquery is an ordinary SELECT query in parentheses that returns exactly one row with one column. (See Chapter 7 for information about writing queries.) Th…Scalar Subqueries Schema Visibility Inquiry FunctionsManual ChaptersPG189.27.3. Schema Visibility Inquiry Functions Table 9.75 shows functions that determine whether a certain object is visible in the current schema search path. For example, a table is said to b…Schema Visibility Inquiry Functions SchemasManual ChaptersPG185.10. Schemas A PostgreSQL database cluster contains one or more named databases. Roles and a few other object types are shared across the entire cluster. A client connection to the server c…Schemas Schemas and PrivilegesManual ChaptersPG185.10.4. Schemas and Privileges By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. By…Schemas and Privileges schemataManual ChaptersPG1835.46. schemata The view schemata contains all schemas in the current database that the current user has access to (by way of being the owner or having some privilege). Table 35.44. schemata…schemata Scope for Host VariablesManual ChaptersPG1834.13.1. Scope for Host Variables The ecpg preprocessor understands the scope of variables in C. In the C language, this is rather simple because the scopes of variables is based on their co…Scope for Host Variables SCRAM-SHA-256 AuthenticationManual ChaptersPG1854.3.1. SCRAM-SHA-256 Authentication SCRAM-SHA-256, and its variant with channel binding SCRAM-SHA-256-PLUS, are password-based authentication mechanisms. They are described in detail in RFC…SCRAM-SHA-256 Authentication Search OrderManual ChaptersPG187.8.2.1. Search Order When computing a tree traversal using a recursive query, you might want to order the results in either depth-first or breadth-first order. This can be done by computing…Search Order Searched CASEManual ChaptersPG1841.6.4.5. Searched CASE CASE WHEN boolean-expression THEN statements [ WHEN boolean-expression THEN statements ... ] [ ELSE statements ] END CASE; The searched form of CASE provides conditio…Searched CASE Searching a TableManual ChaptersPG1812.2.1. Searching a Table It is possible to do a full text search without an index. A simple query to print the title of each row that contains the word friend in its body field is: SELECT t…Searching a Table Searching in ArraysManual ChaptersPG188.15.5. Searching in Arrays To search for a value in an array, each value must be checked. This can be done manually, if you know the size of the array. For example: SELECT * FROM sal_emp WH…Searching in Arrays Secure TCP/IP Connections with GSSAPI EncryptionManual ChaptersPG1818.10. Secure TCP/IP Connections with GSSAPI Encryption PostgreSQL also has native support for using GSSAPI to encrypt client/server communications for increased security. Support requires t…Secure TCP/IP Connections with GSSAPI Encryption Secure TCP/IP Connections with SSH TunnelsManual ChaptersPG1818.11. Secure TCP/IP Connections with SSH Tunnels It is possible to use SSH to encrypt the network connection between clients and a PostgreSQL server. Done properly, this provides an adequat…Secure TCP/IP Connections with SSH Tunnels Secure TCP/IP Connections with SSLManual ChaptersPG1818.9. Secure TCP/IP Connections with SSL PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is…Secure TCP/IP Connections with SSL SecurityManual ChaptersPG1829.11. Security The role used for the replication connection must have the REPLICATION attribute (or be a superuser). If the role lacks SUPERUSER and BYPASSRLS, publisher row security polici…Security SecurityManual ChaptersPG18Security If untrusted users have access to a database that has not adopted a secure schema usage pattern, do not run pgbench in that database. pgbench uses unqualified names and does not man…Security Security Considerations for Extension FunctionsManual ChaptersPG1836.17.6.1. Security Considerations for Extension Functions SQL-language and PL-language functions provided by extensions are at risk of search-path-based attacks when they are executed, sinc…Security Considerations for Extension Functions Security Considerations for Extension ScriptsManual ChaptersPG1836.17.6.2. Security Considerations for Extension Scripts An extension installation or update script should be written to guard against search-path-based attacks occurring when the script exe…Security Considerations for Extension Scripts Security Considerations for ExtensionsManual ChaptersPG1836.17.6. Security Considerations for Extensions Widely-distributed extensions should assume little about the database they occupy. Therefore, it's appropriate to write functions provided by …Security Considerations for Extensions SECURITY LABELManual ChaptersPG18SECURITY LABEL SECURITY LABEL — define or change a security label applied to an object Synopsis SECURITY LABEL [ FOR provider ] ON { TABLE object_name | COLUMN table_name.column_name | AGGRE…SECURITY LABEL Security LimitationsManual ChaptersPG18F.26.8.3. Security Limitations All pgcrypto functions run inside the database server. That means that all the data and passwords move between pgcrypto and client applications in clear text. …Security Limitations See AlsoManual ChaptersPG18See Also CLUSTERSee Also See AlsoManual ChaptersPG18See Also dropdb, CREATE DATABASESee Also See AlsoManual ChaptersPG18See Also dropuser, CREATE ROLE, createrole_self_grantSee Also See AlsoManual ChaptersPG18See Also createdb, DROP DATABASESee Also See AlsoManual ChaptersPG18See Also createuser, DROP ROLESee Also See AlsoManual ChaptersPG18See Also pg_ctl, postgres, Section 20.1See Also See AlsoManual ChaptersPG18See Also initdb, postgresSee Also See AlsoManual ChaptersPG18See Also Check pg_dump for details on possible error conditions.See Also See AlsoManual ChaptersPG18See Also amcheckSee Also See AlsoManual ChaptersPG18See Also pg_dump, Section 27.4.6See Also