pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE ANY TABLE privileges, etc
4+ messages / 3 participants
[nested] [flat]
* CREATE ANY TABLE privileges, etc
@ 2017-08-15 13:15 Don Seiler <don@seiler.us>
0 siblings, 1 reply; 4+ messages in thread
From: Don Seiler @ 2017-08-15 13:15 UTC (permalink / raw)
To: pgsql-admin
Good morning,
I've had a request to create a user that can create tables and schemas
anywhere in this dev database. I know I could just make that user a
SUPERUSER but wondering if there's anything short of that that seems more
responsible. Again, this would only be in DEV so I'm not nearly as
concerned giving them a superuser, assuming they haven't shown a track
record of reckless behavior.
I'm thinking of privileges like Oracle's CREATE ANY TABLE, etc. vs granting
them the DBA role.
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE ANY TABLE privileges, etc
@ 2017-08-15 14:08 Keith <keith@keithf4.com>
parent: Don Seiler <don@seiler.us>
0 siblings, 2 replies; 4+ messages in thread
From: Keith @ 2017-08-15 14:08 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin
On Tue, Aug 15, 2017 at 9:15 AM, Don Seiler <don@seiler.us> wrote:
> Good morning,
>
> I've had a request to create a user that can create tables and schemas
> anywhere in this dev database. I know I could just make that user a
> SUPERUSER but wondering if there's anything short of that that seems more
> responsible. Again, this would only be in DEV so I'm not nearly as
> concerned giving them a superuser, assuming they haven't shown a track
> record of reckless behavior.
>
> I'm thinking of privileges like Oracle's CREATE ANY TABLE, etc. vs
> granting them the DBA role.
>
> Don.
>
> --
> Don Seiler
> www.seiler.us
>
Easiest thing would be to grant ownership of that database to the user.
That won't grant privileges to any existing objects, but will allow new
schemas to be made by that user, which will in turn allow objects to be
made in those schema that user creates.
Otherwise, there is the GRANT CREATE ON DATABASE ... command which will
essentially do the same thing and allow schemas to be created.
https://www.postgresql.org/docs/9.6/static/sql-grant.html
Keith
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE ANY TABLE privileges, etc
@ 2017-08-15 14:30 Don Seiler <don@seiler.us>
parent: Keith <keith@keithf4.com>
1 sibling, 0 replies; 4+ messages in thread
From: Don Seiler @ 2017-08-15 14:30 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: pgsql-admin
On Tue, Aug 15, 2017 at 9:08 AM, Keith <keith@keithf4.com> wrote:
>
>
> Easiest thing would be to grant ownership of that database to the user.
> That won't grant privileges to any existing objects, but will allow new
> schemas to be made by that user, which will in turn allow objects to be
> made in those schema that user creates.
>
I ended up granting the role that owns the DB to the user. Sounds similar
to what you suggested? I'm waiting to hear from the dev if it does what he
needs.
Thanks,
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CREATE ANY TABLE privileges, etc
@ 2017-08-15 19:50 Jerry Sievers <gsievers19@comcast.net>
parent: Keith <keith@keithf4.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jerry Sievers @ 2017-08-15 19:50 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: Don Seiler <don@seiler.us>; pgsql-admin
Keith <keith@keithf4.com> writes:
> On Tue, Aug 15, 2017 at 9:15 AM, Don Seiler <don@seiler.us> wrote:
>
> Good morning,
>
> I've had a request to create a user that can create tables and
> schemas anywhere in this dev database. I know I could just make
> that user a SUPERUSER but wondering if there's anything short of
> that that seems more responsible. Again, this would only be in
> DEV so I'm not nearly as concerned giving them a superuser,
> assuming they haven't shown a track record of reckless behavior.
>
> I'm thinking of privileges like Oracle's CREATE ANY TABLE, etc.
> vs granting them the DBA role.
>
> Don.
>
> --
> Don Seiler
> www.seiler.us
>
>
> Easiest thing would be to grant ownership of that database to the
> user. That won't grant privileges to any existing objects, but will
> allow new schemas to be made by that user, which will in turn allow
> objects to be made in those schema that user creates.
Hmmm?
Often DB objects will be owned by whichever owner role...
Thus, granting that role to some other user does indeed give that user
full access to same objects as per whatever grants are established on
them to the owner.
By default, owner has all privileges but this can be changed.
HTH
> Otherwise, there is the GRANT CREATE ON DATABASE ... command which
> will essentially do the same thing and allow schemas to be created.
>
> https://www.postgresql.org/docs/9.6/static/sql-grant.html
>
> Keith
>
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-08-15 19:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-15 13:15 CREATE ANY TABLE privileges, etc Don Seiler <don@seiler.us>
2017-08-15 14:08 ` Keith <keith@keithf4.com>
2017-08-15 14:30 ` Don Seiler <don@seiler.us>
2017-08-15 19:50 ` Jerry Sievers <gsievers19@comcast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox