Thread: Re: create database with template doesn't copy database ACL
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
Attachment
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