The best way, really, is for the web app to have no right to create or modify tables at all. It should only have the rights to do what it needs to do to perform its day to day functions, not administration.
In particular, the web app must not run as a superuser and should preferably not own the database or tables.
If the webapp has built-in administration functions for changing tables when it's upgraded, etc, it should do so via a separate set of credentials and allow you to be prompted to enter those credentials each time you perform an upgrade. Or let you create the file with the admin credentials, run the upgrade, and delete it again.
If you can't fix the webapp to use different credentials for schema changes, then it might as well be the owner of the database and tables. Just don't make it a superuser!
If you can fix the app so it uses different credentials for schema changes, then its regular non-schema-change user should not own the tables or database. It should have only CONNECT and TEMPORARY rights on the database, USAGE rights on the schema(s), and the required rights on each table GRANT
ed to it. Just don't forget to REVOKE ALL FROM public ON DATABASE mydb;
and REVOKE ALL FROM public ON SCHEMA public;
.
The documentation about ALTER DEFAULT PRIVILEGES
tells you a possible reason - it is not that clearly described, though.
Let's see, what is said:
You can change default privileges only for objects that will be
created by yourself or by roles that you are a member of.
This means that the default privileges defined by this statement applies only to objects that are created by you (or the role you are a member of). Let's see this in action!
alice
's schema
First, we are logged in as alice
. Then, in a newly created schema, we create a table and grant some rights to bob
:
SELECT current_user;
current_user
──────────────
alice
SHOW search_path ;
search_path
──────────────
test, public
CREATE SCHEMA alicetest;
ALTER DEFAULT PRIVILEGES
FOR ROLE alice
IN SCHEMA alicetest
GRANT ALL ON TABLES TO alice;
GRANT SELECT ON alicetest.a TO bob;
-- this I do only for showing the privileges -
-- the owner has by default ALL and is not shown by \dp
\dp alicetest.a
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
───────────┼──────┼───────┼─────────────────────┼──────────────────────────
alicetest │ a │ table │ alice=arwdDxt/alice↵│
│ │ │ bob=r/alice │
alice
now has all rights on her table, as expected.
bob
' table in the same schema
Now, after obtaining access to this schema, bob
tries to create a table:
SELECT current_user;
current_user
──────────────
bob
CREATE TABLE alicetest.b (id integer);
GRANT SELECT ON alicetest.b TO alice;
\dp alicetest.b
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
───────────┼──────┼───────┼───────────────────┼──────────────────────────
alicetest │ b │ table │ bob=arwdDxt/bob ↵│
│ │ │ alice=r/bob │
As you can see, despite creating the table in alice
's schema where she set the default privileges, bob
's table doesn't have all those permissions. This happens because alice
is not a member of bob
.
Let's check this membership thing, too, and try to define default privileges by alice
again, this time for another role:
ALTER DEFAULT PRIVILEGES
FOR ROLE charlie
IN SCHEMA alicetest
GRANT ALL ON TABLES TO alice;
ERROR: must be member of role "charlie"
So, some mighty enough user grants her a membership in charlie
, then she tries again, with success:
ALTER DEFAULT PRIVILEGES
FOR ROLE charlie
IN SCHEMA alicetest
GRANT ALL ON TABLES TO alice;
ALTER DEFAULT PRIVILEGES
charlie
's round
Then charlie
creates a new table:
CREATE TABLE alicetest.c (id integer);
And the privileges:
\dp alicetest.c
Access privileges
Schema │ Name │ Type │ Access privileges │ Column access privileges
───────────┼──────┼───────┼─────────────────────────┼──────────────────────────
alicetest │ c │ table │ charlie=arwdDxt/charlie↵│
│ │ │ alice=arwdDxt/charlie │
As you see, alice
, as a member of charlie
, gets her access to this table.
To answer your question,
I guess you defined the default privileges for yourself (alice
in the example), but you developers act as a bunch of bob
s here, not getting the necessary privileges. One way to get around this (as we do it at work) is to do a
SET ROLE TO schema_owner;
every time before creating a new object in the schema. This should be a role that all developers are a member of (otherwise you'd get an error).
NOTE that \dp
is a psql
command.
Best Answer
There is no need to grant the
CONNECT
role. In 10.2, Oracle finally reduced the set of privileges assigned to that role to justCREATE SESSION
but in previous versions, that role has many more privileges than the name would imply.It would be more secure to create one users-- one that owns the tables, procedures, etc. but that does not have
CREATE SESSION
privileges and one that has appropriate privileges on the various objects that the application can use to connect to the database. That allows you to do things like prevent the application user from dropping tables or from deleting data from logging tables.Additionally, the owner of the tables is going to need to be granted quota on whatever tablespace or tablespaces that user's tables are going to be created in. You could grant the owner the
UNLIMITED TABLESPACE
privilege but it is more secure to grant them a smaller quota on whatever tablespaces they actually need to use.