{"id":96535,"date":"2023-04-10T18:18:50","date_gmt":"2023-04-10T18:18:50","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96535"},"modified":"2024-04-16T19:17:11","modified_gmt":"2024-04-16T19:17:11","slug":"postgresql-schema-learning-postgresql-with-grant","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/postgresql-schema-learning-postgresql-with-grant\/","title":{"rendered":"PostgreSQL Schema: Learning PostgreSQL with Grant"},"content":{"rendered":"

This is part of an ongoing series of post by Grant as he takes his vast knowledge of SQL Server and applies it to adding PostgreSQL and shares it with you so you can skip learn from his triumphs and mistakes. For more you can go to the Learning PostgreSQL with Grant<\/a> series home page<\/strong><\/em><\/p>\n\n

An important aspect of building and managing a database is organizing the objects within your database. You may have tables that support different functions, for example, a schema for warehousing operations and another for sales. Some logins may need access to some tables, but not others. You might want to isolate one set of objects within a database from other sets of objects. All of this, and more, can be accomplished using schemas within a database and PostgreSQL supports the use of schema for just these types of functions.<\/p>\n

In the sample database I\u2019ve created as a part of this ongoing series, I created a couple of schemas and organized my tables within them. You can see the database here<\/a> in the CreateDatabase.sql<\/code> script. The rest of the code in this article is in the folder 08_Schema<\/code>.<\/p>\n

Managing Schema<\/h2>\n

A schema is used first and foremost as an organizing mechanism on your database. From there, you can get into using schema to help design security, manage access, and generally control what users can see and do within your database. When you create a blank database, it comes with a default schema, public.<\/p>\n

When you create an object, like a table, it will automatically go into your default schema unless you specify otherwise. By default, all logins to the database have access to the public<\/code> schema (PostgreSQL 15 has changed this default, so now users do not have rights to create objects in the public schema). Other than these default behaviors, the public schema is just another schema in the database and most of the functions and rules we\u2019ll discuss in the rest of the article apply.<\/p>\n

To get stated creating your own schemas, the syntax is very straightforward:<\/p>\n

CREATE SCHEMA mytestschema;<\/pre>\n
This creates a schema called mytestschema<\/code>. To create a table within that schema, you simply use a two part name (schema_name.table_name<\/code>) for the table within the CREATE TABLE<\/code> command like this:<\/p>\n
create table mytestschema.testtable\r\n(id int,\r\nsomevalue varchar(50));<\/pre>\n
It\u2019s the same with any queries too:<\/p>\n
select id from mytestschema.testtable;<\/pre>\n
You can think of the schema as the owner of the table (the owner of the schema is technically the owner of the table). Defining the owner in all your code helps ensure that accidents don\u2019t happen. Because, when you start using schema, you can define the same object names if they\u2019re in different schemas. Keeping object names distinct is a good practice, but sometimes the same name is the best name in different schemas):<\/p>\n
create schema secondschema;\r\n\r\ncreate table secondschema.testtable\r\n(insertdate date,\r\nsomeothervalue varchar(20));<\/pre>\n
This is perfectly valid. If I were to write, what I consider poor code, like this:<\/p>\n
select * from testtable;<\/pre>\n
This likely results in the following error:<\/p>\n
ERROR:  relation \"testtable\" does not exist<\/code><\/p>\n
LINE 2: select * from testtable;<\/code><\/p>\n
Initially, is seems like it\u2019s an error because PostgreSQL can\u2019t figure out which of the two testtable<\/code> tables to pull from. Rather, it\u2019s because logins have a default schema. When I run a query like the one immediately preceding, without a schema identifying where the table lives, PostgreSQL looks in my default search path. If it\u2019s not there, well, that table doesn\u2019t exist. This is true even though, I\u2019ve got two with that name. PostgreSQL doesn\u2019t check other schemas \u201cjust in case.\u201d<\/p>\n
Later in the article, I will discuss how to manage the schema defaults.<\/p>\n
If the schema is empty, you can drop it:<\/p>\n
drop table if exists secondschema.testtable;\r\n\r\ndrop schema if exists secondschema;<\/pre>\n
If I do not drop the table first, an error will occur:<\/p>\n
SQL Error [2BP01]: ERROR: cannot drop schema mytestschema because other objects depend on it<\/code><\/p>\n
  Detail: table mytestschema.testtable depends on schema mytestschema<\/code><\/p>\n
  Hint: Use DROP ... CASCADE to drop the dependent objects too.<\/code><\/p>\n
