Re: create database with template doesn't copy database ACL
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 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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: create database with template doesn't copy database ACL
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 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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee 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 name Database-level configuration parameters (set via ) are not copied from the template -database. +linkend="sql-alterdatabase"/>) and database-level permissions (set via +) are not copied from the template database.
Re: create database with template doesn't copy database ACL
On Mon, Jun 15, 2020 at 12:14:55AM -0400, Tom Lane wrote: > Bruce Momjian 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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: create database with template doesn't copy database ACL
Bruce Momjian 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
Re: create database with template doesn't copy database ACL
On Sun, Jun 14, 2020 at 11:24:56PM -0400, Tom Lane wrote: > Bruce Momjian 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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Re: create database with template doesn't copy database ACL
Bruce Momjian 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
Re: create database with template doesn't copy database ACL
On Sun, Jun 14, 2020 at 07:26:13AM +, 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 +, 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
