PostgreSQL 具有createdb的用户无法创建数据库的原因（之一）

最近有人问我，PostgreSQL的模板数据库的问题，说在模板数据库中打入了一些表和存储过程，并且还调整了extension的部分，但打入完毕后，通过模板数据库来创建数据库失败了,ERROR:  permission denied to copy database "template1",但他确认他有createdb的数据库权限。

首先模板数据库是PostgreSQL为在实例下快速扩展新数据库时，将PostgreSQL中复杂的配置带到新数据库中的一项功能。这项功能在建立新的PG逻辑库中是非常方便的，尤其在PG 数据库中包含了一些DBA 建立的便于PG查询系统信息的VIEW，或者运维存储过程，或特殊的EXTENSION后，在建立新数据库都需要带上这些东西，如果重新部署将变得十分的繁琐。

在使用template 模板数据库时，需要注意PG 有两个模板数据库，template0, template1,这里默认即使在改变模板数据库的情况下，只能动template1，而不能动template0，主要的原因是在操作的过程中如果将模板数据库设置错误，可以通过删除模板数据库，在重新创建的方法来将错误的信息消除，基本的原理是，必须要保持一个干净的template数据库。

下面复原一下那个人的情况，他提示的是superuser 可以创建数据库并从template1将里面的信息都带走，但其他的用户有createdb权限的不可以。

登录后复制

[postgres@postgresql13 ~]$ psql -h 192.168.198.100 -p 5432 -U test_t 
Password for user test_t: 
psql (13.8)
Type "help" for help.

postgres=> create database test;
ERROR:  permission denied to create database
postgres=> exit 
[postgres@postgresql13 ~]$ psql 
psql (13.8)
Type "help" for help.

postgres=# alter user test_t CREATEDB;
ALTER ROLE
postgres=# exit 
[postgres@postgresql13 ~]$ psql -h 192.168.198.100 -p 5432 -U test_t 
Password for user test_t: 
psql (13.8)
Type "help" for help.

postgres=> create database test;
ERROR:  permission denied to copy database "template1"
postgres=>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.

从上面提示的是test_t 没有权限创建数据库，但test_t 的确有createdb的权限。

登录后复制

postgres=> \du 
                                      List of roles
    Role name    |                         Attributes                         | Member of 
-----------------+------------------------------------------------------------+-----------
 pgadmin         | Superuser                                                  | {}
 pgbackrest_user | Superuser                                                  | {}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test            |                                                            | {}
 test_t          | Create DB                                                  | {}
 vacuum_user     | Superuser                                                  | {}

postgres=>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.

后经查证，原因是template1数据库被变动了，导致非superuser的用户无法建立数据库的问题，

登录后复制

postgres=# select * from pg_database;
  oid  |  datname  | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfroz
enxid | datminmxid | dattablespace |               datacl                
-------+-----------+--------+----------+------------+----------+---------------+--------------+--------------+---------------+--------
------+------------+---------------+-------------------------------------
 13580 | postgres  |     10 |        6 | C          | C        | f             | t            |           -1 |         13579 |        
  478 |          1 |          1663 | 
 13579 | template0 |     10 |        6 | C          | C        | t             | f            |           -1 |         13579 |        
  478 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
     1 | template1 |     10 |        6 | C          | C        | f             | t            |           -1 |         13579 |        
  478 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
(3 rows)

postgres=# update pg_database set datistemplate = 't' where datname = 'template1';
UPDATE 1
postgres=#

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.

登录后复制

Type "help" for help.

postgres=> create database test;
ERROR:  permission denied to copy database "template1"
postgres=> 
postgres=> 
postgres=> create database test;
CREATE DATABASE
postgres=> \du

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

参看上面的图根因是POSTGRESQL的template1的数据库中的datistemplate被修改成f，导致的问题。在POSTGRESQL 中对于数据库有明确的区分，是模板数据库还是非模板的数据库，当template1被取消了标记为模板数据库的情况下，默认进行create database 具有权限的普通用户的操作会失败。另外有同学提出高版本的数据库不存在这个问题

登录后复制

postgres@pg16:~$ psql -h 192.168.198.120 -p 5432 -U test_t 
Password for user test_t: 
psql (16.0)
Type "help" for help.

postgres=> create database test;
2024-07-22 09:10:13.431 UTC [186104] ERROR:  permission denied to copy database "template1"
2024-07-22 09:10:13.431 UTC [186104] STATEMENT:  create database test;
ERROR:  permission denied to copy database "template1"
postgres=> select version();
                                             version                                              
--------------------------------------------------------------------------------------------------
 PostgreSQL 16.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)

postgres=>

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.

这里在PG16也做了相关的测试，情况是一样的。

Austindatabases 公众号，主要围绕数据库技术（PostgreSQL, MySQL, Mongodb, Redis, SqlServer，PolarDB, Oceanbase 等）和职业发展，国外数据库大会音译,国外大型IT信息类网站文章翻译，等，希望能和您共同发展。

原创作者: u_14150796 转载于: https://blog.51cto.com/u_14150796/11729468