In the error message, there is a hint for how to get around this. I could rewrite my query like this:<\/p>\n
drop schema if exists mytestschema cascade;<\/pre>\n
The beauty of this is that it will remove all tables, views, etc., within the given schema. That is also the horror of this syntax, that it\u2019s going to remove all the tables, views, etc., without consulting with you in any way.<\/p>\n
There is a default schema in every database that gets created, public. However, it is just the default and like most defaults, it can be changed. In fact, you can even drop the public schema if you so choose. I started this section detailing how to create your own schema because, I think it\u2019s a good practice to organize your data storage into a defined schema that you directly control, as opposed to just accepting the defaults.<\/p>\n
Controlling the Default Search Path<\/h2>\n
In addition to helping you organize your database objects; schema helps you control access to those objects. I haven\u2019t delved into security yet in this series, and it\u2019s likely a little way off still. However, I will talk some about how schema helps you manage security on your database. (My teammate Ryan Booz did recently publish an article on the subject, \u201cPostgreSQL Basics: Roles and Privileges<\/a>\u201d.)<\/p>\n
In this section I want to detail some of the ways you can manage the default schema.<\/p>\n
In the last example of the section above, I showed how you can have duplicate table names between schema, but, that you must reference the schema name to access those tables. However, this isn\u2019t the whole story.<\/p>\n
There\u2019s actually a defined search list for schema that you can see using this query:<\/p>\n
show search_path;<\/pre>\n
If you haven\u2019t changed anything in your server, the default results are:<\/p>\n
\"$user\",public<\/code><\/p>\n
Each user has a schema of their own, like SQL Server. That\u2019s the $user<\/code> schema you see above. However, if you don\u2019t specify the schema, it\u2019ll default to the first one in the search list, public in this case. We can add schema to the search list for the current connection:<\/p>\n
SET search_path TO radio,public;<\/pre>\n
That will not only add the radio<\/code> schema to the the search_path<\/code>, it will rearrange the order on the search_path<\/code> so that the radio<\/code> schema is searched before the public<\/code> schema. If you disconnect and come back to the connection, you will have to reset the path when using the SET<\/code> command.<\/p>\n
If you want to make the changes to the path the default, you can use the ALTER ROLE<\/code> to set any role to have a specific search path. For example:<\/p>\n
ALTER ROLE scaryDba SET search_path = 'radio,public,$user';<\/pre>\n
If you want to set the default for the server\\cluster\\database, you can modify the search_path<\/code> in the postgressql.cnf<\/code> file, or you can use:<\/p>\n
ALTER ROLE ALL SET search_path = '$user';<\/pre>\n
This will not override the individual path\u2019s set but will make every login that does not override the search path need to specify the schema name with referencing any object. (Which as noted, is a best practice.)<\/p>\n
Ownership and Basic Privileges<\/h2>\n
When you create a schema, you can define a schema owner as other than the login that is running the command:<\/p>\n
CREATE SCHEMA secureschema AUTHORIZATION radio_admin;<\/pre>\n
A schema I haven\u2019t created yet, secureschema<\/code>, would be created with the owner being the radio_admin<\/code> login role (also not yet defined because I am not digging into security yet). That will ensure that only that the radio_admin<\/code> login, and any accounts defined as superuser, of course, can work within that schema.<\/p>\n
You can also control behaviors on schema. For example, since I have set up independent schema in this database and intend to use it in that manner, I can revoke access for all logins to create objects on the public schema (This is only necessary in Postgres 14 and earlier, in 15 create is not granted by default.):<\/p>\n
REVOKE CREATE ON SCHEMA public FROM PUBLIC;<\/pre>\n
This is using two different meanings of the word \u201cpublic<\/code>\u201d. In the first, \u2018public\u2019<\/code>, we\u2019re referring to the schema of that name. In the second, \u2018PUBLIC\u2019<\/code>, we\u2019re talking about the role that contains all users within the database. This is a shorthand mechanism to make sure no one accidentally puts things into the public schema. I\u2019d say it\u2019s a good practice to follow if you\u2019re going to use other schemas, especially if you\u2019re using them to help properly secure your database.<\/p>\n
You can grant various privileges between schema and users such that a given user can read data from tables within a schema, but not modify the data contained in the tables (read only access). In this way, you can combine multiple types of data within one database but isolate them from each other as needed. This is a principal reason for using schema in your database.<\/p>\n
If you\u2019re not isolating storage and access between schema, it may make less sense to use a schema other than public in the first place. However, most applications have varying levels of access they would like to manage, and schema will lend themselves to appropriately implementing that type of security. If security isn\u2019t a concern, using schema names instead of putting all objects in the public schema can be advantageous for documentation as well.<\/p>\n
Conclusion<\/h2>\n
Schemas are containers that allow you to segment objects and security at a lower level than a database. Using schemas other than public has good benefits. In PostgreSQL there are several methods of setting the default schema if your users are averse to using two-part names.<\/p>\n
If you are familiar with schemas from SQL Server, the core functionality of schema is roughly the same as in SQL Server. However, there is added functionality like the ability to control a search list changes the types of behaviors you can control within PostgreSQL.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
An important aspect of building and managing a database is organizing the objects within your database. You may have tables that support different functions, for example, a schema for warehousing operations and another for sales. Some logins may need access to some tables, but not others. You might want to isolate one set of objects……<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143534],"tags":[158977,158976,159066],"coauthors":[6785],"class_list":["post-96535","post","type-post","status-publish","format-standard","hentry","category-featured","category-postgresql","tag-learningpostgresqlwithgrant","tag-planetpostgresqlgrantfritchey","tag-postgresql-101-webinar-sidebar"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96535","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=96535"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96535\/revisions"}],"predecessor-version":[{"id":102162,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96535\/revisions\/102162"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=96535"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=96535"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=96535"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=96535"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}