Difference between revisions of "Database Schema"
Jump to navigation
Jump to search
↑ https://www.postgresql.org/docs/current/ddl-schemas.html
↑ https://www.postgresql.org/docs/current/ddl-schemas.html
↑ https://stackoverflow.com/a/10353730
↑ https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql
| (30 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
[[wikipedia:Database schema]] are analogous to [[directories]] at the operating system level, except that schemas cannot be nested. <ref>https://www.postgresql.org/docs/current/ddl-schemas.html</ref> | [[wikipedia:Database schema]] are analogous to [[directories]] at the operating system level, except that schemas cannot be nested. <ref>https://www.postgresql.org/docs/current/ddl-schemas.html</ref> | ||
| − | + | Typical schemas usage scenarios <ref> https://www.postgresql.org/docs/current/ddl-schemas.html</ref>: | |
| − | |||
| − | Typical schemas usage scenarios: | ||
* To allow many users to use one database without interfering with each other. | * To allow many users to use one database without interfering with each other. | ||
* To organize database objects into logical groups to make them more manageable. | * To organize database objects into logical groups to make them more manageable. | ||
* Third-party applications can be put into separate schemas so they do not collide with the names of other objects. | * Third-party applications can be put into separate schemas so they do not collide with the names of other objects. | ||
| + | public or default | ||
| + | == Examples == | ||
| + | * <code>[[CREATE SCHEMA]]</code> | ||
Grant examples on schemas: | Grant examples on schemas: | ||
| Line 13: | Line 14: | ||
* <code>[[GRANT USAGE ON]] [[SCHEMA]] your_schema TO [[GROUP]] your_group;</code> | * <code>[[GRANT USAGE ON]] [[SCHEMA]] your_schema TO [[GROUP]] your_group;</code> | ||
| − | |||
| − | |||
| − | |||
* Show/List [[schema]]s: | * Show/List [[schema]]s: | ||
** <code>select [[nspname]] from [[pg_catalog]].pg_namespace;</code> | ** <code>select [[nspname]] from [[pg_catalog]].pg_namespace;</code> | ||
** <code>select [[schema_name]] from [[information_schema]].schemata;</code> <ref>https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql</ref> | ** <code>select [[schema_name]] from [[information_schema]].schemata;</code> <ref>https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql</ref> | ||
| + | ** <code>[[\dn]]</code> | ||
| + | ** <code>[[\dn+]]</code> | ||
| + | |||
| + | [[PostgreSQL]]: <code>[[pg_catalog]]</code> and <code>[[public]]</code>. | ||
| + | |||
| + | |||
| + | [[GRANT SELECT ON ALL TABLES]] IN [[SCHEMA]] [[public]] TO xxx; | ||
| + | |||
| + | [[ALTER DEFAULT PRIVILEGES]] IN SCHEMA public GRANT SELECT ON TABLES TO xxx; | ||
| + | |||
| + | [[GRANT ALL PRIVILEGES ON SCHEMA]] | ||
| + | |||
| + | |||
| + | == Errors == | ||
| + | * [[ERROR: permission denied for schema public]] | ||
== Related == | == Related == | ||
| Line 26: | Line 39: | ||
* [[PostgreSQL: Database roles|Role]] | * [[PostgreSQL: Database roles|Role]] | ||
* [[PostgreSQL: catalogs]] | * [[PostgreSQL: catalogs]] | ||
| + | * <code>[[GRANT SELECT]]</code> | ||
| + | * [[Index]], [[Table]], [[Schema]], [[Sequence]], [[Views]], [[PostgreSQL Materialized Views|Materialized Views]] | ||
| + | * <code>[[SET]] [[search_path]]</code>, <code>[[SHOW search_path]]</code> | ||
| + | * <code>[[GRANT ON ALL TABLES]]</code> | ||
| + | * <code>[[schema_name]]</code> | ||
| + | |||
| + | == News == | ||
| + | * Oct 2022 [[PostgreSQL 15]], revokes the [[CREATE]] permission from all users except a database owner from the [[public]] (or default) schema | ||
== See also == | == See also == | ||
| − | * {{ | + | * {{Schema}} |
[[Category:PostgreSQL]] | [[Category:PostgreSQL]] | ||
Latest revision as of 08:54, 7 August 2024
wikipedia:Database schema are analogous to directories at the operating system level, except that schemas cannot be nested. [1] Typical schemas usage scenarios [2]:
- To allow many users to use one database without interfering with each other.
- To organize database objects into logical groups to make them more manageable.
- Third-party applications can be put into separate schemas so they do not collide with the names of other objects.
public or default
Contents
Examples[edit]
Grant examples on schemas:
GRANT USAGE ON ALL SEQUENCES IN SCHEMA your_schema TO mygrp;[3]GRANT USAGE ON SCHEMA your_schema TO GROUP your_group;
- Show/List schemas:
select nspname from pg_catalog.pg_namespace;select schema_name from information_schema.schemata;[4]\dn\dn+
PostgreSQL: pg_catalog and public.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO xxx;
GRANT ALL PRIVILEGES ON SCHEMA
Errors[edit]
Related[edit]
- Liquibase
- PostgreSQL, Amazon Redshift
- GRANT
- Role
- PostgreSQL: catalogs
GRANT SELECT- Index, Table, Schema, Sequence, Views, Materialized Views
SET search_path,SHOW search_pathGRANT ON ALL TABLESschema_name
News[edit]
- Oct 2022 PostgreSQL 15, revokes the CREATE permission from all users except a database owner from the public (or default) schema
See also[edit]
Advertising:
