First, connect to your database cluster as the admin user, doadmin , by passing the cluster's connection string to psql . This brings you into the interactive shell for PostgreSQL, which changes your command prompt to defaultdb=> . From here, connect to the database that you want to modify the user's privileges on.
Another way to do this is to use the information_schema schema and query the table_privileges table as: $ SELECT * FROM information_schema. table_privileges LIMIT 5; The above query will show detailed information about user privileges on databases as well as tables.
All commands must be executed while connected to the right database cluster. Make sure of it.
Roles are objects of the database cluster. All databases of the same cluster share the set of defined roles. Privileges are granted / revoked per database / schema / table etc.
A role needs access to the database, obviously. That's granted to PUBLIC
by default. Else:
GRANT CONNECT ON DATABASE my_db TO my_user;
Postgres 14 adds the predefined, non-login roles pg_read_all_data
/ pg_write_all_data
.
They have SELECT
/ INSERT
, UPDATE
, DELETE
privileges for all tables, views, and sequences. Plus USAGE
on schemas. We can GRANT
membership in these roles:
GRANT pg_read_all_data TO my_user;
GRANT pg_write_all_data TO my_user;
This covers all basic DML commands (but not DDL, and not some special commands like TRUNCATE
or the EXECUTE
privilege for functions!). The manual:
pg_read_all_data
Read all data (tables, views, sequences), as if having
SELECT
rights on those objects, andUSAGE
rights on all schemas, even without having it explicitly. This role does not have the role attributeBYPASSRLS
set. If RLS is being used, an administrator may wish to setBYPASSRLS
on roles which this role isGRANT
ed to.
pg_write_all_data
Write all data (tables, views, sequences), as if having
INSERT
,UPDATE
, andDELETE
rights on those objects, andUSAGE
rights on all schemas, even without having it explicitly. This role does not have the role attributeBYPASSRLS
set. If RLS is being used, an administrator may wish to setBYPASSRLS
on roles which this role isGRANT
ed to.
Commands must be executed while connected to the right database. Make sure of it.
The role needs (at least) the USAGE
privilege on the schema. Again, if that's granted to PUBLIC
, you are covered. Else:
GRANT USAGE ON SCHEMA public TO my_user;
Or grant USAGE
on all custom schemas:
DO
$$
BEGIN
-- RAISE NOTICE '%', ( -- use instead of EXECUTE to see generated commands
EXECUTE (
SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO my_user', nspname), '; ')
FROM pg_namespace
WHERE nspname <> 'information_schema' -- exclude information schema and ...
AND nspname NOT LIKE 'pg\_%' -- ... system schemas
);
END
$$;
Then, all permissions for all tables (requires Postgres 9.0 or later).
And don't forget sequences (if any):
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;
Alternatively, you could use the "Grant Wizard" of pgAdmin 4 to work with a GUI.
There are some other objects, the manual for GRANT
has the complete list. As of Postgres 12:
privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, schema, or tablespace)
But the rest is rarely needed. More details:
Consider upgrading to a current version.
GRANT ALL PRIVILEGES ON DATABASE "my_db" to my_user;
In PostgreSQL 9.0+ you would do the following:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA MY_SCHEMA TO MY_GROUP;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA MY_SCHEMA TO MY_GROUP;
If you want to enable this for newly created relations too, then set the default permissions:
ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
GRANT ALL PRIVILEGES ON TABLES TO MY_GROUP;
ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
GRANT ALL PRIVILEGES ON SEQUENCES TO MY_GROUP;
However, seeing that you use 8.1 you have to code it yourself:
CREATE FUNCTION grant_all_in_schema (schname name, grant_to name) RETURNS integer AS $$
DECLARE
rel RECORD;
BEGIN
FOR rel IN
SELECT c.relname
FROM pg_class c
JOIN pg_namespace s ON c.namespace = s.oid
WHERE s.nspname = schname
LOOP
EXECUTE 'GRANT ALL PRIVILEGES ON ' || quote_ident(schname) || '.' || rel.relname || ' TO ' || quote_ident(grant_to);
END LOOP;
RETURN 1;
END; $$ LANGUAGE plpgsql STRICT;
REVOKE ALL ON FUNCTION grant_all_in_schema(name, name) FROM PUBLIC;
This will set the privileges on all relations: tables, views, indexes, sequences, etc. If you want to restrict that, filter on pg_class.relkind
. See the pg_class docs for details.
You should run this function as superuser and as regular as your application requires. An option would be to package this in a cron job that executes every day or every hour.
I did the following to add a role 'eSumit' on PostgreSQL 9.4.15 database and provide all permission to this role :
CREATE ROLE eSumit;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO eSumit;
GRANT ALL PRIVILEGES ON DATABASE "postgres" to eSumit;
ALTER USER eSumit WITH SUPERUSER;
Also checked the pg_table enteries via :
select * from pg_roles;
Database queries snapshot :
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With