[BUGS] BUG #6264: Superuser does not have inherent Replication permission
The following bug has been logged online: Bug reference: 6264 Logged by: Keith Fiske Email address: ke...@omniti.com PostgreSQL version: 9.1.1 Operating system: Debian Description:Superuser does not have inherent Replication permission Details: Was testing out an upgrade from 9.0.4 to 9.1.1 and ran across either some lack of clarity in the documentation or a bug in the superuser privilege. I had a streaming replication setup and after the upgrade and setting up the new slave, the slave would no longer connect to the master for SR. Looking into this more, I saw that 9.1 has a new Replication permission to allow non-superusers the ability to be replication users. The documentation says replication is inherent to a superuser. After testing several times, I can assure you it is not. The old replication user from 9.0.4 which was carried through the upgrade kept its superuser privileges, but was unable to be used for replication until I explicitly granted it the Replication permission. Brand new roles created initially as superusers are explicitly given the separate replication permission. If you create a user as a NONsuperuser, then later ALTER them to be one, they will NOT have the replication permission and cannot be used as a replication user until you explicitly grant that permission. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6264: Superuser does not have inherent Replication permission
On Sat, Oct 22, 2011 at 11:49 AM, Tom Lane wrote: > "Keith Fiske" writes: >> The documentation says replication is inherent to a superuser. > > What it actually says is that superusers get the replication privilege > by default --- but you can create a superuser without that. If you > see a place that says something different, please point it out > specifically so we can improve it. From the documentation "...If not specified, NOREPLICATION is the default for all roles except superusers..." From the 9.1 release notes: "...Previously only superusers could initiate replication connections; superusers still have this permission by default..." Reading through all the other options, none of them state anything special about superuser defaults. As stated above, if one were first reading this, one would assume that a superuser would have replication permission as default, no matter if it was given superuser via ALTER ROLE or CREATE ROLE. If you're going to start saying which permissions a superuser has and doesn't have by default, you should be consistent with all of them and clearly state how and when they are given. Ex: superusers inherently have CREATEROLE and CREATEDB, but not LOGIN. And especially reading the release notes the use of the word "still" would indicate that it works the same as it did in 9.0. It's what caused my much of my confusion in this whole thing. When I upgraded my 9.0.4 system to 9.1 and couldn't get streaming replication to work, I saw the above and began to assume this was a regression of the "replication" role name issue since it gave the same error that that bug gave and things were working fine in 9.0.4. It was only after I created a brand new superuser and then did a \du that I noticed the replication permission was tacked on separately and only during role creation. An upgrade from 9.0 to 9.1 does not account for SR users and the release notes should probably reflect this, NOT state that superusers still have the permission by default. They don't. > >> After testing >> several times, I can assure you it is not. > > Please show your test case. It looks to me like it works as expected: > > regression=# create user foo superuser; > CREATE ROLE > regression=# create user foo2 superuser noreplication; > CREATE ROLE > regression=# select rolname,rolsuper,rolreplication from pg_authid ; > rolname | rolsuper | rolreplication > --+--+ > postgres | t | t > foo | t | t > foo2 | t | f > (3 rows) > > If you create a non-super user then ALTER them to be one, they don't have replication as default. This either needs to be clarified in the documentation or the ALTER should give the same defaults as the CREATE. Whether it's inherent like CREATEDB or a separate permission, I don't think matters and is a whole other argument. But things should be consistent in some manner. >> If you create a user as a NONsuperuser, >> then later ALTER them to be one, they will NOT have the replication >> permission and cannot be used as a replication user until you explicitly >> grant that permission. > > That doesn't sound to me like a bug. These flags are independent, we > just provide a certain default at role creation time. > That is not what the documentation as read would lead people to believe. I'd be more than happy to help with clarifying the documentation myself if needed. Just let me know how. > regards, tom lane > -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6581: pg_dumpall --no-tablespaces option still sets default tablespace
On Wed, Apr 11, 2012 at 2:21 PM, Tom Lane wrote: > ke...@omniti.com writes: >> Using the --no-tablespaces option for pg_dumpall correctly stops tablespace >> creation and each individual object from having its tablespace set. But if >> the original database had its default tablespace changed, this is still >> added at the beginning of the dump file: > >> ALTER DATABASE dbname SET default_tablespace TO 'old_db_tablespace'; > > Well, what the --no-tablespaces option does is suppress actions that are > related to tablespaces. However, ALTER DATABASE SET options are just > dumped out by generic code (likewise for ALTER ROLE SET, function SET > options, yadda yadda). I guess we could insert a hack that looked for > tablespace-related options and didn't print them, but it seems like it > would be a wart not a feature. > > Also, the purpose of the option is to let you reload the dump into an > installation that hasn't got the same (or any) tablespaces. ALTER SET > commands won't prevent that, they'll at worst result in some NOTICEs > during the reload. > > In short, I'm not real sure this should be considered a bug. > > regards, tom lane Agreed it's not a serious bug and doesn't stop the restore during a basic replay with psql. But besides the warning during restore, it also causes warnings after the reload when you connect to the database. postgres=# \c dbname WARNING: invalid value for parameter "default_tablespace": "alldata1" DETAIL: Tablespace "alldata1" does not exist. You are now connected to database "dbname" as user "postgres". dbname=# Again, easily fixed with running another ALTER after the database is up and running. But figured I'd report it. -- Keith Fiske Database Administrator OmniTI Computer Consulting, Inc. 443.325.1357 x251 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7716: Only owner can drop trigger?
On Fri, Nov 30, 2012 at 7:02 PM, Jeff Davis wrote: > On Fri, 2012-11-30 at 23:41 +, ke...@omniti.com wrote: >> The following bug has been logged on the website: >> >> Bug reference: 7716 >> Logged by: Keith Fiske >> Email address: ke...@omniti.com >> PostgreSQL version: 9.2.1 >> Operating system: Any >> Description: >> >> This is documented in the DROP TRIGGER section of the documentation, but it >> doesn't seem to make any sense. If a role has permission to create a >> trigger, why can that same role not have the same permissions to drop it, >> even if that role doesn't own the table? > > My guess is that it's to prevent someone from dropping a trigger created > by someone else. > > I agree that it's inconsistent. I'm not sure why they added the separate > "TRIGGER" privilege in the first place, but it was done more than 10 > years ago. > > Regards, > Jeff Davis > > > So then don't give that other role trigger permissions if you don't want them to be able to drop a trigger. I'm actually thankful for the trigger permission since it allows the extension I'm working on to create triggers on tables the role doesn't own. But now it only half-works because it can't clean up after itself when the trigger needs to be dropped. Keith -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
