#Postgresql create database access privileges manual
So now I understood why I had template1, but what was the deal with template0? Why did I need two of these things? Back to the manual I went! Turns out, template0 can be useful to us in a couple ways. Unsurprisingly, my new database had the table, row, and extension that I added to the template. 2 | public | connect to other PostgreSQL databases from within a database
example =# \ d List of relations Schema | Name | Type | Owner -+-+-+- public | test | table | marylee example =# select * from test data - Some data ( 1 row ) example =# \ dx dblink List of installed extensions Name | Version | Schema | Description -+-+-+- dblink | 1. template1 =# create database example CREATE DATABASE template1 =# \ c example You are now connected to database "example" as user "marylee". Then I created a new database and checked to see that my changes were there. template1 =# create table test ( data varchar ) CREATE TABLE template1 =# insert into test values ( 'Some data' ) INSERT 0 1 template1 =# create extension dblink CREATE EXTENSION postgres =# \ c template1 You are now connected to database "template1" as user "marylee". I connected to template1, and then added a table with one row, and an extension. Since template1 allows connections, this meant that I could go into template1 and make a bunch of changes, and expect those changes to appear on any new databases I created. I discovered that, by default, when I create a new database ( create database example ), Postgres is actually making a copy of template1. Unsure of where to look next, I decided to take the long-standing advice of a colleague and read the dang manual. Ok fine, so now I knew that I had two identical databases, one allowing connections, and one with connections disabled. postgres =# \ c template0 FATAL : database "template0" is not currently accepting connections Previous connection kept Being the person that I am, I couldn't help myself I tried to connect. UTF - 8 datistemplate | t datallowconn | f datconnlimit | - 1 datlastsysoid | 13266 datfrozenxid | 548 datminmxid | 1 dattablespace | 1663 datacl | postgres =# select datname, datallowconn from pg_database where datname like 'template%' datname | datallowconn -+- template0 | f template1 | tĪha! Found a difference: template1 allows database connections, but template0 does not. postgres =# select * from pg_database where datname like 'template%' -+ - datname | template0 datdba | 10 encoding | 6 datcollate | en_US. Since this wasn't helpful enough, I decided to pull up the database information with pg_database. UTF - 8 | = c / postgres + postgres = CTc / postgres UTF - 8 | = c / postgres + postgres = CTc / postgres template1 | postgres | UTF8 | en_US. UTF - 8 | template0 | postgres | UTF8 | en_US. postgres =# \ l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -+-+-+-+-+- postgres | postgres | UTF8 | en_US.
My first plan of attack was to just list them and see if I could find a difference right away, but everything looked exactly the same. I decided to seize the moment and launched myself into an exploration of what the template databases are meant to do, and why the heck there are two of them.
I was recently poking around the psql terminal, and noticed something that I had never had to deal with before: the Postgres template databases. PostgreSQL Exploring the Default Postgres Template Databases
0 kommentar(er)
