Menu
I'm having trouble granting privileges to another user in PostgreSQL 8.3. While the GRANT command gives me no error, the privileges do not show up. Do I need to "flush" them?
sirprize=# CREATE DATABASE testdb;
CREATE DATABASE
sirprize=# GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
GRANT
sirprize=# \c testdb
You are now connected to database "testdb".
testdb=# \z
Access privileges for database "testdb"
Schema | Name | Type | Access privileges
--------+------+------+-------------------
(0 rows)
testdb=#
527
In the above example we use revoke command to flush privilege, where select and insert are the privilege, where emp is specified table name and sam, jenny are specified user. In this example we flush more than one privilege with more than one user.
The view usage_privileges identifies USAGE privileges granted on various kinds of objects to a currently enabled role or by a currently enabled role. In PostgreSQL, this currently applies to collations, domains, foreign-data wrappers, foreign servers, and sequences.
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.
\z Shows your table, view, and sequence permissions, for the objects contained within the Database. It does not show permissions on the database itself. If you create a table or some other object within 'testdb', it will then show up in \z's output.
You can see which Databases exist on your system with \l (or \l+ for a bit more info).
See section 9.22. of the PostgreSQL 8.3 manual for information about how to programatically determine which permissions exist for a user on a given database.
51
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
