create database with template doesn't copy ACL
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/sql-createdatabase.html
Description:
My understanding is that not copying the ACL is the (currently) expected
behavior when issuing CREATE DATABASE newdb WITH TEMPLATE my_tmpl;
It would be useful for the documentation to note this caveat.
On Fri, Jun 5, 2020 at 02:31:34PM +0000, PG Doc comments form wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/sql-createdatabase.html
Description:My understanding is that not copying the ACL is the (currently) expected
behavior when issuing CREATE DATABASE newdb WITH TEMPLATE my_tmpl;
It would be useful for the documentation to note this caveat.
Uh, what ACLs are not copied?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Fri, Jun 12, 2020 at 05:29:51PM -0400, Bruce Momjian wrote:
On Fri, Jun 5, 2020 at 02:31:34PM +0000, PG Doc comments form wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/sql-createdatabase.html
Description:My understanding is that not copying the ACL is the (currently) expected
behavior when issuing CREATE DATABASE newdb WITH TEMPLATE my_tmpl;
It would be useful for the documentation to note this caveat.Uh, what ACLs are not copied?
The ACL on the database itself. For example:
postgres@postgres[[local]#9655]=# CREATE DATABASE acl_template WITH IS_TEMPLATE = 1;
CREATE DATABASE
postgres@postgres[[local]#9655]=# REVOKE ALL ON DATABASE acl_template FROM PUBLIC;
REVOKE
postgres@postgres[[local]#9655]=# CREATE DATABASE acl_test WITH TEMPLATE = acl_template;
CREATE DATABASE
postgres@postgres[[local]#9655]=# SELECT datname, datacl FROM pg_database WHERE datname LIKE 'acl%';
datname | datacl
--------------+-------------------------
acl_template | {postgres=CTc/postgres}
acl_test |
(2 rows)
Here, the ACL on the new acl_test database does NOT match the ACL on the
acl_template database upon which it is based.
Hope this helps,
--Joe
On Sun, Jun 14, 2020 at 07:26:13AM +0000, Joseph Nahmias wrote:
On Fri, Jun 12, 2020 at 05:29:51PM -0400, Bruce Momjian wrote:
On Fri, Jun 5, 2020 at 02:31:34PM +0000, PG Doc comments form wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/11/sql-createdatabase.html
Description:My understanding is that not copying the ACL is the (currently) expected
behavior when issuing CREATE DATABASE newdb WITH TEMPLATE my_tmpl;
It would be useful for the documentation to note this caveat.Uh, what ACLs are not copied?
The ACL on the database itself. For example:
postgres@postgres[[local]#9655]=# CREATE DATABASE acl_template WITH IS_TEMPLATE = 1;
CREATE DATABASE
postgres@postgres[[local]#9655]=# REVOKE ALL ON DATABASE acl_template FROM PUBLIC;
REVOKE
postgres@postgres[[local]#9655]=# CREATE DATABASE acl_test WITH TEMPLATE = acl_template;
CREATE DATABASE
postgres@postgres[[local]#9655]=# SELECT datname, datacl FROM pg_database WHERE datname LIKE 'acl%';
datname | datacl
--------------+-------------------------
acl_template | {postgres=CTc/postgres}
acl_test |
(2 rows)Here, the ACL on the new acl_test database does NOT match the ACL on the
acl_template database upon which it is based.
[I am moving this to the hackers list because I am not clear if this is a
documentation problem or a bug.]
Effectively, we have three levels of objects:
1 global, cluster-wide, e.g., tablespaces, users
2 database attributes, e.g., database encoding, database tablespace
3 objects inside of databases
We don't clearly describe it that way though. Looking at the test:
psql -a <<END
ALTER DATABASE acl_template WITH IS_TEMPLATE false;
DROP DATABASE IF EXISTS acl_template;
DROP DATABASE IF EXISTS acl_test;
CREATE DATABASE acl_template WITH IS_TEMPLATE = 1;
REVOKE ALL ON DATABASE acl_template FROM PUBLIC;
CREATE DATABASE acl_test WITH TEMPLATE = acl_template;
SELECT datname, datacl FROM pg_database WHERE datname LIKE 'acl%';
datname | datacl
--------------+-------------------------
acl_template | {postgres=CTc/postgres}
acl_test | (null)
END
$ pg_dump acl_template | grep CONNECT
$ pg_dump --create acl_template | grep CONNECT
REVOKE CONNECT,TEMPORARY ON DATABASE acl_template FROM PUBLIC;
$ pg_dumpall --globals-only | grep CONNECT
$ pg_dumpall | grep CONNECT
REVOKE CONNECT,TEMPORARY ON DATABASE acl_template FROM PUBLIC;
it appears database CONNECT and TEMPORARY are treated as database
attributes (2) because they are only dumped when the database is being
created, not by pg_dumpall --globals-only(1) or pg_dump(3).
I am unclear if we should be copying the CONNECT and TEMPORARY
attributes or documenting that CREATE DATABASE does not copy them.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Bruce Momjian <bruce@momjian.us> writes:
I am unclear if we should be copying the CONNECT and TEMPORARY
attributes or documenting that CREATE DATABASE does not copy them.
We should absolutely not copy them.
As an example, it'd make sense for an admin to revoke CONNECT on a
template database, just to help ensure that nobody modifies it.
If that propagated to every created database, it would be a complete
fail.
Moreover, since the ACLs of an object depend quite a bit on who the owner
is, it'd make no sense to copy them to a new object that has a different
owner. The granted-by fields would be wrong, if nothing else.
In practice, CREATE DATABASE never has copied any database-level property
of the template DB, only its contents. (Well, I guess it copies encoding
and collation by default, but those are descriptive of the contents.)
regards, tom lane
On Sun, Jun 14, 2020 at 11:24:56PM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
I am unclear if we should be copying the CONNECT and TEMPORARY
attributes or documenting that CREATE DATABASE does not copy them.We should absolutely not copy them.
As an example, it'd make sense for an admin to revoke CONNECT on a
template database, just to help ensure that nobody modifies it.
If that propagated to every created database, it would be a complete
fail.Moreover, since the ACLs of an object depend quite a bit on who the owner
is, it'd make no sense to copy them to a new object that has a different
owner. The granted-by fields would be wrong, if nothing else.In practice, CREATE DATABASE never has copied any database-level property
of the template DB, only its contents. (Well, I guess it copies encoding
and collation by default, but those are descriptive of the contents.)
Well, I thought we copied everything except things tha can be specified
as different in CREATE DATABASE, though I can see why we would not copy
them. Should we document this or issue a notice about not copying
non-default database attributes?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Bruce Momjian <bruce@momjian.us> writes:
Well, I thought we copied everything except things tha can be specified
as different in CREATE DATABASE, though I can see why we would not copy
them. Should we document this or issue a notice about not copying
non-default database attributes?
We do not need a notice for behavior that (a) has stood for twenty years
or so, and (b) is considerably less broken than any alternative would be.
If you feel the docs need improvement, have at that.
regards, tom lane
On Mon, Jun 15, 2020 at 12:14:55AM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Well, I thought we copied everything except things tha can be specified
as different in CREATE DATABASE, though I can see why we would not copy
them. Should we document this or issue a notice about not copying
non-default database attributes?We do not need a notice for behavior that (a) has stood for twenty years
or so, and (b) is considerably less broken than any alternative would be.
If you feel the docs need improvement, have at that.
Well, I realize it has been this way for a long time, and that no one
else has complained, but there should be a way for people to know what
is being copied from the template and what is not. Do we have a clear
description of what is copied and skipped?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Mon, Jun 15, 2020 at 10:10:32AM -0400, Bruce Momjian wrote:
On Mon, Jun 15, 2020 at 12:14:55AM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Well, I thought we copied everything except things tha can be specified
as different in CREATE DATABASE, though I can see why we would not copy
them. Should we document this or issue a notice about not copying
non-default database attributes?We do not need a notice for behavior that (a) has stood for twenty years
or so, and (b) is considerably less broken than any alternative would be.
If you feel the docs need improvement, have at that.Well, I realize it has been this way for a long time, and that no one
else has complained, but there should be a way for people to know what
is being copied from the template and what is not. Do we have a clear
description of what is copied and skipped?
We already mentioned that ALTER DATABASE settings are not copied, so the
attached patch adds a mention that GRANT-level permissions are not
copied either.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Attachments:
template.difftext/x-diff; charset=us-asciiDownload
diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml
index 504c1b0224..d116b321bc 100644
--- a/doc/src/sgml/ref/create_database.sgml
+++ b/doc/src/sgml/ref/create_database.sgml
@@ -236,8 +236,8 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
<para>
Database-level configuration parameters (set via <xref
- linkend="sql-alterdatabase"/>) are not copied from the template
- database.
+ linkend="sql-alterdatabase"/>) and database-level permissions (set via
+ <xref linkend="sql-grant"/>) are not copied from the template database.
</para>
<para>
On Tue, Jun 16, 2020 at 06:10:54AM -0400, Bruce Momjian wrote:
On Mon, Jun 15, 2020 at 10:10:32AM -0400, Bruce Momjian wrote:
On Mon, Jun 15, 2020 at 12:14:55AM -0400, Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Well, I thought we copied everything except things tha can be specified
as different in CREATE DATABASE, though I can see why we would not copy
them. Should we document this or issue a notice about not copying
non-default database attributes?We do not need a notice for behavior that (a) has stood for twenty years
or so, and (b) is considerably less broken than any alternative would be.
If you feel the docs need improvement, have at that.Well, I realize it has been this way for a long time, and that no one
else has complained, but there should be a way for people to know what
is being copied from the template and what is not. Do we have a clear
description of what is copied and skipped?We already mentioned that ALTER DATABASE settings are not copied, so the
attached patch adds a mention that GRANT-level permissions are not
copied either.
Patch applied to all supported versions. Thanks for the discussion.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee