PostgreSQL 具有createdb的用户无法创建数据库的原因（之一）
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=>
从上面提示的是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=>
后经查证，原因是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=#
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
参看上面的图根因是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=>
这里在PG16也做了相关的测试，情况是一样的。
本文分享自 AustinDatabases 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ，欢迎热爱写作的你一起参与！
腾讯云开发者
